Send an Excel PDF to Multiple Email Recipients with VBA
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.

- 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.
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.
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.
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.
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
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.

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.

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.




