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 (29-7-2017)

Below you can find example VBA code to save as PDF in Mac Excel 2016. Microsoft fixed a few bugs but there are still a lot of bugs to fix, so I will update the code 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. Check out this page if you want an easy way to open this folder in finder : Setup your Mac for Mac Office 2016

Option Explicit

'Note: the 4 macros below use the CreateFolderinMacOffice2016 function that is on the bottom of this module

Sub SaveActiveWorkbookAsPDFInMacExcel2016()
    'Ron de Bruin : 29-July-2017
    'Test macro to save the ActiveWorkbook as pdf with ExportAsFixedFormat
    'If set it save the printareas. All sheets seems to follow the orientation of the activesheet(Bug)
    Dim FileName As String
    Dim FolderName As String
    Dim Folderstring As String
    Dim FilePathName As String

    'If my ActiveSheet is landscape, I must attach this line
    'for making the PDF also landscape, seems to default to xlPortait
    ActiveSheet.PageSetup.Orientation = ActiveSheet.PageSetup.Orientation

    'Name of the folder in the Office folder
    FolderName = "PDFSaveFolder"
    'Name of the pdf file
    FileName = ActiveWorkbook.Name & " " & Format(Now, "dd-mmm-yyyy hh-mm-ss") & ".pdf"

    Folderstring = CreateFolderinMacOffice2016(NameFolder:=FolderName)
    FilePathName = Folderstring & Application.PathSeparator & FileName

    'expression A variable that represents a Workbook, Sheet, Chart, or Range object.
    'the parameters are not working like in Excel for Windows
    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
    FilePathName, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False
  
    MsgBox "You find the PDF file in this location : " & FilePathName
End Sub


Sub SaveActiveSheetAsPDFInMacExcel2016()
    'Ron de Bruin : 29-July-2017
    'Test macro to save the ActiveSheet as pdf with ExportAsFixedFormat
    'Note : if set it save the printarea
    Dim FileName As String
    Dim FolderName As String
    Dim Folderstring As String
    Dim FilePathName As String

    'If my ActiveSheet is landscape, I must attach this line
    'for making the PDF also landscape, seems to default to xlPortait
    ActiveSheet.PageSetup.Orientation = ActiveSheet.PageSetup.Orientation

    'Name of the folder in the Office folder
    FolderName = "PDFSaveFolder"
    'Name of the pdf file
    FileName = ActiveSheet.Name & " " & Format(Now, "dd-mmm-yyyy hh-mm-ss") & ".pdf"

    Folderstring = CreateFolderinMacOffice2016(NameFolder:=FolderName)
    FilePathName = Folderstring & Application.PathSeparator & FileName

    'expression A variable that represents a Workbook, Sheet, Chart, or Range object.
    'the parameters are not working like in Excel for Windows
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
    FilePathName, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False
  
    MsgBox "You find the PDF file in this location : " & FilePathName
End Sub


Sub SaveRangeAsPDFInMacExcel2016()
    'Ron de Bruin : 29-July-2017
    'Test macro to save the Range A1:C20 as pdf with ExportAsFixedFormat
    Dim FileName As String
    Dim FolderName As String
    Dim Folderstring As String
    Dim FilePathName As String

    'If my ActiveSheet is landscape, I must attach this line
    'for making the PDF also landscape, seems to default to xlPortait
    ActiveSheet.PageSetup.Orientation = ActiveSheet.PageSetup.Orientation

    'Name of the folder in the Office folder
    FolderName = "PDFSaveFolder"
    'Name of the pdf file
    FileName = ActiveSheet.Name & " Range " & Format(Now, "dd-mmm-yyyy hh-mm-ss") & ".pdf"

    Folderstring = CreateFolderinMacOffice2016(NameFolder:=FolderName)
    FilePathName = Folderstring & Application.PathSeparator & FileName

    'expression A variable that represents a Workbook, Sheet, Chart, or Range object.
    'the parameters are not working like in Excel for Windows
    Range("A1:C20").ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
    FilePathName, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False
  
    MsgBox "You find the PDF file in this location : " & FilePathName
End Sub


Sub PublishEachWorkSheetToPDFInMacExcel2016()
    'Ron de Bruin : 29-July-2017
    'Test macro to publish each worksheet to pdf with ExportAsFixedFormat
    'Note : if set it save the printarea
    'It will create a new folder for you with the files
    Dim FolderName As String
    Dim Folderstring As String
    Dim Fstr As String
    Dim TestStr As String
    Dim sh As Worksheet
    Dim FileName As String
    Dim FilePathName As String

    'Name of the Root folder in the Office folder, and create the folder
    FolderName = "PDFSaveFolder"
    Folderstring = CreateFolderinMacOffice2016(NameFolder:=FolderName)

    'Create folder in the Root folder with the name of the ActiveWorkbook
    Fstr = Mid(ActiveWorkbook.Name, 1, InStrRev(ActiveWorkbook.Name, ".", , 1) - 1) & Format(Now, " dd-mmm-yyyy hh-mm-ss")
    On Error Resume Next
    TestStr = Dir(Folderstring & "/" & Fstr, vbDirectory)
    On Error GoTo 0
    If TestStr = vbNullString Then MkDir Folderstring & "/" & Fstr

    'Loop through all worksheets
    For Each sh In ActiveWorkbook.Worksheets
        'If the sheet is visible then publish it to PDF
        If sh.Visible = -1 Then
            sh.PageSetup.Orientation = sh.PageSetup.Orientation

            'File name is the sheet name and a date/time stamp
            FileName = sh.Name & " " & Format(Now, "dd-mmm-yyyy hh-mm-ss") & ".pdf"

            'Publish the Worksheet to pdf
            FilePathName = Folderstring & Application.PathSeparator & Fstr & Application.PathSeparator & FileName
            'expression A variable that represents a Workbook, Sheet, Chart, or Range object.
            'the parameters are not working like in Excel for Windows
            sh.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
            FilePathName, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False
        End If
        Next sh
        
    MsgBox "You find the PDF files in this location : " & Folderstring & "/" & Fstr
End Sub


Function CreateFolderinMacOffice2016(NameFolder As String) As String
    'Function to create folder if it not exists in the Microsoft Office Folder
    'Ron de Bruin : 8-Jan-2016
    Dim OfficeFolder As String
    Dim PathToFolder As String
    Dim TestStr As String

    OfficeFolder = MacScript("return POSIX path of (path to desktop folder) as string")
    OfficeFolder = Replace(OfficeFolder, "/Desktop", "") & _
        "Library/Group Containers/UBF8T346G9.Office/"

    PathToFolder = OfficeFolder & NameFolder

    On Error Resume Next
    TestStr = Dir(PathToFolder, vbDirectory)
    On Error GoTo 0
    If TestStr = vbNullString Then
        MkDir PathToFolder
        'You can use this msgbox line for testing if you want
        'MsgBox "You find the new folder in this location :" & PathToFolder
    End If
    CreateFolderinMacOffice2016 = PathToFolder
End Function

 

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

Mac Outlook 2016 with Mac Excel 2016:

Download example to Save and mail a PDF with Mac Outlook 2016 (29-July-2017)

For more Mac Outlook 2016 mail code visit this page for examples files and Add-in (29-July-2017)

 

Mac Mail with Mac Excel 2016:

Download example to Save and mail a PDF with Apple Mail (14-Sept-2017)

Note : There is also a workbook in the download to mail as workbook

 

 

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