OfficeTalk: Creating and Sending PDF Files from Excel 2007 and Excel 2010

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Summary: PDF files are widespread in business communications. See how to create and send these file types as e-mail from Microsoft Office Excel 2007 and Excel 2010. (8 Printed Pages)

Applies to:    Microsoft Excel 2007 | Microsoft Excel 2010
Provided by:   MVP  Ron de Bruin, Microsoft Excel MVP | Frank Rice, Microsoft Corporation

Contents

  • About Creating and Sending PDF Files

  • Creating and Sending PDF Files as E-mail

  • Conclusion

  • Additional Resources

About Creating and Sending PDF Files

Code Gallery   Download the sample code and template

A feature of Microsoft Excel 2007 (with the 2007 Microsoft Office system Service Pack 2 installed) is the ability to create and send Acrobat Reader PDF files.

Note

If you have not already done this, you can install the 2007 Microsoft Office system Service Pack 2.

If you do not want to install the 2007 Microsoft Office system Service Pack 2, you can install the Save as PDF add-in that enables you to export and save files to the PDF format that is used in most 2007 Microsoft Office system applications. The add-in also enables you to send files as e-mail attachments in the PDF format in a subset of these applications. Download the Save as PDF add-in for the 2007 Microsoft Office system.

After you install the feature or the add-in, you can manually save or send a PDF file from the Microsoft Office button Save As option. To do this, click the Microsoft Office buttonMicrosoft Office Button, click Save As and then select PDF from the Save as type drop-down list. You can also use the code demonstrated in this column.

There are several options available when you create PDF files from Excel. You can create a PDF file from the following:

  • The complete workbook.

  • The active worksheet or a group of worksheets. Note that you can use the Sheets(“Sheet3”) command instead of the ActiveSheet command to specify a single worksheet. This lets you create the PDF file from a sheet other than the active sheet.

  • A selection or range of cells.

  • Every worksheet with an address in cell A1.

  • Every worksheet with a sheet-level name (For more information, download the sample file.)

The following prerequisites and limitations apply when you use this feature:

  • If you already have Acrobat Reader installed, you can set the OpenAfterPublish argument in the following code examples to True to open the PDF file after you create it.

  • The code to automatically send e-mail does not work with Microsoft Outlook Express or with Microsoft Windows Mail.

  • If you set OpenAfterPublish to True, you can then manually send the PDF file in Acrobat Reader, Outlook Express, or Microsoft Windows Mail.

  • If there is no printer installed on the computer, the Send As PDF add-in will not work. To work around this, you must install a printer driver for one of the printers in the default printer list. You do not have to be physically connected to a printer to use the add-in.

  • When you use a hyperlink in the workbook or if you use the Hyperlink worksheet function, the hyperlinks will not work in the PDF file.

Creating and Sending PDF Files as E-mail

In the sample code and workbook mentioned at the beginning of this column, there is code that creates the PDF in addition to code that creates and sends the PDF. In this column, I use the code that creates and sends the PDF. However, the code statement that sends the PDF is included but commented out.

The following function is called by the other procedures in this column and creates the PDF file. You must specify all four arguments when you call the function in the examples that follow.

For example, in the following statement:

RDB_Create_PDF(ActiveWorkbook, "", True, True)

Where:

  • The first argument indicates what you want to publish.

  • The path and file name of the PDF file. Note that when you use "", the GetSaveAsFilename dialog box appears where you can enter the file name.

  • Indicates whether you want to overwrite the file that you chose in the GetSaveAsFilename dialog, if any exist.

  • Specifies whether you want to open the file after it is published.

The following example creates the PDF file.

Function RDB_Create_PDF(Myvar As Object, FixedFilePathName As String, _
                 OverwriteIfFileExist As Boolean, OpenPDFAfterPublish As Boolean) As String
    Dim FileFormatstr As String
    Dim Fname As Variant

    'Test to see if the Microsoft Create/Send add-in is installed.
    If Dir(Environ("commonprogramfiles") & "\Microsoft Shared\OFFICE" _
         & Format(Val(Application.Version), "00") & "\EXP_PDF.DLL") <> "" Then

        If FixedFilePathName = "" Then
            'Open the GetSaveAsFilename dialog to enter a file name for the PDF file.
            FileFormatstr = "PDF Files (*.pdf), *.pdf"
            Fname = Application.GetSaveAsFilename("", filefilter:=FileFormatstr, _
                  Title:="Create PDF")

            'If you cancel this dialog, exit the function.
            If Fname = False Then Exit Function
        Else
            Fname = FixedFilePathName
        End If

        'If OverwriteIfFileExist = False then test to see if the PDF
        'already exists in the folder and exit the function if it does.
        If OverwriteIfFileExist = False Then
            If Dir(Fname) <> "" Then Exit Function
        End If

        'Now export the PDF file.
        On Error Resume Next
        Myvar.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                FileName:=Fname, _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=OpenPDFAfterPublish
        On Error GoTo 0

        'If the export is successful, return the file name.
        If Dir(Fname) <> "" Then RDB_Create_PDF = Fname
    End If
