How to Merge Email Addresses in Excel to Send One Email to a Group

Whether you're the secretary of a secret society, the leader of a local book club or simply someone with many friends, you may find opportunities to send group email messages. One of the simplest ways to send one email message to a list of recipients is to copy and paste the entire list into your email program. However, if you're keeping track of your group email list in a Microsoft Excel spreadsheet, you must first merge or concatenate the addresses into one spreadsheet cell. Excel doesn't provide a built-in command to accomplish this, but a few lines of Visual Basic code will do the job.

...
Send one email to a group list by merging email addresses in Microsoft Excel.

Step

Click "Start," "All Programs," "Microsoft Office," "Microsoft Office Excel 2007" to launch Excel.

Step

Click the "Office" button and "Open." Navigate to the Excel workbook file where you've stored the email addresses you want to merge. Highlight the file name and click "Open" to open the file in Excel.

Step

Click the "Developer" tab. Click "Visual Basic" to launch the Visual Basic editor.

Step

Click "Insert," "Module" to create a Visual Basic module and open the code window.

Step

Paste the following three lines of Visual Basic code into the code window:

Step

Function mergeCellsInRange(theRange As Range) As String mergeCellsInRange = Join(WorksheetFunction.Transpose(theRange), "; ") End Function

Step

Click "File," "Close and Return to Microsoft Excel" to close the Visual Basic editor.

Step

Click the cell where you want the merged list of email addresses to appear. Type the following into the cell: "= mergeCellsInRange(A1:A100)" (without the quotation marks). In place of the range expression "A1:A100," which refers to rows one through 100 in the "A" column, use the appropriate range formula to refer to the actual location of the email addresses in your spreadsheet.

Step

Press "Enter" to cause Excel to run the formula and put the merged list of addresses into the cell. Click back onto the same cell and press "Ctrl-C" (the "Ctrl" key and the "C" key simultaneously) to copy its contents to the clipboard.

Step

Open the email program you prefer to use or open your Web browser and navigate to the online email service you use. Create a new message. Click the box where you want to paste the list of email addresses, either "To," "Cc" or "Bcc." Press "Ctrl-V" (the "Ctrl" key and the "V" key simultaneously) to paste the list from the clipboard into the box. Compose your group email and send it in the usual manner.