Emailing Sheets in PDF to Email in that sheet

lizemi

New Member
Joined
Sep 5, 2021
Messages
28
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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.

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

HTH, Dave
 
Upvote 0
Solution

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top