Poor Man’s Mail-Merge with Gmail

Here’s how you can send a lot of individual personal messages if all you have is a GMail or Google Apps account.

One of the key ways for successful startups or new IT projects is knowing your customers (and partners) and being available to them – so you can quickly collect feedback, learn more about what they are trying to achieve with your platform, and just make sure that they know who to contact when they need to.

Instructions below will help you set up your personal gmail account or Google Apps account to send personalized email like the one you see on the right. Note that this is as personalized as it gets with everything originating from your address (no on-behalfs) and the recipient being the only person in the To list (no BCCs or users seeing addresses of each other).

Phase One: Simple Email Script

1. Open the Google Script developer environment by going to http://script.google.com and clicking the Start Scripting button,

2. Close the welcome screen and you will get your first script created for you:

3. Click the Untitled project name at the top left and give it a proper name – this file will get added to your Google Docs.

4. The simplest script that sends an email to someone will likely look like:

Every email needs to have a recipient, subject and body (the text in the email). Note that you need to add ‘\n\’ at the end of each line to have more than one line in your text. Also, note that besides the plain text – you can have nicely formatted HTML email text – see how I did that in the htmlbody variable.

5. Now click File / Save, and then Run / YourFunctionName to run the script.

The first time you run the script you will be getting a couple prompts to confirm access to gmail. After that the script will run and send the email.

Phase Two: Multiple Emails and Names

Running a script to send just one email is kind of meaningless. Lets modify the script quickly to send many emails at once:

1. Add arrays with names and addresses – e.g.:

var names = ['Bill', 'Susan']
var addresses = ['bill@test.com', 'susan@test.com']

2. Put everything in a FOR loop that goes through the lists:

for (var i=0; i<names.length; i++){
}

3. Use names[i] to put the name into subject and body (if you want) and addresses[i] instead of the recipient in the Gmail call. You code will likely look like:

Phase Three: Get Recipients from a Spreadsheet

Now, if you want to have a spreadsheet rather than the list of recipients in the code, this is also fairly easy to do. Here’s how.

1. Create a Google Spreadsheet like this one and save it:

2. In your script, add the code that opens the spreadsheet (you will the Id of the spreadsheet – copy it from the URL of the spreadsheet in the browser):

var sheet = SpreadsheetApp.openById("1speapm4TJpG5SxRFC94RIkpTzf6vAiZUBGoT7Xrk6Iw");
var data = sheet.getDataRange().getValues();

3. Change the loop to iterate through the cells of the spreadsheet instead of the preset array:

for (var i = 0; i < data.length; i++) {

4. Instead of name use use data[i][0] and instead of email address use data[i][1].

 

That’s it. Now you can send multiple personalized emails from your very own Gmail/Google Apps account.