How to send a mail merge using Google sheets for free

Did you know that sending a mailmerge from Gmail can be done for free? Here are easy steps to send an email mail merge for free without any plugin or add on.

Create a Google sheet

Create a google sheet. We will be using a Google sheet to send a mail merge. Sign in to your Gmail account and visit drive.google.com, right-click at any place and create a new spreadsheet.

Create Columns inside the Google sheet

Create 4 columns inside the Google sheet, each having the following headings.

• Name
• Email subject
• Email body
• Sent or not

Adding code via script editor

Go to tools-> script editor and add the following script, save it as email_sender. in the code replace Muthali Ganesh <muthali@gmail.com> with your name and email id.

If you have multiple aliases replace the text in bold with the name and email ID of the alias you want to send from.

Go to tools-> script editor and add the following script, save it as email_sender. in the code replace Muthali Ganesh <muthali@gmail.com> with your name and email id. If you have multiple aliases mention the alias you want to send from.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Custom Menu')
      .addItem('Send Email', 'sendEmails2')
      .addToUi();
}


// This constant is written in column C for rows for which an email
// has been sent successfully.
var EMAIL_SENT = 'EMAIL_SENT';
var aliases = GmailApp.getAliases();
Logger.log(aliases); //returns the list of aliases you own
Logger.log(aliases[0]); //returns the alias located at position 0 of the aliases array


/**
 * Sends non-duplicate emails with data from the current spreadsheet.
 */
function sendEmails2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2; // First row of data to process
  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(startRow, 1, sheet.getLastRow() - startRow + 1, 4);
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[0]; // First column
    var subject = row[1]
    var message = row[2]; // Second column
    var emailSent = row[3]; // Third column
    if (emailSent != EMAIL_SENT) { // Prevents sending duplicates
//      MailApp.sendEmail(emailAddress, subject, message);
      GmailApp.sendEmail(emailAddress, subject, message,{'from': "Muthali Ganesh <muthali@expertrec.com>"});
      sheet.getRange(startRow + i, 4).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}

Run the onOpen function from the script editor

Go to select function-> and run onOpen function.

Authorize the APP

Authorize the app. Ignore any warning if any.

Paste emails, subject and body text to the relevant columns

Now go to your sheet, paste the emails, under the column emails, subject under the column subject and body of the email under the body column.

You can use the concatenate function to create a personalized body for every mail. For example, if you like to attach the name of people to each email.

Send emails

Go to custom menu-> click on send emails.

Once an email is sent, under the sent or not column, you will get a message saying EMAIL_SENT.

If you want to resend, delete all the EMAIL_SENT, go to custom menu-> send emails.