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.
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.
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)
- How to save an Excel filename with timestamp?
- How to use Save As function to automatically overwriting existing file in Excel?
- How to save, export multiple/all sheets to separate csv or text files in Excel?
- How to disable or do not allow Save & Save As options in Excel?
- How to disable workbook save but only allow save as in Excel?