Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

Make and Mail PDF files with VBA code on your Mac

Below you find examples for Excel 2011 and Excel 2016 for the Mac, if you are a Windows user check out the code in the Win Tips section of my site.

Excel 2016 for the Mac

Save as PDF in Mac Excel 2016

Below you can find a example workbook with a example to save the activesheet and whole workbook or all worksheets as a separate PDF with VBA code. Microsoft fixed a few bugs but there are still a lot of bugs to fix, so I will update the workbook when needed. The code will create a folder in the Office folder to save the PDF files in, read the info on this page why I use a folder in that location.

Download example workbook to save as PDF (19-Nov-2016)

 

Save as PDF and attach to mail in Mac Excel 2016

For creating mails with VBA code with the PDF attached we must copy the script file that you also find in the downloads below in the correct location, there is a script file for Mac Mail and one for Mac Outlook 2016. This are the same script files that I use in the examples that send Excel attachments with Mac Excel 2016, see this page : http://www.rondebruin.nl/mac/mail.htm

Where must I copy the file(RDBMacMail.scpt or RDBMacOutlook.scpt) with the script in it ? :

Open a Finder Window
Hold the Alt key when you press on Go in the Finder menu bar
Click on Library
Click on Application Scripts (if it exists; if not create this folder)
Click on com.microsoft.Excel (if it exists; if not create this folder) note: Capital letter E
Copy the script file from the download inside this folder

Download example to Save and mail a PDF with Apple Mail (1-May-2016)

For Mac Outlook 2016 visit this page for examples files and Add-in (25-Nov-2016)

For more information and examples about mailing with VBA code from Mac Excel visit : Send Mail from Mac Excel

 

Excel 2011 for the Mac

Download the example workbook below with a few example macro's to create and mail PDF's in Excel 2011. Please test it and give feedback, good or bad. There are Mail examples in the workbook for Apple Mail and Outlook 2011.

Download example file : 29 Nov 2015

Note: Fixed a bug that it not attach the pdf to the mail when you use El Capitan as OS X.

Double-click the dmg file if it does not mount automatically. Drag the file to the folder you want on your Mac. Tip: Use one folder for all your example files (easy to backup your files this way)

Note: When you add new worksheets to the workbook the example code to make a PDF of the whole workbook do not include that sheet or sheets until you Close/Reopen the file.

There are a few problems(bugs) If you use VBA SaveAs or ExportAsFixedFormat or save a workbook manual as PDF. When you save the workbook as PDF it wil create a seperate PDF for each sheet in the workbook and it also add a sheet name to the file name of the PDF when you do it manual or with code.

My workeround is this :
because creating a pdf of each sheet only happens the first time when you run the code I do this :

1: Create the PDF in a Temporary folder (code will create that for you)
2: Delete all files in this folder
3: Create the pdf again
4: Rename the PDF file (remove the sheet name)
5: Move the file to the folder you want

 

More Mail Examples

For more information and examples about mailing with VBA code from Mac Excel visit : Send Mail from Mac Excel