Tuesday, May 09, 2006

Take email addresses in a column and make them a comma separated list

I often get asked how to take a list of email addresses from a column in a table on the internet and turn it into a comma separated list for using to email that group.  The question usually arises in connection with emailing to undeliverable email addresses from an autoresponder system.

This represents one way you can do this:
1. copy the table from the internet like below:

E-mail address
Status
123@coldmail.com
Attempt #5. Deleted.
4@iwon.com
Attempt #4. Deleted.
123456@dfa.state.ny.us Attempt #4. Deleted.
aaa@yahoo.com Attempt #4. Deleted.

2. Paste special unformatted text into a new spreadsheet.
3. Delete any unwanted rows and columns like the title row and the status column above so that you have:
123@coldmail.com
4@iwon.com
123456@dfa.state.ny.us
aaa@yahoo.com
4. Select the cells with the data you want, and cut it from the spreadsheet.
5. Paste special making sure that the "Transpose" has a check mark to change this data set into a row like this:
123@coldmail.com
4r@iwon.com
123456@dfa.state.ny.us aaa@yahoo.com
6. File > Save As the file changing the save as a type to: Text CSV with a .csv extension to a location you know how to get to like your desktop.
7. Open the file you just saved in excel as a CSV file WITH notepad using the right mouse menu option "Open With" and selecting notepad either from that short list or from the "Choose Program" option.
8. Copy line of comma separated email addresses or the portion you wish to email to like this:
"123@coldmail.com ","4@iwon.com"," 123456@dfa.state.ny.us "," aaa@yahoo.com "
9. Paste this in either the to: or better yet the bcc: portion of a new email, type your email and send.

Viola!
Post a Comment

+