Print to PDF and Email Using VBA in Excel (2 Useful Cases)

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.


Download Practice Workbook


2 Cases to Print to PDF and Email Using Excel VBA

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.

Dataset1

And, the below Sales Apr Sheet refers to the Sales Report of Fruits in April month.

Dataset2

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).

How to Insert VBA Code

➜ Secondly, go to Insert > Module.

How to Insert VBA Code

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:=True

Step 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.Attachments

Note: 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 = Nothing

So, 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

Excel VBA Print to PDF and Email for Single Worksheet

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.

Excel VBA Print to PDF and Email for Single Worksheet

Finally, you’ll get the Email input box where the PDF files are attached. Now, you need to press the Send button only.

Excel VBA Print to PDF and Email for Single Worksheet

However, if you didn’t set up an outlook account, you may get this type of message.

Setting up an Outlook Account

After pressing the Next button, you’ll get the following message.

Setting up an Outlook Account

Lastly, check the circle before the Yes command and set up your email account.

Setting up an Outlook Account


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 i

Things 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

Excel VBA Print to PDF and Email for Multiple Worksheets

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.

Excel VBA Print to PDF and Email for Multiple Worksheets

Immediately, you’ll find the first published PDF (Sales Mar).

Excel VBA Print to PDF and Email for Multiple Worksheets

And, then you’ll get the rest.

Excel VBA Print to PDF and Email for Multiple Worksheets

Eventually, you’ll get the email inbox with multiple attachments as depicted in the following screenshot.

Excel VBA Print to PDF and Email for Multiple Worksheets


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.

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo