Fix an Excel VBA Macro to Add All Email Addresses from Column A to the Outlook To Field
Question details
The user needs an Excel VBA macro that collects every nonblank email address from column A and places them all into the To field of an Outlook email template.

- Product
- Microsoft Excel VBA and Microsoft Outlook
- Device & OS
- not provided
- Scenario
- Automating an Outlook email from Excel using a VBA macro and an Outlook template.
- Observed behavior
- The macro opens the Outlook template but only the last address from column A appears in the recipient field.
Open the workbook that contains the email addresses and confirm the addresses are listed in column A, starting at the same row used by the macro. Also check that the Outlook template path and template file name are correct before running the amended macro.
Build a Single Semicolon-Separated Recipient String Before Assigning the Outlook To Field
Use this approach when the macro iterates through column A but only the last address appears in the Outlook To field.
The Outlook To property receives one recipient string. If it is assigned inside the loop, each address overwrites the previous address. Combining values first and assigning the completed string once preserves all nonblank addresses.
In Excel, press Alt+F11 to open the Microsoft Visual Basic for Applications editor and select the module that contains the email macro.
At the top of the macro, before the Outlook message is created, declare the variables: `Dim recipients As String` and `Dim cell As Range`.
Use a loop that starts at the first data row and ends at the last nonblank cell in column A. Example: `For Each cell In Range("A2", Cells(Rows.Count, "A").End(xlUp)): If Len(Trim(cell.Value)) > 0 Then recipients = recipients & IIf(Len(recipients) > 0, ";", "") & Trim(cell.Value): Next cell`.
After the loop and after the Outlook mail item is created, set the recipient field with `mailItem.To = recipients` instead of assigning inside the loop.
Return to Excel, run the macro, and check that the Outlook template opens with all nonblank column A email addresses listed in the To field.

Use WPS Office as a Free, Excel-Compatible Spreadsheet Alternative for Address List Workflows
WPS Office is a free, lightweight office suite that opens, edits, and saves Excel workbooks with strong Microsoft Office format compatibility. Its familiar ribbon interface makes it easy to manage column-based address lists without depending on Outlook VBA automation.
- 1. Download WPS Office Free: Go to the official WPS Office download page and install the free version for your operating system.
- 2. Open the Excel workbook in WPS Spreadsheets: Launch WPS Office, click Menu > Open, and select the workbook that contains the column A email addresses.
- 3. Review and save the workbook: Use WPS Spreadsheets to manage the address list, then click Menu > Save As and choose .xlsx to preserve Microsoft Office compatibility.

Frequently Asked Questions
Why does my Excel VBA macro only add the last email address to the Outlook To field?
Usually the macro assigns each cell to `mailItem.To` inside the loop, so each new address overwrites the previous one. Build one semicolon-separated recipient string first, then assign it once after the loop.
What separator should I use for multiple Outlook recipients in VBA?
Use a semicolon between addresses. The example uses `IIf(Len(recipients) > 0, ";", "")` so a semicolon is inserted only between existing and newly added addresses.
How do I skip the header row when collecting email addresses from column A?
Start the range at the first data row. For example, change `Range("A2", Cells(Rows.Count, "A").End(xlUp))` to `Range("A3", Cells(Rows.Count, "A").End(xlUp))` if row 2 contains other data or you want to start at row 3.
What should I check if the Outlook template still opens without recipients?
Confirm the worksheet with the addresses is active, the range matches the actual column A layout, and the `recipients` string has a value before `mailItem.To = recipients` runs.
Can WPS Office run this Outlook VBA macro for me?
The Outlook part depends on Microsoft Outlook, which WPS Office does not include. WPS Office can open and edit the Excel workbook, but the email automation must run in Microsoft Office with Outlook installed.




