CODEWITHSUNDEEP
htmlemailgoogle-apps-scriptgoogle-sheetsgmail
Hung Cao
Hung Cao

Reputation: 25

Advanced Send mail with formated in google app scripts - flexible content in mail

Im a new coder, my task is: Send formatted email with google-app-scripts, but different content base on who will be received email (like name, deadline, date, a little minor thing..) So im try to code this task, use both java-script and html but now im stuck here.

If i use this method: "MailApp.sendEmail(emailAddress, recipient, subject, message)" => content will change flexibly base on some var i set up. But this method cant format the content in email.

So i change to another method: "MailApp.sendEmail(emailAddress, subject, '',options)". In this method we can use html code to format the content in email, but we cant use var to change content flexibly.

So anyone understand deeply how to use var in html tag, please help me fix my code to work.

I will give you detail code, input, and output for co-help. As below: 

  function sendArticleCountEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.setActiveSheet(ss.getSheetByName("STEP2 Send-Emails"));
  var sheet= SpreadsheetApp.getActiveSheet()
  var startRow = 2;  
  var numRows = sheet.getLastRow();  
  var dataRange = sheet.getRange(startRow, 1, numRows, 
  sheet.getLastColumn());
  var data = dataRange.getValues();        

for (i in data) {
        var rowData = data[i];
        if(!rowData[1]) continue;
        if(rowData[3] != false) continue;
        var emailAddress = rowData[1];
        var recipient = rowData[0];
        var parameter1 = rowData[2];
        var subject = sheet.getRange(2,9).getValue();
        var nbd = sheet.getRange(2,10).getValue();
        var nkt = sheet.getRange(2,11).getValue();
        var deadline = sheet.getRange(2,12).getValue();

    var options = {
      htmlBody: <body aria-readonly="false" style="cursor: auto;"><span style="font-size:16px"><span style="font-family:verdana,geneva,sans-serif"><span style="background-color:rgb(255, 255, 255); color:rgb(34, 34, 34)">Dear <strong>recipient</strong>,<br />
    <br />
    We would like to share with you the schedule of next week, from <strong>nbd</strong> to <strong>nkt</strong> as below: </span></span></span><br />
    <br />
       parameter1   
    <br />
    <span style="font-size:16px"><span style="font-family:verdana,geneva,sans-serif"><span style="background-color:rgb(255, 255, 255); color:rgb(34, 34, 34)">Please take a look and feedback this mail to confirm that you cover these class information next week and deliver to your teachers.<br />
    Looking forward to seeing your <em><strong>CONFIRMATION ASAP</strong></em>, and please before <strong>14:00, deadline</strong>.<br />
    <br />
    Thanks &amp; best regards,<br />
    STEMHOUSE</span></span></span></body>
    };

    MailApp.sendEmail(emailAddress, subject, '',options)

    var completeRange = sheet.getRange(startRow, 1, numRows, sheet.getLastColumn()).check()
        }}

I use this function, it successfuly to send email but, as you see this image you will get my iuse. Thank you for spending time on this topic ! enter image description here

Upvotes: 1

Views: 896

Answers (1)

AMolina
AMolina

Reputation: 1379

Try changing your options to this:

  var options = {
      htmlBody: <body aria-readonly="false" style="cursor: auto;"><span style="font-size:16px"><span style="font-family:verdana,geneva,sans-serif"><span style="background-color:rgb(255, 255, 255); color:rgb(34, 34, 34)">Dear <strong>" + recipient + "</strong>,<br />
    <br />
    We would like to share with you the schedule of next week, from <strong>" + nbd + "</strong> to <strong>" + nkt + "</strong> as below: </span></span></span><br />
    <br />
       parameter1   
    <br />
    <span style="font-size:16px"><span style="font-family:verdana,geneva,sans-serif"><span style="background-color:rgb(255, 255, 255); color:rgb(34, 34, 34)">Please take a look and feedback this mail to confirm that you cover these class information next week and deliver to your teachers.<br />
    Looking forward to seeing your <em><strong>" + CONFIRMATION ASAP + "</strong></em>, and please before <strong>14:00, " + deadline + "</strong>.<br />
    <br />
    Thanks &amp; best regards,<br />
    STEMHOUSE</span></span></span></body>
    };

when you're putting, for example, this sees the entire HTML like a string, so when you put just recipients into it, it just sees it as the string "recipients", so it's not passing the actual variable.

Upvotes: 0

Related Questions