Return to VBA Code Examples

Excel VBA Save As (Print) to PDF

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on November 18, 2021

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! vba save as


Learn More!
vba-free-addin

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)

Free Download

Return to VBA Code Examples