VBA 2010 Aanmaken nieuw excelbestand met meerdere tabbladen en verzenden per email

[JKlerks]

LS,

Vooruitlopend op een macro die ik vanmiddag ga schrijven, stel ik alvast de vragen. Ik weet even niet of het allemaal in 1 vraag mag, maar dan staat het allemaal bij elkaar.

Ik ga werken met 1 totaalbestand met data voor verschillende ontvangers. Van dit bestand met 9 tabbladen (8x data en 1x emailadressen) moet iedereen met een emailadres een apart excelbestand ontvangen met de eigen data. Omdat het totaalbestand erg groot is, wil ik hiervoor gebruik maken van een tijdelijk excelbestand dat verstuurd wordt en daarna verwijderd (het mag ook opgeslagen worden, maar de code daarvoor kan ik zelf schrijven).

Op de datatabbladen staat een uniek record, waarmee ik kan bepalen wie de ontvanger is. Ik ga middels filter en de code de data selecteren. Deze wil ik kopiëren naar een tijdelijk nieuw excelbestand, waarna ik het tijdelijke bestand verzend en afsluit. Dit wil ik doen voor een eindeloos (in praktijk ongeveer 40) aantal ontvangers.

Vragen:
- Hoe creëer ik een tijdelijk bestand met meerdere tabbladen (de tabbladen mogen ook allemaal een vaste naam krijgen (heeft zelfs de voorkeur))
- Hoe verzend ik dit naar gebruikers? De emailadressen staan in het totaalbestand en ik zou graag tekst toevoegen aan de email.
- Hoe sluit ik het tijdelijke excelbestand af en creëer ik de volgende? Ook het einde is mogelijk een probleem als ik niet 40x hetzelfde script wil laten lopen, maar dit ook wil programmeren.

Dank alvast!

 

[HSV]

Bv.

thisworkbook.sheets(array("blad1", "blad3", "blad5")).copy
activeworkbook.saveas "c:\temp\copybestand.xlsx"

 

[JKlerks]

HSV, ten eerste dank voor de snelle respons.

(Het is nog niet zeker dat de namen van de tabbladen al in het totaalbestand staan, maar daar kan ik uiteraard voor zorgen.)

Ik heb de code getest en hij werkt, maar hij neemt alle data uit het basisbestand over en die wil ik filteren per gebruiker. Ik denk aan een tijdelijk bestand maken met voorgeschreven tabnamen (in de code) en hier data naar toe kopiëren, alvorens ik het naar de gebruiker stuur. Wellicht dat u andere ideeën hebt, maar het einddoel is 1 mail naar 1 gebruiker met een uniek excel met meerdere tabbladen met voorgeschreven namen

 

[JKlerks]

Autofilter obv waarde uit cel op meerdere tabbladen

Allen,

het aanmaken en verzenden van een nieuw bestand moet gaan lukken met bijgaande link: https://www.rondebruin.nl/win/s1/outlook/amail1.htm

Ik ga met het bestand zo te werk, dat ik de basisdata die ik wil versturen vooraf definieer en dan de betreffende tabbladen naar een nieuw bestand overhaal om te verzenden.

Ik heb de unieke code op de tabbladen 1-8 in elke dataset staan. Dit wel in verschillende kolommen. Om een repeterend geheel te krijgen, wordt de unieke code telkens naar cel AE1 op het dashboard gekopieerd. Ik wil met onderstaande code op alle tabbladen de autofilter met deze waarde gebruiken, maar het werkt niet continu. Wellicht dat ik hierin een simpele fout maak (ik probeer de code ook kort te houden).

Sheets("DASHBOARD").Select
Range("AE1").Copy
Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8")).Select
Sheets("1").Activate
Range("CA1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Sheets("1").Range("A1:H500000").AutoFilter Field:=2, Criteria1:=ActiveSheet.Range("CA1").Value
Sheets("2").Range("A1:N500000").AutoFilter Field:=14, Criteria1:=ActiveSheet.Range("CA1").Value

 

[HSV]

In het bestand een code om mail te verzenden.

In blad 'dashboard' staan in A1:B5 gegevens.
Zo kun je per geadresseerde (kolom A) de tabbladen meegeven (kolom B).

 

[JKlerks]

C00 & .xlsm?

HSV,

Bijzonder erkentelijk voor de code en werkwijze. Met xlsx bestanden werkt dit perfect! :thumb:

Om de eindgebruikers te sturen / helpen, heb ik de tabbladen die ik verzend ook voorzien van macro stuurknoppen. Ik probeer het temp bestand op te slaan als XLSM, maar krijg dan een foutmelding icm c00.

Na zoeken zie ik wel wat staan over bestandscodes per type bestand (52 voor xlsm) maar dat werkt ook niet of ik schrijf het op de verkeerde plaats weg. Kunt u wellicht aangeven, waar ik de wijzigingen naar xlsm dien op te voeren in uw onderstaande code?

Een tweede korte vraag: is dit gebonden aan een maximaal aantal tabbladen?

Sub hsv()
Dim sn, i As Long, Ws As Workbook, c00 As String
Application.ScreenUpdating = False
 sn = ThisWorkbook.Sheets("dashboard").Cells(1).CurrentRegion
  c00 = "c:\temp\copybestand.xlsx"
     For i = 2 To UBound(sn)
       ThisWorkbook.Sheets(Split(sn(i, 2), ",")).Copy
         Set Ws = ActiveWorkbook
             Ws.SaveAs c00
             Ws.Close 0
       With CreateObject("Outlook.Application").CreateItem(0)
          .To = sn(i, 1)
          .Subject = "Zomaar iets"
          .Body = "Geachte heer/mevrouw,"
          .Attachments.Add c00
          .Display 'or send
       End With
    Kill c00
   Next i
End Sub

 

[HSV]

De twee veranderingen.

c00 = "c:\temp\copybestand.[COLOR=#ff0000]xlsm[/COLOR]"

Ws.SaveAs c00[COLOR=#ff0000], 52[/COLOR]