Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

Create and Mail PDF files with Excel 2007/2016

Summary

A new feature of Microsoft Excel 2007 (with Microsoft Office Service Pack 2 installed) and up is the ability to create and mail Acrobat Reader PDF files.

Excel 2007 : Office Button > Save As ….PDF    Or    Office Button > Send ….PDF
Excel 2010 :
File > Save As ….PDF    or    File > Send ….PDF
Excel 2013 and 2016 :
File > Export > Create PDF XPS document    or    File > Share >Email >Send as PDF

Note: In Excel 2010 -2016 the big round Office Button is replaced with File.

Tips / warnings :

1) If you have also installed Acrobat Reader you can change OpenAfterPublish in the code to
    True to open the PDF file after you create it.
2) The mail code example is not working with Outlook Express or Windows Mail.
3) If you set OpenAfterPublish in the code to True then you can do a manual send in
    Acrobat Reader (also with Outlook Express or Windows Mail).
4) If there is no printer installed the add-in will not work. You only have to install a printer driver of one of the printers in the default printer list, you not need a real printer to use the add-in.
5) When you use a hyperlink to another place in the workbook or if you use the Hyperlink worksheet function the hyperlinks are not working in the PDF.

 

Download the example code to Create and Mail PDF files

Download a workbook with a 9 VBA code examples, file date : 21-Feb-2015

Download PDF-Examples.zip

In the workbook you find three code modules with 9 macros and 3 functions.
Note: Do not change the functions in the module named "FunctionsModule"

 

The macros to create a PDF in the "CreatePDF" module

Macro 1 : Create a PDF of the workbook
Macro 2 : Create a PDF of the ActiveSheet or selected sheets
Macro 3 : Create a PDF of the selection or range
Macro 4 : Create a PDF with every sheet with a specific sheet level name

Note : Read the information good in the example workbook

The macros that create and mail the PDF in the "CreatePDFMail" module

Important : The code in the example workbook is only working if you use Outlook as mail program.

Macro 1 : Create and mail a PDF of the workbook
Macro 2 : Create and mail a PDF of the ActiveSheet or selected sheets
Macro 3 : Create and mail a PDF of the selection or range
Macro 4 : Create and mail a PDF with every sheet with a specific sheet level name
Macro 5 : Create and mail a PDF of every sheet with a mail address in A1

Note : Read the information good in the example workbook

 

More information about mailing from Excel

Note: For much more mail code examples for Outlook visit my mail page