VBA & Macro Problems

Send an Excel PDF to Multiple Email Recipients with VBA

Camila MilosovichCamila Milosovich Nov 18, 2026 869 views

Question details

Create a VBA recipient list from Excel cells and attach a generated PDF to an email, excluding cells whose formulas return empty strings.

Send an Excel PDF to Multiple Email Recipients with VBA
Product
Microsoft Excel and Outlook
Device & OS
not provided
Scenario
A workbook contains email addresses or formulas in a recipient range, and the user wants to email a PDF to the listed recipients.
Observed behavior
Cells containing formulas may be counted as nonempty even when their formulas return empty strings.
Before you start

Save the workbook locally and place the generated PDF in the same folder. Confirm the recipient range and worksheet code name, and use classic Outlook for Windows with a configured email account for the Outlook automation example.

Solution 1Recommended

Build the recipient list from cell values and prepare an Outlook email

Use this approach when recipient cells may contain formulas that return empty strings and the PDF already exists.

The macro reads each cell's calculated value instead of counting cells that contain formulas. It skips errors and values that are empty after trimming spaces.

This check does not validate email address syntax or confirm that an address exists. The example prepares the message for review rather than sending it automatically.

1
Open the VBA editor

In Excel, press Alt+F11, then choose Insert > Module. Confirm that Sheet3 is the worksheet code name shown in the VBA Project window, and adjust O2:O11 if your recipient range differs.

2
Add the macro

Paste this code into the module and replace Report.pdf with the name of your existing PDF: Sub PreparePdfEmail() Dim EmailTo As String Dim cell As Range Dim address As String Dim pdfPath As String Dim olApp As Object Dim Omail As Object If Len(ThisWorkbook.Path) = 0 Then MsgBox "Save the workbook locally first." Exit Sub End If pdfPath = ThisWorkbook.Path & "\Report.pdf" If Dir(pdfPath) = "" Then MsgBox "PDF not found: " & pdfPath Exit Sub End If For Each cell In Sheet3.Range("O2:O11") If Not IsError(cell.Value2) Then address = Trim$(CStr(cell.Value2)) If Len(address) > 0 Then If Len(EmailTo) > 0 Then EmailTo = EmailTo & ";" EmailTo = EmailTo & address End If End If Next cell If Len(EmailTo) = 0 Then MsgBox "No recipients found." Exit Sub End If Set olApp = CreateObject("Outlook.Application") Set Omail = olApp.CreateItem(0) Omail.To = EmailTo Omail.Attachments.Add pdfPath Omail.Display End Sub

3
Run and review the email

Run PreparePdfEmail from the VBA editor or Excel's Macros dialog. In the Outlook message, check that the recipients resolve correctly, confirm the PDF attachment, add a subject and message, and select Send.

Build the recipient list from cell values and prepare an Outlook email
PDF creation and Outlook support: The example attaches an existing PDF; it does not create one. Outlook COM automation requires a compatible desktop Outlook installation and is not supported by the new Outlook for Windows.
Free Microsoft Office alternative

Work with Excel files and PDFs in WPS Office

WPS Office is a free, lightweight alternative with a familiar interface for working with spreadsheets and PDFs. Its Microsoft Office format compatibility makes it easy to move existing files, though it does not fix Excel's Outlook automation or guarantee that an Excel VBA macro will run unchanged.

Open and edit Microsoft Excel XLSX filesExport spreadsheets to PDF for sharingUse a familiar spreadsheet interfaceMove existing Office documents into a lightweight workspace
microsoft office alternative - wps office

Frequently Asked Questions

Why do formula cells that look blank appear in my recipient count?

A cell containing a formula is not truly empty, even if the formula returns an empty string. In VBA, check the calculated Value2 and use Len(Trim$(...)) to exclude empty results.

Does the macro verify that each email address is valid?

No. It excludes blank results and cell errors but does not validate addresses. Review and resolve the recipients in the Outlook message before selecting Send.

How are multiple Outlook recipients separated?

The macro joins nonblank addresses with semicolons before assigning the result to Omail.To. Outlook then displays them in the message's To field.

What if my PDF is stored in a different folder?

Change the pdfPath assignment in the VBA module to the PDF's full local path, such as C:\Reports\Report.pdf, and confirm that the file exists before running the macro.

Will the macro send the email automatically?

No. Omail.Display opens the message for review. Add the subject and message, check the recipients and attachment, and select Send in Outlook.