Send Email from Excel with PDF

Send email from Excel with a PDF attachment. Put email addresses in a list, choose a folder to store the PDF files, then click a button to send the emails, or do a test first.

Introduction

Get the sample file on this page, and then follow the instructions below, to customize it with your information and mailing list. Then, click a button to send a set of test emails, or send out the actual emails to your mailing list.

NOTE: The Excel macro in this example uses Outlook to send the emails. For examples of sending email from Excel, using other programs, please visit Ron de Bruin’s website.

Menu Sheet

The sample file has a Menu sheet, with 5 buttons.

The first 3 buttons take you to other sheets, where you can customize the sample data and settings with your own information.

• Update Email List - go to StoreList sheet
• Edit Sales Report - go to SalesRpt sheet
• Update Email Settings - go to MailSettings sheet

The other 2 buttons run macros that send your emails, either a test email, or your actual emails

• Send Test Email
• Send Store Emails

Change Menu Headings

At the top of the Menu sheet, you can change the heading text, in cells B3 and B5, to match your company information.

You can also replace the Pen logo with a different image.

• Right-click on the logo shape, and click Change Picture.
• Click From a File
• Browss to the folder where your image is stored
• Select your image, and click Insert.

Change Button Text

You can change the text on any of the buttons to something different, if you prefer.

• Right-click on the button, and click Edit Text
• Type the next text for the button
• Click on the worksheet, away from the button, to deselect it.

Update Email List

Whtn you click the Update Email List button on the Menu sheet, a macro runs, and takes you to the StoreList sheet. On the StoreList sheet, there is a short list of stores, with the following information in each row: Store Number, Store Name, Sales Amount for latest month, and Email Address.

Unique ID

The StoreNum column a unique store number in each row -- there are no duplicates.

When you add your own data to the table, be sure that one of the columns has a unique number or text entry. That unique ID is used to pull the correct data for the sales report.

Names Used

There are two names used on the StoreList sheet.

• The store list is in a named Excel table, tblStores. That table name is not used in the macro, and could be changed.
• The cells with the store numbers are a named range, StoreNums. This range will automatically get smaller or larger, when you add or remove rows in the table.

Update the Store List

To adapt the sample file for your own use, follow these steps:

• Click on any cell in table's data rows (not in the heading row
• Press Ctrl + A to select all the data
• Right=click on any one of the selected data cells
• Click Delete, then click Table Rows

You'll be left with a table that has a heading row, and one data row, that's empty

Table Changes

If you need to, you can make any of the following changes the table structure.

• Add or remove columns as needed.
• Change the text in any of the table headings
• In the Name Manager, change the named range where the Store Numbers are saved -- change the range name, and/or the column location.
  • If the range name is changed, you'll need to edit a macro (instructions later)
  • Make note of where the email address column is located, in relation to the Store Numbers column. For example, in the sample table, it is 3 columns to the right.

Add Your Data

Next, type your own data into the table, or copy and paste your data from another Excel file.

Remember - At least one column must have a unique number or text entry in each row.

Edit Sales Report

Whtn you click the Edit Sales Report button on the Menu sheet, a macro runs, and takes you to the SalesRpt sheet. In the sample file, the imaginary business is a pen shop, and a sales report will be sent to each of the company's sales locations..

When you run the macro to send emails, the email prepares a report for each store in the Store List (tblStores). For each Store:

• The email macro puts the store number in cell E8 (named rngSN)
• In cells E9 and E11, INDEX/MATCH formulas look up the store name and sales amount
• The SalesRpt sheet is saved as a pdf file
• An email is sent to the store's email address, with the pdf attachment

Customize the Report

You can change the Sales Report, to meet your needs. For example, you can:

• Edit the text and formulas on the SalesRpt sheet, to pull the information that you need in your company's report.
• Change the logo (if you put a different logo on the Menu sheet, copy and paste from there)
• Add more text and formulas, if needed, and move things around, to create the report that you need.
• In the Name Manager, make changes to the named range, rngSN (cell E8 in the sample file) -- change the range name, and/or the cell location.
  • If the range name is changed from rngSN to something different, you'll need to edit a macro (instructions later)
• Change the print area, to fit the revised report

Update Email Settings

Whtn you click the Update Email Settings button on the Menu sheet, a macro runs, and takes you to the MailSettings sheet.

• Before you use the email macros for the first time, you'll need to make changes to the sample settings.
• After that, you can leave the settings as is, or make changes if necessary

Email Subject and Body

In the blue cells, you can customize the email message content:

• Type a short subject line for your email
• Type a short message to go in the email body.
  • A LEN formula in cell E6 counts the characters in the email body cell. You'll see the result after you exit cell D6 (not while you're typing the message)

Test Email Information

In the next 2 blue cells, enter the address and number for the test emails.

2 - Enter the email address where you want all of the test emails to be sent. Usually, you'd enter your own email address here, so you'll receive all the messages, and can check them.

3 - Type a number in this cell, for the number of test emails that you want to send. Usually 2 or 3 tests emails are enough. However, if you want to do a full run, leave this cell empty, and all the emails will be sent to the test address.

