0

I have a perfect working script that substitutes a name for an email address and then sends that address an email. I only included the email part that's broken from adding bcc. I need to add several bcc recipients to it. But when I add them, the script keeps on sending, and enters "EMAIL_SENT" in a column and keeps on going down the column through all the blank rows like this. The script stops at the 1st blank row until I add a bcc. How can I add a bcc recipient to this and not send duplicate emails?

I added the argument for cc / bcc with a list of two emails and this makes it send duplicates.

/* Sends non-duplicate emails with data from the current spreadsheet.
 */
function SendEmail() {
  //if ()
  var esheet = SpreadsheetApp.getActiveSheet();
  var EMAIL_SENT = 'EMAIL_SENT';
  var startRow = 2; // First row of data to process
  var numRows = 1000; // Number of rows to process
  // Fetch the range of cells A4:C100
  var dataRange = esheet.getRange(startRow, 1, numRows, 3);
  // 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 message = row[1]; // Second column
    var emailSent = row[2]; // Third column
    if (emailSent != EMAIL_SENT) { // Prevents sending duplicates
      var subject = 'You have a new message in the message center.';
      MailApp.sendEmail(emailAddress, subject, message, {
  htmlBody: message,
  cc: 'boss@company.com',
  bcc: 'customers@home.com'
});
      esheet.getRange(startRow + i, 3).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}
// [END apps_script_gmail_send_emails_2]

I expect this to send an email and stop sending when it gets to a blank row. Instead it adds EMAIL_SENT all the way down the sheet row by row. This gets stuck in a never ending loop. It works perfect unless I add the BCC Part and I need to do that today.

ziganotschka
  • 25,866
  • 2
  • 16
  • 33

0 Answers0