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 (11-March-2017)

Below you can find example VBA code to save as PDF in 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

Read the comments in the code about the bugs that there are, hope that Microsoft can fix them so we have code that is working the same in Win Excel as in Mac Excel 2016.

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

Sub SaveActiveSheetAsPDFIn2016()
    'Ron de Bruin : 10-March-2017
    'Test macro to save the activesheet(or printarea) as pdf with ExportAsFixedFormat
    'Note: This macro use the custom CreateFolderinMacOffice2016 function
    Dim FileName As String
    Dim FolderName As String
    Dim Folderstring As String
    Dim FilePathName As String

    'Set the Orientation of the sheet, seems to default to xlPortait
    'if we not use this code line. This is a bug in Mac Excel 2016
    ActiveSheet.PageSetup.Orientation = ActiveSheet.PageSetup.Orientation

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

    'Make folder in the Office folder if it not exists and create the path/file name
    FolderName = "PDFSaveFolder"
    Folderstring = CreateFolderinMacOffice2016(NameFolder:=FolderName)
    FilePathName = Folderstring & Application.PathSeparator & FileName

    'ExportAsFixedFormat is not working correct in Mac Excel 2016, for example:
    'expression A variable that represents a Workbook, Sheet, Chart, or Range object
    'is not working if you change ActiveWorkbook, it always save the activesheet.
    'Also the parameters are not working like in Win Excel.
    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 SaveSelectionAsPDFIn2016()
    'Ron de Bruin : 10-March-2017
    'Test macro to save/mail the Selection as pdf with ExportAsFixedFormat with Mac Outlook 2016
    'Note: This macro use the custom CreateFolderinMacOffice2016 function
    Dim Source As Range
    Dim Destwb As Workbook
    Dim FileName As String
    Dim FolderName As String
    Dim Folderstring As String
    Dim FilePathName As String

    'Exit the sub if it is Mac Excel 2011 or lower
    If Val(Application.Version) < 15 Then Exit Sub

    'Set Range and test if you have selected a range
    Set Source = Nothing
    On Error Resume Next
    Set Source = Selection.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If Source Is Nothing Then
        MsgBox "The source is not a range or the sheet is protected, " & _
               "please correct and try again.", vbOKOnly
        Exit Sub
    End If

    If ActiveWindow.SelectedSheets.Count > 1 Or _
       Selection.Cells.Count = 1 Or _
       Selection.Areas.Count > 1 Then
        MsgBox "An Error occurred :" & vbNewLine & vbNewLine & _
               "You have more than one sheet selected." & vbNewLine & _
               "You only selected one cell." & vbNewLine & _
               "You selected more than one area." & vbNewLine & vbNewLine & _
               "Please correct and try again.", vbOKOnly
        Exit Sub
    End If

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    'Add new workbook with one worksheet
    Set Destwb = Workbooks.Add(xlWBATWorksheet)

    'Copy the Selection and paste it in the new workbook
    Source.Copy
    With Destwb.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial Paste:=xlPasteValues
        .Cells(1).PasteSpecial Paste:=xlPasteFormats
        .Cells(1).Select
        Application.CutCopyMode = False
    End With

    'Set the Orientation of the sheet, seems to default to xlPortait
    'if we not use this code line. This is a bug in Mac Excel 2016
    ActiveSheet.PageSetup.Orientation = Source.Parent.PageSetup.Orientation

    'Name of the pdf file
    FileName = "Selection of " & Source.Parent.Name & " " & Format(Now, "hh-mm-ss") & ".pdf"

    'Make folder in the Office folder if it not exists and create the path/file name
    FolderName = "PDFSaveFolder"
    Folderstring = CreateFolderinMacOffice2016(NameFolder:=FolderName)
    FilePathName = Folderstring & Application.PathSeparator & FileName

    'ExportAsFixedFormat is not working correct in Mac Excel 2016, for example:
    'expression A variable that represents a Workbook, Sheet, Chart, or Range object
    'is not working if you change ActiveWorkbook, it always save the activesheet.
    'Also the parameters are not working like in Win Excel.
    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
    FilePathName, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False

    'Close the temporory workbook
    Destwb.Close False

    MsgBox "You find the PDF file in this location : " & FilePathName

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub



Sub SaveActiveWorkbookAsPDFIn2016()
    'Ron de Bruin : 10-March-2017
    'Test macro to save the whole workbook(or print areas)as pdf with SaveAs
    'Note: This macro use the custom CreateFolderinMacOffice2016 function
    Dim FileName As String
    Dim FolderName As String
    Dim Folderstring As String
    Dim FilePathName As String

    'Exit the sub if it is Mac Excel 2011 or lower
    If Val(Application.Version) < 15 Then Exit Sub

    'Set the Orientation of the activesheet, seems to default to xlPortait
    'if we not use this code line. This is a bug in Mac Excel 2016
    'Also all sheets in the workbook use the Orientation of the activesheet.
    'Big bug that it change the Orientation of all sheets to the
    'Orientation of the activesheet when you print the whole workbook(paper and PDF)
    ActiveSheet.PageSetup.Orientation = ActiveSheet.PageSetup.Orientation

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

    'Make folder in the Office folder if it not exists and create the path/file name
    FolderName = "PDFSaveFolder"
    Folderstring = CreateFolderinMacOffice2016(NameFolder:=FolderName)
    FilePathName = Folderstring & Application.PathSeparator & FileName

    'Also SaveAs is not wot working correct like ExportAsFixedFormat
    'PublishOption:=xlSheet and xlSelection are not working, it always save the whole workbook
    ActiveWorkbook.SaveAs FileName:=FilePathName, FileFormat:=xlPDF, PublishOption:=xlSheet

    MsgBox "You find the PDF file in this location : " & FilePathName
End Sub



Sub PublishEachWorkSheetToPDF()
    'Ron de Bruin : 10-March-2017
    'Test macro to publish each worksheet(or print area) to pdf with ExportAsFixedFormat
    'It will create a new folder for you with the files
    'Note: This macro use the custom CreateFolderinMacOffice2016 function
    Dim ASheet As Worksheet
    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
    
    'Exit the sub if it is Mac Excel 2011 or lower
    If Val(Application.Version) < 15 Then Exit Sub

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    'Remember the active worksheet
    Set ASheet = ActiveSheet

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

    'Create folder in the PDFSaveFolder 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.Select
            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

            'ExportAsFixedFormat is not working correct in Mac Excel 2016, for example:
            'expression A variable that represents a Workbook, Sheet, Chart, or Range object
            'is not working if you change ActiveWorkbook, it always save the activesheet.
            'Also the parameters are not working like in Win Excel.
           ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
               FilePathName, Quality:=xlQualityStandard, _
                    IncludeDocProperties:=True, IgnorePrintAreas:=False
        End If
     Next sh
        
    ASheet.Select
    MsgBox "You find the PDF files in this location : " & Folderstring & "/" & Fstr
    
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
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
    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 :

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

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

Mac Mail :

Note: I update the mail as PDF code for Mac Mail below also soon when i have time

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

 

 

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