Question 282959 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Email only sends if a cc is set

I’m trying to make my script send an email. The email has mail to and cc but the email wont send if the cc is missing. Not all my rows have a cc email. I want to send the email both with and without cc. Please help, I tried everything nothing works.

function myFunction() {
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Tasks").activate();

  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lr = ss.getLastRow();
  var now = new Date();
  
  for (var i = 3; i <= lr; i++) {
    var checkboxRange = ss.getRange(i, 9).getValue();
    var EMAIL_SENT = 'EMAIL_SENT';
    var EMAIL_SENTCC = 'EMAIL_SENTCC';
    var EmailAddress = ss.getRange(i, 12).getValue();
    const email = EmailAddress;

    var EmailAddressCC = ss.getRange(i, 13).getValue();
    const emailcc = EmailAddressCC;

    var currenttask = ss.getRange(i, 3).getValue();
    var EmailSent = ss.getRange(i, 7).getValue();
    var EmailSentCC = ss.getRange(i, 8).getValue();
    var daysleft = ss.getRange(i, 6).getValue();
    //var duedate = ss.getRange(i,5).getValue().getDate().getMonth();
    var date = new Date(ss.getRange(i, 5).getValue());
    var formattedDate = Utilities.formatDate(date, "GMT+4", 'E, MMM dd yyyy');


    var templatetext = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Template").getRange(1, 1).getValue();
    var emailtext = templatetext.replace()


    var subjectline = "Task Reminder: " + currenttask;
    var messageBody = templatetext.replace("{name}", EmailSent).replace("{title}", currenttask).replace("{task}", currenttask).replace("{date}", formattedDate).replace("{#}", daysleft)
    Logger.log(messageBody);

    var templatetext = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Template").getRange(1, 1).getValue();
    var emailtext = templatetext.replace()

    var message = {
      to: EmailAddress,
      subject: subjectline,
      body: messageBody,
      cc: EmailAddressCC,
      //replyTo:currentemail,
      name: "mfz scriptssCHANGE"
    }
    if (ss.getRange(i, 9).getValue() !== true) {
      if (EmailAddress && EmailSent !== EMAIL_SENT) {
        if (EmailAddressCC && EmailSentCC !== EMAIL_SENTCC) {
          //  let (ss.getRange(i,13).getValue()!==Error)
          // if the check box is (true) it will not send an email to that task
          // MailApp.sendEmail(EmailAddress,subjectline,messageBody);

          try {
            // MailApp.sendEmail(EmailAddress, EmailAddressCC, subjectline, messageBody,);
            MailApp.sendEmail(message);
          } catch (error) {
            console.log(error.message, error.stack);
            continue;
          }
        }
      }
    }
  }
}
Tags:

2

Answers


  1. 0

    Try this:

    var message = {
  to: EmailAddress,
  subject: subjectline,
  body: messageBody,
  //replyTo:currentemail,
  name: "mfz scriptssCHANGE"
}

if( EmailAddressCC !== "" ) message.cc = EmailAddressCC;
    Login or Signup to reply.
  2. 0

    Try this:

    function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Tasks");
  const vs = sh.getRange(3, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();
  const dt = new Date();
  const templatetext = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Template").getRange(1, 1).getValue();
  vs.forEach((r, i) => {
    var eMAIL_SENT = 'EMAIL_SENT';
    var emailAddress = r[11];
    const email = emailAddress;
    var emailAddressCC = r[12];
    var currenttask = r[2];
    var emailSent = r[6];
    var emailSentCC = r[7];
    var daysleft = r[5];
    var date = new Date(r[4]);
    var formattedDate = Utilities.formatDate(date, "GMT+4", 'E, MMM dd yyyy');
    var subjectline = "Task Reminder: " + currenttask;
    var messageBody = templatetext.replace("{name}", emailSent).replace("{title}", currenttask).replace("{task}", currenttask).replace("{date}", formattedDate).replace("{#}", daysleft)
    var templatetext = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Template").getRange(1, 1).getValue();
    var message = { to: emailAddress, subject: subjectline, body: messageBody, cc: emailAddressCC, name: "mfz scriptssCHANGE" }
    if (r[8] !== true) {
      if (emailAddress && emailSent !== eMAIL_SENT) {
        try {
          MailApp.sendEmail(message);
        } catch (error) {
          console.log(error.message, error.stack);
          continue;
        }
      }
    }
  });
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search