Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to save a worksheet as PDF file and email it as an attachment through Outlook?

In some cases, you may need to send a worksheet as a PDF file through Outlook. Usually, you have to manually save the worksheet as a PDF file, then create a new email with this PDF file as attachment in your Outlook and finally send it. It is time-consuming to achieve it manually step by step. In this article, we will show you how to quickly save a worksheet as a PDF file and send it automatically as an attachment through Outlook in Excel.

Save a worksheet as PDF file and email it as an attachment with VBA code


Easily save a worksheet or multiple worksheets as separate PDF files at once:

The Split Workbook utility of Kutools for Excel can help you easily save a worksheet or multiple worksheets as separate PDF files at once. See screen shot:

Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 60 days. Download the free trial now

Excel Productivity Tools

Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial

Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately. 60-day Unlimited Free Trial


arrow blue right bubble Save a worksheet as PDF file and email it as an attachment with VBA code


You can run the below VBA code to automatically save active worksheet as a PDF file, and then email it as an attachment through Outlook. Please do as follows.

1. Open the worksheet you will save as PDF and send, then press the Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then copy and paste the below VBA code into the Code window. See screenshot:

VBA code: Save a worksheet as PDF file and email it as an attachment

Sub Saveaspdfandsend()
Dim xSht As Worksheet
Dim xFileDlg As FileDialog
Dim xFolder As String
Dim xYesorNo As Integer
Dim xOutlookObj As Object
Dim xEmailObj As Object
Dim xUsedRng As Range

Set xSht = ActiveSheet
Set xFileDlg = Application.FileDialog(msoFileDialogFolderPicker)

If xFileDlg.Show = True Then
   xFolder = xFileDlg.SelectedItems(1)
Else
   MsgBox "You must specify a folder to save the PDF into." & vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Must Specify Destination Folder"
   Exit Sub
End If
xFolder = xFolder + "\" + xSht.Name + ".pdf"

'Check if file already exist
If Len(Dir(xFolder)) > 0 Then
    xYesorNo = MsgBox(xFolder & " already exists." & vbCrLf & vbCrLf & "Do you want to overwrite it?", _
                      vbYesNo + vbQuestion, "File Exists")
    On Error Resume Next
    If xYesorNo = vbYes Then
        Kill xFolder
    Else
        MsgBox "if you don't overwrite the existing PDF, I can't continue." _
                    & vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Exiting Macro"
        Exit Sub
    End If
    If Err.Number <> 0 Then
        MsgBox "Unable to delete existing file.  Please make sure the file is not open or write protected." _
                    & vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Unable to Delete File"
        Exit Sub
    End If
End If

Set xUsedRng = xSht.UsedRange
If Application.WorksheetFunction.CountA(xUsedRng.Cells) <> 0 Then
    'Save as PDF file 
    xSht.ExportAsFixedFormat Type:=xlTypePDF, Filename:=xFolder, Quality:=xlQualityStandard
    
    'Create Outlook email 
    Set xOutlookObj = CreateObject("Outlook.Application")
    Set xEmailObj = xOutlookObj.CreateItem(0)
    With xEmailObj
        .Display
        .To = ""
        .CC = ""
        .Subject = xSht.Name + ".pdf"
        .Attachments.Add xFolder
        If DisplayEmail = False Then
            '.Send
        End If
    End With
Else
  MsgBox "The active worksheet cannot be blank"
  Exit Sub
End If
End Sub

3. Press the F5 key to run the code. In the Browse dialog box, please select a folder to save this PDF file, and then click the OK button.

Notes:

1. Now the active worksheet is saved as PDF file. And the PDF file is named with the worksheet name.

2. If the active worksheet is blank, you will get a dialog box as below screenshot shown after clicking the OK button.

4. Now a new Outlook email is created and you can see the PDF file is listed as an attachment in the Attached filed. See screenshot:

5. Please compose this email and then send it.

Note: This code is only available when you use Outlook as your mail program.


arrow blue right bubbleRelated articles:


Excel Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 80% Excel problems.

  • Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  • Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  • Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  • No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  • Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  • 60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  • Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    ranga · 3 days ago
    Thanks it works.
  • To post as a guest, your comment is unpublished.
    james · 8 months ago
    Hi, how can i save & send the pdf wit the workbook name with the current VBA code? what do i use instead of xSht.Name
    • To post as a guest, your comment is unpublished.
      crystal · 8 months ago
      Hi James,
      Do you want to send the active worksheet as pdf and name it as the workbook name?
  • To post as a guest, your comment is unpublished.
    Tom H · 11 months ago
    How would I edit the VBA script above so that the file name is saved as a specific cell selected within the current sheet, for example cell A1?
  • To post as a guest, your comment is unpublished.
    Armin · 1 years ago
    How can I add for example sheet 2 from the workbook as an pdf?
    • To post as a guest, your comment is unpublished.
      crystal · 10 months ago
      Hi Armin,
      You need to open the Sheet 2 in your workbook firstly and then run the VBA code with above steps to get it down.
  • To post as a guest, your comment is unpublished.
    Michael Charry · 1 years ago
    How would I edit the VBA script above so that it adds a date and time stamp to the file name that way it doesn't keep overwriting what is already saved?
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Dear Michael,
      Please run the below VBA code to solve the problem.

      Sub Saveaspdfandsend()
      Dim xSht As Worksheet
      Dim xFileDlg As FileDialog
      Dim xFolder As String
      Dim xYesorNo As Integer
      Dim xOutlookObj As Object
      Dim xEmailObj As Object
      Dim xUsedRng As Range
      Dim xStr As String

      Set xSht = ActiveSheet
      Set xFileDlg = Application.FileDialog(msoFileDialogFolderPicker)

      If xFileDlg.Show = True Then
      xFolder = xFileDlg.SelectedItems(1)
      Else
      MsgBox "You must specify a folder to save the PDF into." & vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Must Specify Destination Folder"
      Exit Sub
      End If
      xStr = Format(Now(), "yyyy-mm-dd-hh-mm-ss")
      xFolder = xFolder + "\" + xSht.Name + "-" + xStr + ".pdf"

      'Check if file already exist
      If Len(Dir(xFolder)) > 0 Then
      xYesorNo = MsgBox(xFolder & " already exists." & vbCrLf & vbCrLf & "Do you want to overwrite it?", _
      vbYesNo + vbQuestion, "File Exists")
      On Error Resume Next
      If xYesorNo = vbYes Then
      Kill xFolder
      Else
      MsgBox "if you don't overwrite the existing PDF, I can't continue." _
      & vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Exiting Macro"
      Exit Sub
      End If
      If Err.Number <> 0 Then
      MsgBox "Unable to delete existing file. Please make sure the file is not open or write protected." _
      & vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Unable to Delete File"
      Exit Sub
      End If
      End If

      Set xUsedRng = xSht.UsedRange
      If Application.WorksheetFunction.CountA(xUsedRng.Cells) <> 0 Then
      'Save as PDF file
      xSht.ExportAsFixedFormat Type:=xlTypePDF, Filename:=xFolder, Quality:=xlQualityStandard

      'Create Outlook email
      Set xOutlookObj = CreateObject("Outlook.Application")
      Set xEmailObj = xOutlookObj.CreateItem(0)
      With xEmailObj
      .Display
      .To = ""
      .CC = ""
      .Subject = xSht.Name + "-" + xStr + ".pdf"
      .Attachments.Add xFolder
      If DisplayEmail = False Then
      '.Send
      End If
      End With
      Else
      MsgBox "The active worksheet cannot be blank"
      Exit Sub
      End If
      End Sub
      • To post as a guest, your comment is unpublished.
        Parag · 5 days ago
        Hi Crystal,

        It's really great and working perfectly for me. Need more help to add:

        1. in "To" I want to give link to particular cell of Active sheet like wise in CC and in BCC i would like to add active sheet link
        2. in e-mail body i need to specify some standard text.

        I will be great full to you for your help.

        Thanks
        Parag
      • To post as a guest, your comment is unpublished.
        Parag Somani · 5 days ago
        Hi Crystal,

        It's really great and working perfectly for me. Need more help to add:

        1. in "To" I want to give link to particular cell of Active sheet like wise in CC and in BCC i would like to add active sheet link
        2. in e-mail body i need to specify some standard text.

        I will be great full to you for your help.

        Thanks
        Parag
      • To post as a guest, your comment is unpublished.
        Parag Somani · 5 days ago
        Hi Crystal,

        It's really great and working perfectly for me. Need more help to add:

        1. in "To" I want to give link to particular cell of Active sheet like wise in CC and in BCC i would like to add active sheet link
        2. in e-mail body i need to specify some standard text.

        I will be great full to you for your help.

        Thanks
        Parag