Report PDF Folder

In the final blue cell, enter the file path for the folder where the report PDF files should be saved. You can either:

• Type the file path yourself (or copy and paste from Windows Explorer)
• OR, click the button, Select a folder to save PDF files.
  • When the dialog box opens, the dialog box shows the folder where the the active workbook is stored
  • If necessary, browse to the folder that you want to use, then click OK.

Send Test Email

If you’re sending out an email message to a list, it’s a good idea to test it first, by sending the reports to your own email address. This can prevent an embarrassing mistake going out to a big list!

• Be sure that Outlook is open, before you click the button.
• When the confirmation message appears, click the Yes button to continue the test emails, or click No to cancel.
  • 
• Then, click the “Send Test Emails” button, and see what arrives in your inbox.
• Check the email subject line and the message in the email body
• Check the PDF attachments
  • Make sure that the report information is correct
  • Check that each person is receiving the right PDF attachment.

Do multiple tests, if necessary, until everything looks correct.

Send Store Emails

If the Test emails worked correctly, you’re ready to click the “Send Store Emails” button, to send out the actual email.

• Be sure that Outlook is open, before you click the button.
• When the confirmation message appears, click the Yes button to send the emails, or click No to cancel.

Email Macros

Test Emails

When you click the Send Test Emails button, the following macro runs. It runs the main macro, SendEmailWithPDF, with the bTest variable set to True.

Sub SendEmailTest()
SendEmailWithPDF (True)
End Sub

Store Emails

When you click the Send Store Emails button, the following macro runs. It runs the main macro, SendEmailWithPDF, with the bTest variable set to False.

Sub SendEmailStores()
SendEmailWithPDF (False)
End Sub

Store Emails

When you click the Send Store Emails button, the following macro runs. It runs the main macro, SendEmailWithPDF, with the bTest variable set to False.

Sub SendEmailStores()
SendEmailWithPDF (False)
End Sub

Send Emails

Below is the main macro, SendEmailWithPDF, which runs when you click the Send Test Emails button or the Send Store Emails button.

• If you clicked the Send Test Emails button, the bTest variable set to True
• If you clicked the Send Store Emails button, the bTest variable set to False

Sub SendEmailWithPDF(bTest As Boolean)
Dim wsM As Worksheet
Dim wsL As Worksheet
Dim wsR As Worksheet
Dim wsS As Worksheet
Dim rngL As Range
Dim rngSN As Range
Dim rngTN As Range
Dim rngPath As Range
Dim c As Range
Dim lSend As Long
Dim lSent As Long
Dim lCount As Long
Dim lTest As Long
Dim lOff As Long

Dim OutApp As Object
Dim OutMail As Object
Dim strSavePath As String
Dim strPathTest As String
Dim strPDFName As String
Dim strSendTo As String
Dim strSubj As String
Dim strBody As String
Dim strMsg As String
Dim strConf As String

On Error GoTo errHandler
Application.ScreenUpdating = False
Application.DisplayAlerts = False

strMsg = "Could not set variables"
Set wsM = wksMenu
Set wsS = wksSet
Set wsL = wksList
Set wsR = wksRpt
Set rngL = wsL.Range("StoreNums")
Set rngSN = wsR.Range("rngSN")
Set rngTN = wsS.Range("rngTN")
Set rngPath = wsS.Range("rngPath")
'test email address
strSendTo = wsS.Range("rngSendTo").Value

lCount = rngL.Cells.Count
'#columns offset for email address
lOff = 3

If bTest = True Then
   strConf = "TEST Emails: "
   lTest = rngTN.Value
   If lTest > 0 Then
      lCount = lTest
   End If
Else
   strConf = "STORE Emails: "
End If

strConf = strConf & lCount _
      & " emails will be sent"

If bTest = True Then
  If strSendTo = "" Then
    MsgBox "Enter a test email address" _
     & vbCrLf _
     & "and try again."
    GoSettings
    GoTo exitHandler
  Else
    strConf = strConf & vbCrLf _
      & "to " & strSendTo
  End If
End If

strConf = strConf & vbCrLf & vbCrLf
strConf = strConf & "Please confirm: " _
  & vbCrLf & _
  "Do you want to send the emails?"

lSend = MsgBox(strConf, _
  vbQuestion + vbYesNo, "Send Emails")

