Mail Merge : Send mails in bulk from your Gmail account

Want to send mail automatically from Gmail/ send mail in bulk or send mail in a scheduled time?

Here is mail merge!! Using this, you can do all the above things. You can mail in bulk from your gmail account.

Mail merge works with a google apps script, which anyone can setup with their gmail account and use it. By using this, you can send personalised mails like – newsletter, invitations to a particular set of recipient or a particular mail-list.

Pre-Requisites:

Free Gmail Account or Google Apps Account

Limits:

Free Gmail: Daily limit: Max 500

Google Apps Account: Max 1500 daily. Sign up for a Google Apps account with your personalized domain(example.com) at https://goo.gl/82M7BC (Only INR 125 per user per month)

20% OFF on Google Apps Accounts for you with codes: W4YEWTD7QM4LLW6 and G4TH99FNGFAHVFX

Setting Up Mail Merge

Let’s set up the script for your gmail account.

Do not worry if you have no idea about google apps script. We will help you set up the mail merge script from scratch. Just follow the below steps one by one.

1. Go to your Google drive.
2. Create a new spreadsheet and name “Mail Merge”
3. Create the columns in the spreadsheet and paste the respective values:
   

   Email

   First Name

   Status

   Error Message

• Email – Paste all the email addresses(recipients) in this column.
• First Name- If you have the first name, then paste it in this column, if not I have also included extracting the first name from the email address into the script. It will be used in the mail for proper greeting.
• Status- It logs the status of the sent mail.
• Error Message- It will give you the mail failure reason(if any).

4. Now go to Tools > Script editor. This will open an apps script page for you attached to spreadsheet.
5. Name it mail merge. Now you have to copy the below script and paste it in the script page.

function onOpen()
{

 SpreadsheetApp.getUi().createMenu('Send Mail').addItem('Send','sendmail').addToUi();
 SpreadsheetApp.getUi().createMenu('First Name').addItem('Scrape','scrapefirstname').addToUi();
}

function sendmail()
{
 var selectsheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 var data = selectsheet.getDataRange().getValues();
 
 var drafts = GmailApp.getDraftMessages();
 var message = drafts[0].getBody();
 var subject = drafts[0].getSubject()
 Logger.log(message);
 for (var i = 1 ; i < data.length; i++)
 {
 try{
 if((data[i][3]!="Mail Sent")&& (data[i][3]!="Error"))
 {
 var recipient = data[i][0];
 var fname = data[i][1];
 
// Logger.log('recipient is' + recipient);
// Logger.log('subject is' +subject);
// Logger.log('First name is' +fname);
// Logger.log('message is' + message);
 GmailApp.sendEmail(recipient, subject, 'Dear '+ fname + '\n ' , {htmlBody: message});
 selectsheet.getRange(i+1,3).setValue("Mail Sent")
 }
 }
 catch(e)
 {
 selectsheet.getRange(i+1,3).setValue("Error").setNote(e.message).clearNote();
 selectsheet.getRange(i+1,4).setValue(e.message)
// Logger.log(e.message);
// Logger.log(e.lineNumber);
 }
 }
}


function scrapefirstname() 
{
var sheet = SpreadsheetApp.getActiveSpreadsheet();
 var data = sheet.getDataRange().getValues();
 for (var i = 1 ; i < data.length; i++)
 {
 Logger.log(data[i]);
 var names = data[i].map(function (n)
 { 
 var subs = n.substring(0, (n.indexOf("@")));
 var substr = n.substring(0, (subs.indexOf(".")));
 if (substr == "")
 return subs;
 else
 return substr;
 });
 Logger.log(names);
 var write = sheet.getActiveSheet().getRange(i+1, 2).setValue(names);
 }
}

It should look like this 

After pasting and saving this, you can see more menu items in your spreadsheet “Send Mail” and “First Name”. If you are not seeing this option, then refresh your spreadsheet and also check if you have saved the script in the script page.

First Name – This menu is to scrape the first name of the user from their email address. If you already have first name of the user, then no need to run this. Clicking on “First Name > Scrape” will fetch the first name of the users from their email address.

Send Mail – Send mail option is to trigger the sending mail. Clicking on “Send Mail > send” will start sending mails to the user listed under Email column.

Once, you have set the apps script with your gmail account, now lets write a message.

Write Your personalized message

You don’t need to go to the spreadsheet or script page to write a mail. Write the mail in your inbox, and the script will fetch the mail from your inbox.

1. Go to Your Inbox
2. Click on compose message.
3. Write your message with Subject title and save it in draft.

Note: Greeting will be added by the script “Dear FirstName” So no need to write while composing the message.

Send Mail/ Run the Script

Script picks up the first draft message, so please keep it in mind that while sending it should be the 1st message in drafts folder.

Now, as we have set everything, we can go to send mails.

If you don’t have the first name listed. Click on “First Name > Scrape”, It will fetch the first name under ‘First Name’ column.

Now, please check the below things before sending the mails to avoid any errors:

• Mails and First Name are present correctly on 1st and 2nd column.
• Check the mail, if that is present in the 1st under draft folder.

If everything is okay, click on “Send Mail > send”, it will start sending mail from the sheet.

If you face any issues, feel free to put it in the comments.