Home > Macros > Basics > Email Send Email from Excel with PDFSend 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. |
IntroductionGet 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 each recipient in your mailing list. NOTE: The Excel macro in this example uses Microsoft Outlook to send the emails. For examples of sending email from Excel, using other programs, please visit Ron de Bruin’s website. |
The sample file has a Menu sheet, with 5 buttons on the Excel spreadsheet.
The first 3 buttons take you to other sheets, where you can customize the sample data and settings with your own information.
The other 2 buttons run macros that send your emails, either a test email, or your actual emails
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.
You can change the text on any of the buttons to something different, if you prefer.
When 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.
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.
There are two names used on the StoreList sheet.
To adapt the sample file for your own use, follow these steps:
You'll be left with a table that has a heading row, and one data row, that's empty
If you need to, you can make any of the following changes the table structure.
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.
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:
You can change the Sales Report, to meet your needs. For example, you can:
Whtn you click the Update Email Settings button on the Menu sheet, a macro runs, and takes you to the MailSettings sheet.
In the blue cells, you can customize the email message content:
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.
In the final blue cell, enter the file path for the folder where the report PDF files should be saved. You can either:
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!
Do multiple tests, if necessary, until everything looks correct.
If the Test emails worked correctly, you’re ready to click the “Send Store Emails” button, to send out the actual email.
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
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
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
Below is the VBA code for the main macro, SendEmailWithPDF, which runs when you click the Send Test Emails button or the Send Store Emails button.
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
The following 3 macros are stored on the code module named modNav.
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
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.
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
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
If you run these macros in Excel for Office 365, you might see an error:
Or, if you try to manually export a PDF file in Excel for Office 365, you could see this error:
On the Windows Club site, they suggest two possible fixes for this problem. The article shows the steps for each solution:
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.
Excel VBA Edit Your Recorded Macro
Last updated: July 19, 2023 12:33 PM