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.