If lSend = vbYes Then
  strSubj = wsS.Range("rngSubj").Value
  strBody = wsS.Range("rngBody").Value
  strSavePath = rngPath.Value
  
  strMsg = "Could not test Outlook"
  On Error Resume Next
  Set OutApp = _
   GetObject(, "Outlook.Application")
  On Error GoTo errHandler

  If OutApp Is Nothing Then
      MsgBox "Outlook is not open. " _
       & vbCrLf _
       & "Open Outlook and try again"
      GoTo exitHandler
  End If
  
  strMsg = "Could not set path" _
     & " for PDF save folder"
  If Right(strSavePath, 1) <> "\" Then
      strSavePath = strSavePath & "\"
  End If
  
  If DoesPathExist(strSavePath) Then
    'continue code below,
    ' using strSavePath
  Else
    MsgBox "The Save folder, " _
      & strSavePath _
      & vbCrLf & "does not exist." _
      & vbCrLf & _
        "Files could not be created." _
      & vbCrLf & _
        "Please select valid folder."
      wsS.Activate
      rngPath.Activate
    GoTo exitHandler
  End If

  strMsg = "Could not start mail process"
  For Each c In rngL
     rngSN = c.Value
     
     strMsg = "Could not create PDF for " _
         & c.Value
     strPDFName = "SalesReport_" _
         & c.Value & ".pdf"
     If bTest = False Then
        strSendTo = c.Offset(0, lOff).Value
     End If
      wsR.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=strSavePath _
          & strPDFName, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
      
      Set OutMail = OutApp.CreateItem(0)
  
     strMsg = "Could not start mail for " _
           & c.Value
      On Error Resume Next
      With OutMail
          .To = strSendTo
          .CC = ""
          .BCC = ""
          .Subject = strSubj
          .Body = strBody
          .Attachments.Add _
             strSavePath & strPDFName
          .Send
      End With
      On Error GoTo 0
     lSent = lSent + 1
     If lSent >= lCount Then Exit For
  Next c
  
  Application.ScreenUpdating = True
  wsM.Activate
  
  MsgBox "Emails have been sent"
  
End If

exitHandler:
   Application.ScreenUpdating = True
   Application.DisplayAlerts = True
   Set OutMail = Nothing
   Set OutApp = Nothing
   
   Set wsM = Nothing
   Set wsS = Nothing
   Set wsL = Nothing
   Set wsR = Nothing
   Set rngL = Nothing
   Set rngSN = Nothing
   Set rngPath = Nothing
   
   Exit Sub
   
errHandler:
   MsgBox strMsg
   Resume exitHandler

End Sub

Navigation Macros

The following 3 macros are stored on the code module named modNav.

• GoList macro takes you to the StoresList sheet (wksList)
• GoReport macros takes you to the SalesRpt sheet (wksRpt)
• GoSettings macros takes you to the MailSettings sheet (wksSet)

In these macros, the sheet code names are used, such as wksList, instead of the names that you see on the sheet tabs.

Because the sheet code names are used, you can change the names on the sheet tabs, without affecting these macros.

Sub GoList()
On Error Resume Next
With wksList
   .Activate
   .Range("A1").Activate
End With
End Sub
'========================
Sub GoReport()
On Error Resume Next
With wksRpt
   .Activate
   .Range("rngSN").Activate
End With
End Sub
'========================
Sub GoSettings()
On Error Resume Next
With wksSet
   .Activate
   .Range("rngSubj").Activate
End With
End Sub

PDF Folder Macros

There is a macro that lets you select a folder for the email report PDF files, and there's a function to check if the selected folder path is valid.

Select PDF Folder

On the MailSettings sheet, when you click the button, Select a folder to save PDF files, the following macro runs. It opens a dialog box where you can select the folder where you want to store the PDF report files that the email macro creates.

When it opens, the dialog box shows the folder where the the active workbook is stored.

Sub GetFolderFilesPDF()
Dim rngPath As Range
Dim PathStart As String
On Error Resume Next

Set rngPath = wksSet.Range("rngPath")
PathStart = ActiveWorkbook.Path
  
With Application.FileDialog _
 (msoFileDialogFolderPicker)
   .AllowMultiSelect = False
   .InitialFileName = PathStart
   .Show
    
   If .SelectedItems.Count > 0 Then
       rngPath.Value = _
         .SelectedItems(1)
   End If
    
End With
  
End Sub

Folder Check

When the main email macro runs, it uses the following function, to verify that the folder path on the MailSettings sheet is valid.

Function DoesPathExist _
  (myPath As String) As Boolean
  Dim TestStr As String
  If Right(myPath, 1) <> "\" Then
      myPath = myPath & "\"
  End If
  TestStr = ""
  On Error Resume Next
  TestStr = Dir(myPath & "nul")
  On Error GoTo 0

  DoesPathExist = CBool(TestStr <> "")

End Function

Error - Could not create PDF

If you run these macros in Excel for Office 365, you might see an error:

• Could not create PDF...

Or, if you try to manually export a PDF file in Excel for Office 365, you could see this error:

• Document not saved. The document may be open, or an error may have been encountered when saving

On the Windows Club site, they suggest two possible fixes for this problem. The article shows the steps for each solution:

1. Try an Office repair
2. Make a Registry change, to fix the sRGB Color Space Profile.icm, which is in the wrong location

Get the Sample File

To see how the macro works, you can get the Excel Email with PDF sample file. The zipped file is in xlsm format, and contains macros. Be sure to enable macros, if you want to run the macro.

Go back to the Introduction section.

More Tutorials

Macro- Save Sheets As PDF

Copy Macro Code to a workbook

Excel VBA Edit Your Recorded Macro

Excel VBA Getting Started  

Send Email from Excel with PDF