Emailing Sheets in PDF to Email in that sheet

[lizemi]

I have a workbook of 43 sheets.
This my monthly invoices that I need to email.
It has to email that sheet in pdf to the email in cell b6 of that sheet.
I have this macro that does it but it says "active sheet" so if I only select one sheet it sends that sheet to the email on that sheet 100% but if I select all sheets and run the macro it sends al the sheets to the one email recipient. This means I need to go to each sheet and run the macro 43 times to send out the invoices.
Is there a way to have it changed so that I can run the macro once and it will send all 43 sheets individually to the 43 relevant email adresses?


Sub Send_Email()
Dim wPath As String, wFile As String

wPath = ThisWorkbook.Path
wFile = "Filepdf.pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=wPath & wFile, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False


Set dam = CreateObject("Outlook.Application").CreateItem(0)
'
dam.To = Range("b6")
dam.Subject = "Eli Invoice"
dam.Body = "Regards Denise"
dam.Attachments.Add wPath & wFile
dam.Send
MsgBox "Email sent"


End Sub

 

[dave3009]

Hi

You can just call it from a macro that cycles through all your sheets. I've presumed there is 43 sheets and all are exactly the same and that no other sheets are in the workbook.

Sub Process_All_Sheets()
Dim s As Long
For s = 1 To 43
    Sheets(s).Activate
    Send_Email
Next s
End Sub

HTH, Dave

 

[lizemi]

Sub Process_All_Sheets() Dim s As Long For s = 1 To 43 Sheets(s).Activate Send_Email Next s End Sub

Thanks this helps allot