Google Docs Macros – Send Mail

With Google Docs, you can send personalized email in rich-text HTML format to a large group of people using Mail Merge. This snippet of Google Apps Script is responsible for sending the messages.

function labnolSendEmail() {
  
  var mySheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();  
  
  var emailSubject    = mySheet.getRange("E5").getValue();
  var emailSalutation = mySheet.getRange("E7").getValue();
  var emailBody       = mySheet.getRange("E9").getValue();
  var emailYourName   = mySheet.getRange("E11").getValue();
  var emailYourAddr   = Session.getUser().getEmail();
  var replyToAddr     = mySheet.getRange("E13").getValue();
  var file            = mySheet.getRange("E15").getValue();
  var attachmentID    = file?DocsList.getFileById(file):"";
  var emailBCC        = mySheet.getRange("E17").getValue();

  var myContact = mySheet.getDataRange().getValues();
  
  for (i=1; i < myContact.length; i++) {
    var person = myContact[i];
    if (person[0] != "" && person[1] != "" && person[2] != "OK") {
      var emailMsg = emailSalutation + " " 
            + person[0] + ", <br />" + emailBody;
      var advancedArgs = {htmlBody:emailMsg, 
            name:emailYourName, replyTo:replyToAddr};
      
      if (file)
          advancedArgs["attachments"] = attachmentID;
      
      if (emailBCC == "YES")
         advancedArgs["bcc"] = emailYourAddr;
      
      GmailApp.sendEmail(person[1], 
         emailSubject, emailMsg, advancedArgs);

      mySheet.getRange(i+1,3).setValue("OK");
    }
  }  
}