How would you feel if your Excel file is published in PDF format first and then goes to the Email inbox automatically using a single command? Sounds interesting! Certainly, you can do that. In this instructive session, I’ll present you 2 cases to print to PDF and email for a single worksheet as well as multiple worksheets in Excel VBA with proper explanation. So, you may adjust the code for your requirement.
Print to PDF and Email Using Excel VBA: 2 Cases
Let’s introduce today’s dataset where the Sales Report of Fruits is given along with the necessary information e.g. Product ID, Fruit items, and so on. Moreover, there are two Sales Reports i.e. one is for the month of March and the other one represents April.
The below Sales Mar Sheet refers to the Sales Report of Fruits in March month.
And, the below Sales Apr Sheet refers to the Sales Report of Fruits in April month.
Now, you need to print to a PDF first and then send an email in Excel VBA. The first one presents the way of doing such a task for a single worksheet. And the rest method focuses on the same task in case of multiple worksheets.
Let’s move on to the methods.
1. Print to PDF and Email for Single Worksheet
If you want to publish a single worksheet (where your dataset is available) to PDF and then send email, this method will be highly fruitful for you.
Before doing that you need to create a module to insert the VBA code.
➜ Firstly, open a module by clicking Developer > Visual Basic (or press ALT + F11).
➜ Secondly, go to Insert > Module.
Then, insert the complete code (you’ll get the complete code at the end of Step 4).
Step 01: Print to PDF
Firstly, you have to Print to PDF of your dataset available in the worksheet. To do that, you can use the ChDir statement to specify the current folder. Then, you have to utilize the ExportAsFixedFormat statement to publish the active sheet in PDF format.
ChDir "E:\Exceldemy"
'Print to PDF
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"E:\Exceldemy\Sales Report March.pdf", OpenAfterPublish:=TrueStep 02: Declare and Set Variables to Send Email
Next, you have to declare and set the following variables for sending the email automatically.
'Declare Variables
Dim EmailApp As Object
Dim EmailItem As Object
Dim myAttachments As Object
'Set Variables
Set EmailApp = CreateObject("Outlook.application")
Set EmailItem = EmailApp.CreateItem(0)
Set myAttachments = EmailItem.AttachmentsNote: It is necessary to open Outlook, mainly an email app having versatile tasks, for sharing or sending email from Excel directly.
Step 03: Specify the Email Items
Also, you need to assign the necessary email items e.g. the email address, subject and email body using the MailItem object.
With EmailItem
.To = "[email protected]"
.Subject = "Sales Report of Fruits"
.Body = "Please find the PDF attachment of the Excel file"Step 04: Add the Published PDF and Close Statement
Lastly, you may use the Attachments.Add method to add the published PDF document and close the statement by setting the EmaiItem and EmailApp as Nothing.
.Attachments.Add "E:\Exceldemy\Sales Report March.pdf"
'.send
.Display
End With
Set EmailItem = Nothing
Set EmailApp = NothingSo, the complete code will look as follows.
Sub PrintToPDF_Email()
ChDir "E:\Exceldemy"
'Print to PDF
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"E:\Exceldemy\Sales Report March.pdf", OpenAfterPublish:=True
'Declare Variables
Dim EmailApp As Object
Dim EmailItem As Object
Dim myAttachments As Object
'Set Variables
Set EmailApp = CreateObject("Outlook.application")
Set EmailItem = EmailApp.CreateItem(0)
Set myAttachments = EmailItem.Attachments
'Specify Email Items and Add Attachment
With EmailItem
.To = "[email protected]"
.Subject = "Sales Report of Fruits"
.Body = "Please find the PDF attachment of the Excel file"
.Attachments.Add "E:\Exceldemy\Sales Report March.pdf"
'.send
.Display
End With
Set EmailItem = Nothing
Set EmailApp = Nothing
End Sub
⧬ Things that you have to change:
- File path: You have to define the file path where you want to publish the PDF e.g. E:\Exceldemy.
- File name: Besides, specify the file name e.g. E:\Exceldemy\Sales Report March.pdfÂ
- Email address: Enter the suitable email address.
- Email subject: Don’t forget to add an attractive subject e.g. Sales Report of Fruits.
- Email body: Describe briefly about the attachment e.g. Please find the PDF attachment of the Excel file.
- Attached file name: It will be the same as the file name.
After running the code (keyboard shortcut is F5), you’ll get the published pdf document first.
Finally, you’ll get the Email input box where the PDF files are attached. Now, you need to press the Send button only.
However, if you didn’t set up an outlook account, you may get this type of message.
After pressing the Next button, you’ll get the following message.
Lastly, check the circle before the Yes command and set up your email account.
Read More: How to Use Excel VBA to Paste Range into Email Body
2. Print to PDF and Email for Multiple Worksheets
But if you have multiple worksheets to print in multiple PDF files and then email those attachments, you can check out this method.
Here, you have to change two major things and the rest will be the same as shown in the first method.
Things to Change 01: Add loop for Printing Multiple Worksheets
Initially, you can use InputBox to enter the name of multiple worksheets. Subsequently, you have run For…Next statement along with the LBound and UBound function to set the lower and upper limit successfully. Needless to say, you have to use the ExportAsFixedFormat statement for printing to PDF.
Sheet_Names = InputBox("Enter the Names of the Worksheets to Print to PDF: ")
Sheet_Names = Split(Sheet_Names, ", ")
For i = LBound(Sheet_Names) To UBound(Sheet_Names)
Worksheets(Sheet_Names(i)).ExportAsFixedFormat Type:=xlTypePDF, _
Filename:= _
"E:\Exceldemy\" + Sheet_Names(i) + ".pdf", _
OpenAfterPublish:=True
Next iThings to Change 02: Add Multiple AttachmentsÂ
Moreover, you can add multiple attachments together in the following way.
.Attachments.Add "E:\Exceldemy\Sales Mar.pdf"
.Attachments.Add "E:\Exceldemy\Sales Apr.pdf"After changing the two things (also you can change the path, file name, email items), the complete code will be like the following.
Sub PrintToPDF_Email_MultipleSheets()
ChDir "E:\Exceldemy"
'Print to PDF for Multiple Sheets
Sheet_Names = InputBox("Enter the Names of the Worksheets to Print to PDF: ")
Sheet_Names = Split(Sheet_Names, ", ")
For i = LBound(Sheet_Names) To UBound(Sheet_Names)
Worksheets(Sheet_Names(i)).ExportAsFixedFormat Type:=xlTypePDF, _
Filename:= _
"E:\Exceldemy\" + Sheet_Names(i) + ".pdf", _
OpenAfterPublish:=True
Next i
'Declare and Set Variables
Dim EmailApp As Object
Dim EmailItem As Object
Dim myAttachments As Object
Set EmailApp = CreateObject("Outlook.application")
Set EmailItem = EmailApp.CreateItem(0)
Set myAttachments = EmailItem.Attachments
'Specify Email Items and Add Attachments
With EmailItem
.To = "[email protected]"
.Subject = "Sales Report in 2022"
.Body = "Please find the PDF attachment of the Excel file"
.Attachments.Add "E:\Exceldemy\Sales Mar.pdf"
.Attachments.Add "E:\Exceldemy\Sales Apr.pdf"
'.send
.Display
End With
Set EmailItem = Nothing
Set EmailApp = Nothing
End Sub
However, if you run the code, you’ll get an input box where you have to specify the sheet name e.g. Sales Mar, Sales Apr.
Immediately, you’ll find the first published PDF (Sales Mar).
And, then you’ll get the rest.
Eventually, you’ll get the email inbox with multiple attachments as depicted in the following screenshot.
Read More: How to Send Email from Excel with Body Using a Macro
Download Practice Workbook
Conclusion
That’s the end of today’s session. I firmly believe the above-discussed methods will be handy to print to PDF and email in Excel VBA. Anyway, don’t forget to share your thoughts in the comment section below.
Related Articles
- Send Reminder Email Automatically from an Excel Worksheet Using VBA
- Send Email from Excel VBA without Outlook
- VBA to Generate Multiple Lines in Email Body in Excel
- How to Apply Macro to Send Email from Excel with Attachment
- Excel Macro to Send Email Automatically
- Excel Macro: Send Email to an Address in Cell
- Macro to Send Email from Excel
