Make and Mail PDF files with VBA code on your Mac

Save as PDF in Mac Excel VBA examples

Below you can find example VBA code to save as PDF in Mac Excel. Microsoft fixed a few bugs but there are still a few of bugs to fix, so I will update the code when needed. The code will create a folder named PDFSaveFolder 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

If you want to create a PDF and also mail it check out this two pages :
Send Mail from Mac Excel with Mac Mail
Send Mail from Mac Excel with Mac Outlook


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

Sub SaveActiveWorkbookAsPDFInMacExcel()
'Ron de Bruin : 11-Dec-2020
'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 = CreateFolderinMacOffice(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 SaveActiveSheetAsPDFInMacExcel()
'Ron de Bruin : 11-Dec-2020
'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 = CreateFolderinMacOffice(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 SaveRangeAsPDFInMacExcel()
'Ron de Bruin : 11-Dec-2020
'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 = CreateFolderinMacOffice(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 PublishEachWorkSheetToPDFInMacExcel()
'Ron de Bruin : 11-Dec-2020
'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 = CreateFolderinMacOffice(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 CreateFolderinMacOffice(NameFolder As String) As String
'Function to create folder if it not exists in the Microsoft Office Folder
'Ron de Bruin : 13-July-2020
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
CreateFolderinMacOffice = PathToFolder
End Function

7-March-2021
Web design by Will Woodgate