Skip to main content

Kutools for Office — One Suite. Five Tools. Get More Done.

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

Author Siluvia Last modified

In daily work, you may often encounter situations where you need to share a specific worksheet with colleagues or clients, but want to ensure the file format is fixed and secure. Sending a worksheet as a PDF file via Outlook is a common requirement, especially for documenting reports, sending invoices, or sharing finalized data that should not be easily modified. Traditionally, this involves manually saving the worksheet as a PDF, opening Outlook, composing an email, attaching the PDF, and sending— which is tedious and time-consuming, particularly if repeated frequently or when handling multiple sheets.

This article guides you step by step on how to automate the process of converting a worksheet to PDF and immediately attaching it to an Outlook email directly from Excel, helping you save significant time and avoid repetitive manual work. You will find a VBA code solution, along with details on operation, advantages, application scenarios, and practical tips.

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

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

To speed up the process of exporting an Excel worksheet as a PDF file and emailing it through Outlook, you can use the following VBA code. This approach is especially useful if you regularly need to send personalized reports, invoices, or other data snapshots straight from Excel, as it completely automates the saving and emailing steps.

Before applying this method, make sure that Microsoft Outlook is installed and set as your default mail client. The code works best when macros are enabled in your Excel environment.

1. Open the worksheet you want to save and send as a PDF. Press Alt + F11 at the same time to launch the Microsoft Visual Basic for Applications (VBA) editor.

2. In the VBA window, go to the menu and click Insert > Module. This will create a new code module. Then copy and paste the following VBA code into the module's Code window.  

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. After pasting the code, press F5 or click Run in the VBA editor to execute the macro. A folder selection dialog will appear. Choose the target folder where the PDF file should be saved, then click OK to continue.

run vba code to select a folder to save this PDF file

Notes and Practical Tips:

1. The macro creates a PDF file with the same name as the worksheet, stored in your selected folder.
2. If you try to run the code when the active worksheet is empty, you will see a warning dialog like the screenshot below. This prevents saving and emailing a blank PDF by mistake. To avoid this, ensure there is content in the worksheet before executing the macro.

If the active worksheet is blank,a warning prompt box is popped out

4. Once the process completes, a new Outlook email window is generated, with the PDF file attached. The subject line is prefilled with the worksheet's name ending in ".pdf"; you can edit the email content, add recipients, and then send as needed. This reduces errors and increases efficiency over manual attachment.

a new Outlook email is created with the PDF file as an attachment

Note: The macro only works if Microsoft Outlook is installed and set as your default mail client. If you use any other email program, this solution may not apply.

This VBA-based approach is most efficient when you regularly need to distribute finalized worksheets as PDF attachments and minimizes repetitive manual steps. Its limitations include dependency on Outlook, inability to batch multiple sheets at once, and the need to enable macros. For more complex workflows, such as sending multiple worksheets in one go or additional automation, consider using Excel add-ins or built-in features.

Alternative solution: If VBA macros are not suitable for your environment, such as when macros are restricted, you can manually use Excel's built-in Export or Save As function to save your worksheet as PDF, then attach and send it in Outlook. This method, while requiring more steps, is universally available without special permissions or scripting knowledge.

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 as the below demo shown. Download and try it now! (30-day free trail)

save multiple worksheets as separate PDF files by kutools

Related articles:

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

All Kutools add-ins. One installer

Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.

Excel Word Outlook Tabs PowerPoint
  • All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
  • One installer, one license — set up in minutes (MSI-ready)
  • Works better together — streamlined productivity across Office apps
  • 30-day full-featured trial — no registration, no credit card
  • Best value — save vs buying individual add-in