Google Apps Script Send Email via Spreadsheet

Question

I'm attempting to create a Google apps script that will send emails for me and I'm a bit stuck. I've searched around to no avail. The way I wish to do this is to send one single email with a CC'd list that never changes and for the bcc variable to reference a column on the spreadsheet, as that changes weekly.

Any help would be much appreciated. My attempt below yields errors as line 15 16 are not correct.

function sendEmails() {
 var sheet = SpreadsheetApp.getActiveSheet()
 var startRow = 2; // First row of data to process
 var numRows = 2; // Number of rows to process
 // Fetch the range of cells A2:C
 var dataRange = sheet.getRange(startRow, 1, numRows, 2);
 // Fetch values for each row in the Range.
 var data = dataRange.getValues();
 for (var i in data) {
   var row = data[i];
   var emailAddress = row[0]; // First column, includes Will.Henderson.
   var message = 'test';
   var subject = 'testy pants'
   var options = {
     cc = row[1]; // Second column is the listserv that never changes. You mad add folks here on occasion
     bcc = row[2]; // Third column include managers that change each week to bcc 
   }
  MailApp.sendEmail(emailAddress, subject, message);
 }
}

soMario soMario · Accepted Answer · 2020-08-26T23:55:32

Explanation: you are very close. The json object options should be defined like that:

var options = {
    cc : row[1], // Second column is the listserv that never changes. You mad add folks here on occasion
    bcc : row[2] // Third column include managers that change each week to bcc 
}

and then you can send the email like that:

MailApp.sendEmail(emailAddress, subject, message, options);

Complete Solution:

function sendEmails() {
 var sheet = SpreadsheetApp.getActiveSheet()
 var startRow = 2; // First row of data to process
 var numRows = 2; // Number of rows to process
 // Fetch the range of cells A2:C
 var dataRange = sheet.getRange(startRow, 1, numRows, 2);
 // Fetch values for each row in the Range.
 var data = dataRange.getValues();
 for (var i in data) {
   var row = data[i];
   var emailAddress = row[0]; // First column, includes Will.Henderson.
   var message = 'test';
   var subject = 'testy pants'
   var options = {
     cc : row[1], // Second column is the listserv that never changes. You mad add folks here on occasion
     bcc : row[2] // Third column include managers that change each week to bcc 
   }
  
   MailApp.sendEmail(emailAddress, subject, message, options);
   
 }
}

Google Apps Script Send Email via Spreadsheet

2 Answers

Complete Solution: