GoogleAppsSCript mailSender

mailSender

function send() {

  var EMAIL_SENT = "EMAIL_SENT";
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('setting');
  var guests = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('list').getDataRange().getValues();

  var title = sheet.getRange('B2').getValue();

  var emailTemplate = sheet.getRange('B4').getValue();
  var auther = sheet.getRange('B3').getValue();
  emailTemplate = emailTemplate.replace('${auther}', auther);
  
  // Now send one to each person on the Guest List
  for (var g=1; g<guests.length; ++g) {
    var row = guests[g];
    var company = row[0];
    var first = row[1];
    var second = row[2];
    var emailAddress = row[3];
    var emailBody = emailTemplate.replace('${company}', company);
    var emailSent = row[5];
    
    if (emailSent != EMAIL_SENT) {
      emailBody = emailBody.replace('${first}', first);
      emailBody = emailBody.replace('${second}', second);
      try {
        MailApp.sendEmail(emailAddress, title, emailBody); 
        SpreadsheetApp.getActiveSpreadsheet().getSheetByName('list').getRange(g+1,5+1).setValue(EMAIL_SENT);
      } catch(e) {
        Browser.msgBox(first + second + "'s email address " + emailAddress + " is not a valid.");
      }
      SpreadsheetApp.flush();
    }    
  }
}