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.
Click "Start," "All Programs," "Microsoft Office," "Microsoft Office Excel 2007" to launch Excel.
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.
Click the "Developer" tab. Click "Visual Basic" to launch the Visual Basic editor.
Click "Insert," "Module" to create a Visual Basic module and open the code window.
Paste the following three lines of Visual Basic code into the code window:Function mergeCellsInRange(theRange As Range) As String mergeCellsInRange = Join(WorksheetFunction.Transpose(theRange), "; ")End Function
Click "File," "Close and Return to Microsoft Excel" to close the Visual Basic editor.
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.
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.
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.
Tips & Warnings
- Most email programs use the semicolon character to separate email addresses in a list. If your email program requires a different character---a comma, for example---replace the semicolon between the quotation marks in the second line of Visual Basic code in Step 5 with the required character.
- You can use this technique even if you don't usually keep your email list in Excel. Paste your list into one Excel column and then follow this method.
- When you send an email with a list of addresses in the "To" or "Cc" boxes, all the recipients can see all the addresses. For some group messages this may be considered rude or even an invasion of privacy. To hide the list from all recipients, put it into the "Bcc" (blind carbon copy) field.