End Function

The following example sends the PDF file as an e-mail file. You must fill in all five arguments when you call this function.

RDB_Mail_PDF_Outlook FileName, "ron@debruin.nl", "This is the subject", _
         "See the attached PDF file with the last figures" _
         & vbNewLine & vbNewLine & "Regards Ron de bruin", False

Where:

  1. The first argument is the file name (do not change this).

  2. To whom you want to send the e-mail.

  3. The subject line of the e-mail.

  4. What do you want in the body of the e-mail.

  5. Do you want to display the e-mail (set to False) or send it directly (set to True).

The following example sends the PDF file as an e-mail file.

Function RDB_Mail_PDF_Outlook(FileNamePDF As String, StrTo As String, _
                              StrSubject As String, StrBody As String, Send As Boolean)
    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .To = StrTo
        .CC = ""
        .BCC = ""
        .Subject = StrSubject
        .Body = StrBody
        .Attachments.Add FileNamePDF
        If Send = True Then
            .Send
        Else
            .Display
        End If
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Function

To create a PDF of the complete workbook

  • The following example creates a PDF file from the workbook. You can also choose to send the file in an e-mail.

    Note

    Set the last argument in the RDB_Create_PDF call to False if you want to send the PDF file.

    Sub RDB_Workbook_To_PDF()
        Dim FileName As String
    
        'Call the function with the correct arguments.
        FileName = RDB_Create_PDF(ActiveWorkbook, "", True, True)
    
        'For a fixed file name and to overwrite the file each time you run the macro, use the following statement.
        'RDB_Create_PDF(ActiveWorkbook, "C:\Users\Ron\Test\YourPdfFile.pdf", True, True)
    
        If FileName <> "" Then
        'Uncomment the following statement if you want to send the PDF by mail.
            'RDB_Mail_PDF_Outlook FileName, "ron@debruin.nl", "This is the subject", _
               "See the attached PDF file with the last figures" _
              & vbNewLine & vbNewLine & "Regards Ron de bruin", False
        Else
            MsgBox "It is not possible to create the PDF; possible reasons:" & vbNewLine & _
                   "Microsoft Add-in is not installed" & vbNewLine & _
                   "You canceled the GetSaveAsFilename dialog" & vbNewLine & _
                   "The path to save the file in arg 2 is not correct" & vbNewLine & _
                   "You didn't want to overwrite the existing PDF if it exists."
        End If
    End Sub
    

To create a PDF file of the active sheet or selected worksheets

  • The following example creates a PDF file from the active worksheet and from the selected worksheets. You can also decide to send the file in an e-mail.

    Note

    Set the last argument in the RDB_Create_PDF call to False if you want to send the PDF file.

    Sub RDB_Worksheet_Or_Worksheets_To_PDF()
        Dim FileName As String
    
        If ActiveWindow.SelectedSheets.Count > 1 Then
            MsgBox "There is more than one sheet selected," & vbNewLine & _
                   "and every selected sheet will be published."
        End If
    
        'Call the function with the correct arguments.
        'You can also use Sheets("Sheet3") instead of ActiveSheet in the code(the sheet does not need to be active then).
        FileName = RDB_Create_PDF(ActiveSheet, "", True, True)
    
        'For a fixed file name and to overwrite it each time you run the macro, use the following statement.
        'RDB_Create_PDF(ActiveSheet, "C:\Users\Ron\Test\YourPdfFile.pdf", True, True)
    
        If FileName <> "" Then
            'Uncomment the following statement if you want to send the PDF by e-mail.
            'RDB_Mail_PDF_Outlook FileName, "ron@debruin.nl", "This is the subject", _
               "See the attached PDF file with the last figures" _
              & vbNewLine & vbNewLine & "Regards Ron de bruin", False
        Else
            MsgBox "It is not possible to create the PDF; possible reasons:" & vbNewLine & _
                   "Add-in is not installed" & vbNewLine & _
                   "You canceled the GetSaveAsFilename dialog" & vbNewLine & _
                   "The path to save the file is not correct" & vbNewLine & _
                   "PDF file exists and you canceled overwriting it."
        End If
    End Sub
    

