Microsoft Excel
Back to jkp-ads.com

Ron de Bruin
Excel Automation

Microsoft MVP Program

Create and Mail PDF files with Excel 2007 and higher

Important message to visitors of this page

Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.

Luckily, Ron was kind enough to allow me to publish all of his Excel content here.

Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.

Kind regards

Jan Karel Pieterse


Summary

Note : If you are using Mac Office visit this page : https://macexcel.com/examples/mailpdf/makemailpdf/

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. Before Excel 2007 Sp2 you must install an add-in.

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 higher :
File > Export > Create PDF XPS document    or    File > Share >Email >Send as PDF

Note: In Excel 2010 and higher the big round Office Button is replaced with File.

Tips / warnings :

1) You can change OpenAfterPublish in the code to True or False to open the PDF or not after you create it.
2) The mail code example is only working with Outlook.
3) If there is no printer installed it 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.

 

Download the example code to Create and Mail PDF files

Download a workbook with a 9 VBA code examples, file date : 26-April-2020

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 code from 26-April-2020 avoid problems if you also use the Workbook_BeforePrint event (Thanks Brad Yundt), read below why you nee to disable events if you use the Workbook_BeforePrint event.

Warning : ExportAsFixedFormat is nice to create a PDF file, but it also triggers the Workbook_BeforePrint event sub. To avoid a cascade of Workbook_BeforePrint calls if you use that event sub to create a PDF, make sure you turn events off before calling ExportAsFixedFormat, and then back on afterwards. The code in the download disable events so you not have a problem if you use it.

 

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