Use BCC in google script without placing e-mailadresses in the code itself

Question

I need to sent an e-mail to a group of people. Therefor I have a list of e-mailadresses set up in Spreadsheet, the Sheetname = Contacts.

The Weeknr-sheet is used to generate the required weeknumber in the subject.

I want to send this e-mail to everyone using the BCC, but I don't want to place all the e-mailadresses in the code itself (almost every solution gives that option), but extract them from the sheet.

My current code:

function sendEmail() {

    var originalSpreadsheet = SpreadsheetApp.getActive();

    var Weeknr = originalSpreadsheet.getSheetByName("Weeknr");

    var period = Weeknr.getRange("C2").getValues();

    var contacts = originalSpreadsheet.getSheetByName("Contacts");

    var emailTo = contacts.getRange("A2:A500").getValues();

    {

        var subject = " SUBJECT " + period;

        var message = " MESSAGE ";

        MailApp.sendEmail(emailTo, subject, message);

    }
}

score 0 · Answer 1 · answered Feb 23 '17 at 15:01

The below code should do the trick.However, note there is a limit (100per day for general user) on how many email you can send using mailApp each day.

function sendEmail() { 
var originalSpreadsheet = SpreadsheetApp.getActive(); 
var Weeknr = originalSpreadsheet.getSheetByName("Weeknr");
var period = Weeknr.getRange("C2").getValues(); 
var contacts = originalSpreadsheet.getSheetByName("Contacts"); 
var emailTo = contacts.getRange("A2:A500").getValues(); 
var emailsubject = " SUBJECT " + period ;
var message = " MESSAGE ";
MailApp.sendEmail({to: "youremailId@example.com",
             bcc: emailTo.join(","),// joins the emailto array to form a comma separated string of addresses
             subject: emailsubject,
             body: "message",

        }); 
        }

Thanks for that! Unfortunately the code won't run. It keeps saying that the running of the script is being prepared. But it won't sent... — Bart, Mar 03 '17 at 08:48
Could you check the execution transcript (view> execution transcript) after you try to run the script once and tell me what errors do you get? More details here https://developers.google.com/apps-script/troubleshooting — Jack Brown, Mar 03 '17 at 13:09

Use BCC in google script without placing e-mailadresses in the code itself

1 Answers1