VBA & Macro Problems

Fix an Excel VBA Macro to Add All Email Addresses from Column A to the Outlook To Field

John WilsonJohn Wilson Nov 13, 2026 869 views

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.

Fix an Excel VBA Macro to Add All Email Addresses from Column A to the Outlook To Field
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor in Excel

In Excel, press Alt+F11 to open the Microsoft Visual Basic for Applications editor and select the module that contains the email macro.

2
Declare the variables

At the top of the macro, before the Outlook message is created, declare the variables: `Dim recipients As String` and `Dim cell As Range`.

3
Loop through column A and collect nonblank addresses

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

4
Assign the completed string to the To field once

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.

5
Run the macro and verify the Outlook To field

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.

Build a Single Semicolon-Separated Recipient String Before Assigning the Outlook To Field
Recipient list complete: The Outlook To field now contains every nonblank address from column A, separated by semicolons.
Free Microsoft Office alternative

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. 1. Download WPS Office Free: Go to the official WPS Office download page and install the free version for your operating system.
  2. 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. 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.
Free to download and use for everyday spreadsheet tasks, including managing email address lists in columns.Opens and saves .xlsx and .xls workbooks with high Microsoft Office format compatibility.Familiar ribbon interface and spreadsheet functions reduce the learning curve when switching from Excel.Lightweight installation that runs smoothly on Windows, macOS, Android, and iOS.Seamless migration for existing Excel workbooks, including column data and formatting.
microsoft office alternative - wps office

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.