To create a PDF file from a selection or range

  • The following procedure creates a PDF file from a selection of cells or a range. You can also decide to send the file in an e-mail.

    Note

    Set the last argument in the RDB_Create_PDF call to False if you want to send the PDF file.

    Sub RDB_Selection_Range_To_PDF()
        Dim FileName As String
    
        If ActiveWindow.SelectedSheets.Count > 1 Then
            MsgBox "There is more than one sheet selected," & vbNewLine & _
                   "unselect the sheets and try the macro again."
        Else
            'Call the function with the correct arguments.
    
            'For a fixed range use this line.
            FileName = RDB_Create_PDF(Range("A10:I15"), "", True, True)
    
            'For the selection use this line.
            'FileName = RDB_Create_PDF(Selection, "", True, True)
    
            'For a fixed file name and to overwrite it each time you run the macro, use the following statement.
            'RDB_Create_PDF(Selection, "C:\Users\Ron\Test\YourPdfFile.pdf", True, True)
    
            If FileName <> "" Then
                'Uncomment the following statement if you want to send the PDF by mail.
               'RDB_Mail_PDF_Outlook FileName, "ron@debruin.nl", "This is the subject", _
                  "See the attached PDF file with the last figures" _
                 & vbNewLine & vbNewLine & "Regards Ron de bruin", False
            Else
                MsgBox "It is not possible to create the PDF;, possible reasons:" & vbNewLine & _
                       "Microsoft Add-in is not installed" & vbNewLine & _
                       "You canceled the GetSaveAsFilename dialog" & vbNewLine & _
                       "The path to save the file in arg 2 is not correct" & vbNewLine & _
                       "You didn't want to overwrite the existing PDF if it exists."
            End If
        End If
    End Sub
    

To send all worksheets with an address in cell A1

  • The following example scans the workbook, and creates and sends each worksheet with a valid e-mail address in cell A1.

    Sub Mail_Every_Worksheet_With_Address_In_A1_PDF()
    'This example works in Excel 2007 and Excel 2010.
        Dim sh As Worksheet
        Dim TempFilePath As String
        Dim TempFileName As String
        Dim FileName As String
    
        'Set a temporary path to save the PDF files.
        'You can also use another folder similar to
        'TempFilePath = "C:\Users\Ron\MyFolder\"
        TempFilePath = Environ$("temp") & "\"
    
        'Loop through each worksheet.
        For Each sh In ThisWorkbook.Worksheets
            FileName = ""
    
            'Test A1 for an e-mail address.
            If sh.Range("A1").Value Like "?*@?*.?*" Then
    
                'If there is an e-mail address in A1, create the file name and the PDF.
                TempFileName = TempFilePath & "Sheet " & sh.Name & " of " _
                             & ThisWorkbook.Name & " " _
                             & Format(Now, "dd-mmm-yy h-mm-ss") & ".pdf"
    
                FileName = RDB_Create_PDF(sh, TempFileName, True, False)
    
    
                'If publishing is set, create the mail.
                If FileName <> "" Then
                    RDB_Mail_PDF_Outlook FileName, sh.Range("A1").Value, "This is the subject", _
                       "See the attached PDF file with the last figures" _
                       & vbNewLine & vbNewLine & "Regards Ron de bruin", False
    
                    'After the e-mail is created, delete the PDF file in TempFilePath.
                    If Dir(TempFileName) <> "" Then Kill TempFileName
    
                    Else
                       MsgBox "Not possible to create the PDF, possible reasons:" & vbNewLine & _
                       "Microsoft Add-in is not installed" & vbNewLine & _
                           "The path to save the file in arg 2 is not correct" & vbNewLine & _
                           "You didn't want to overwrite the existing PDF if it exist"
                    End If
    
                End If
        Next sh
    End Sub
    

Conclusion

There are several options for creating PDF documents from Excel workbooks and sending them in electronic mail. For example, you can create a file from the complete workbook, from one or more worksheets, or from a range of data. PDF files are widespread in business so this flexibility makes your Excel application or spreadsheets available to a wider audience.

Additional Resources

Special thanks to our editor, Linda Cannon, for her help with this column.