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 e-mail messages. One of the simplest ways to send one e-mail message to a list of recipients is to copy and paste the entire list into your e-mail program. However, if you're keeping track of your group e-mail 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.
- Skill level:
Other People Are Reading
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(the Range As Range) As String
mergeCellsInRange = Join(WorksheetFunction.Transpose(theRange), "; ")
Click "File," "Close and Return to Microsoft Excel" to close the Visual Basic editor.
Click the cell where you want the merged list of e-mail 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 e-mail service you use. Create a new message. Click the box where you want to paste the list of e-mail 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 e-mail and send it in the usual manner.
Tips and warnings
- Most e-mail programs use the semicolon character to separate e-mail addresses in a list. If your e-mail 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 e-mail list in Excel. Paste your list into one Excel column and then follow this method.
- When you send an e-mail 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.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for