Excel VBA Save As (Print) to PDF

This tutorial will demonstrate how to save / print to a PDF in Excel VBA.

Print to PDF

This simple procedure will print the ActiveSheet to a PDF.

Sub SimplePrintToPDF()

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="demo.pdf", Quality:=xlQualityStandard, _
  IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=True

End Sub

I’ve also created a function with error handling, etc. that will print the ActiveSheet to a PDF:

Sub PrintPDF()
    Call Save_PDF
End Sub


Function Save_PDF() As Boolean  ' Copies sheets into new PDF file for e-mailing
    Dim Thissheet As String, ThisFile As String, PathName As String
    Dim SvAs As String

Application.ScreenUpdating = False

' Get File Save Name
    Thissheet = ActiveSheet.Name
    ThisFile = ActiveWorkbook.Name
    PathName = ActiveWorkbook.Path
    SvAs = PathName & "\" & Thissheet & ".pdf"

'Set Print Quality
    On Error Resume Next
    ActiveSheet.PageSetup.PrintQuality = 600
    Err.Clear
    On Error GoTo 0

' Instruct user how to send
    On Error GoTo RefLibError
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=SvAs, Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=True
    On Error GoTo 0
    
SaveOnly:
    MsgBox "A copy of this sheet has been successfully saved as a  .pdf  file: " & vbCrLf & vbCrLf & SvAs & _
        "Review the .pdf document. If the document does NOT look good, adjust your printing parameters, and try again."
        
    Save_PDF = True
    GoTo EndMacro
    
RefLibError:
    MsgBox "Unable to save as PDF. Reference library not found."
    Save_PDF = False
EndMacro:
End Function

The function returns TRUE or FALSE if the print to PDF was successful or not.

Save and Email PDF Function

This function will save the ActiveSheet as a PDF and (optionally) attach the PDF to an email (assuming you have Outlook installed):

Sub Test_Save_PDF()
    Call Send_PDF("SendEmail")
End Sub


Function Send_PDF(Optional action As String = "SaveOnly") As Boolean  ' Copies sheets into new PDF file for e-mailing
    Dim Thissheet As String, ThisFile As String, PathName As String
    Dim SvAs As String

Application.ScreenUpdating = False

' Get File Save Name
    Thissheet = ActiveSheet.Name
    ThisFile = ActiveWorkbook.Name
    PathName = ActiveWorkbook.Path
    SvAs = PathName & "\" & Thissheet & ".pdf"

'Set Print Quality
    On Error Resume Next
    ActiveSheet.PageSetup.PrintQuality = 600
    Err.Clear
    On Error GoTo 0

' Instruct user how to send
    On Error GoTo RefLibError
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=SvAs, Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=True
    On Error GoTo 0
    
' Send Email
    If action = "SendEmail" Then
        On Error GoTo SaveOnly
        Set olApp = CreateObject("Outlook.Application")
        Set olEmail = olApp.CreateItem(olMailItem)
        
        With olEmail
            .Subject = Thissheet & ".pdf"
            .Attachments.Add SvAs
            .Display
        End With
        On Error GoTo 0
        GoTo EndMacro
    End If
    
SaveOnly:
    MsgBox "A copy of this sheet has been successfully saved as a  .pdf  file: " & vbCrLf & vbCrLf & SvAs & _
        "Review the .pdf document. If the document does NOT look good, adjust your printing parameters, and try again."
        
    Send_PDF = True
    GoTo EndMacro
    
RefLibError:
    MsgBox "Unable to save as PDF. Reference library not found."
    Send_PDF = False
EndMacro:
End Function

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!


Learn More!