Monday, March 14, 2011

Sending a mass email to your facebook friend list, the excel-lent way!

Notes: Export contacts from facebook



Facebook is a great tool to connect with people. However, there are times when it get's really frustrating.

The other day, I wanted to drop a mass mail to all my friends in Singapore. So I went to the messaging system, tried to enter "Singapore", hoping facebook would be smart enough to populate the To-List with all my contacts with current city as Singapore.

Alas, it didn't happen. Perhaps I was expecting a lot. So the next thing I did was create a friend list, manually choosing all the people I knew who still lived in Singapore, into it. The exercise took ~5-10 mins with around 200+ friends.



I went back to facebook messaging tool, composed a message, and added "Friends in Singapore" in the to list. Facebook identified it as a friend list, but prompted that I couldn't send a message to more than 20 friends.

I don't know why fb would think putting a limit of 20 on a message is smart. Anyways, I was losing patience. I remembered facebook announcing a data export tool, to export all your information -contacts, pictures etc. You can find it under account settings



However, since it exports everything in one go, it takes a while. And I wonder how big the zip file would be, since it contains all my fb activity since I first opened my account.

I had heard of a chrome extension that helps you do the same, but it has the same issues. I can't selectively export out a list. When you have 500+ friends on facebook, it's a pain to sort through the list again to identify folks you want to send your message to.

So this is what I finally did. Yahoo, (once a great email system) has a nifty contact import tool, which can pull contacts from facebook directly. It is fast, since it restricts itself to email ids only.



Once imported, you can't use yahoo to send emails to 200+ people. It will also give you a "too many contacts selected" error. Export your contacts out into CSV. Now the Excel Magic begins.

I went to my "Friends in Singapore" list I had created, dragged my mouse, selected all the names in the page, and copied them into my notepad. Went to excel and copied them again. Opened the contacts from the CSV into the next tab. Did a vlookup of the list against the entire contacts and extracted the names and their email ids. Took another 5 mins.

Wrote a small formula that added quotes, brackets and semicolons to each, to make it email friendly.


 Copied the entire column and pasted it directly into my gmail To list, and sent the message. Now after 15 mins of effort, which also included numerous failed attempts, I now have all my contacts in a small excel sheet, which can be used again for other purposes. I could have written a small script, but not this time.

People underestimate Microsoft Excel but it can do some really smart things, really quickly, if you only know how to use it :)

Do let me know if there are better ways to selectively export contacts in a list from facebook. Happy Surfing!

2 comments:

Anonymous said...

Very good information. I was pulling my hair out trying to figure out how to send bulk emails to my FB friends. Any chance of getting the excel formulas you mentioned in this article?

Hari said...

Thanks. nice info. Here is a article which can even convert the Facebook contact list into CSV.
Export Facebook contacts to CSV