How do I send Google App Script email using HTML

Question

I have been trying to learn how to send script emails using html but I have a challenge with dynamic data and I need help.

I would like to check a google sheet for projects that ended and send this as a table via email script.

The sheet looks like this:

Here is the code that reads the spreadsheet:

function sendEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.setActiveSheet(ss.getSheetByName("Projects"));
  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getRange("A2:O20");
  var data = dataRange.getValues();
  for (i in data) {
    var rowData = data[i];
    var emailAddress = rowData[11];
    var recipient = rowData[8];
    var end_date = rowData[2];
    var message1 = 'Kindly note that the project'
    var message2 = 'was scheduled to end on' + ' ' + end_date +'. Please confirm the end date and if it needs to be readjusted. If the project is on course please fill the project end survey '
    var message3 = 'Thanks'
    var pro_name = rowData[4];
    var pro_code = rowData[5];
    var message = 'Dear ' + recipient + ',\n\n' + message1 + ' ' + pro_name + ' '  + message2 + '.\n\n'   + message3;
    var subject = 'Project End: ' + pro_name;
    MailApp.sendEmail(emailAddress, subject, message);
  }
}

I have come up with basic HTML file, and I am lost on how to link both and only send emails to a person whose project ended.

<div dir="ltr">Hi,
    <div>
        <br>
    </div>
    <div>
        <table cellspacing="0" cellpadding="0" dir="ltr" border="1" style="table-layout:fixed;font-size:10pt;font-family:Arial;width:0px;border-collapse:collapse;border:none">
            <colgroup>
                <col width="153">
                    <col width="125">
                        <col width="141">
                            <col width="100">
                                <col width="375">
                                    <col width="119">
                                        <col width="132">
            </colgroup>
            <tbody>
                <tr style="height:21px">
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;border:1px solid rgb(204,204,204)">Project_State</td>
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;border:1px solid rgb(204,204,204)">Start_Date</td>
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;border:1px solid rgb(204,204,204)">End_Date</td>
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;border:1px solid rgb(204,204,204)">Project_ID</td>
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;border:1px solid rgb(204,204,204)">Project_Name</td>
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;border:1px solid rgb(204,204,204)">Project_Code</td>
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;border:1px solid rgb(204,204,204)">Type</td>
                </tr>
                <? for(var i = 0; i < Data.length; i++) { ?>
                    <tr style="height:29px">
                        <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;border:1px solid rgb(204,204,204)"><?= proData[i].project_name ?></td>
                        <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;border:1px solid rgb(204,204,204)"><?= proData[i].project_code ?></td>
                        <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;text-align:center;border:1px solid rgb(204,204,204)">1<?= proData[i].end_date ?></td>
                    </tr>
                    <? } ?>
            </tbody>
        </table>
    </div>

Suggestion: Start by keeping the html simple and then progressively you can get more complex. — Cooper
– Cooper, Commented Jun 30, 2020 at 15:21
Use the version of MailApp.sendEmail(emailAddress, subject, message,{htmlBody:yourhtml}); — Cooper
– Cooper, Commented Jun 30, 2020 at 15:22
A lot of people use templated html:developers.google.com/apps-script/guides/html/templates — Cooper
– Cooper, Commented Jun 30, 2020 at 15:51

Aerials · Accepted Answer · 2020-07-03 15:27:04Z

3

To send HTML with Apps Script using the MailApp class you should use the options object to pass the advanced parameter htmlBody

This options object could include all the email message parameters or only the HTML for the message body.

An example:

function sendHtml() {
  // Send HTML content in email.

  var htmlBody = HtmlService.createHtmlOutputFromFile('your-html-file').getContent();

  MailApp.sendEmail({
    to: Session.getActiveUser().getEmail(),
    subject: 'Test Email markup - ' + new Date(),
    htmlBody: htmlBody,
  });
}

answered Jul 3, 2020 at 15:27

Aerials

4,4691 gold badge20 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Wicket · Accepted Answer · 2020-06-30 17:22:14Z

3

To use a HTML in a email message to be sent by using Google Apps Script you should use the options object.

This options object could include all the email message parameters or only the HTML for the message body.

References

answered Jun 30, 2020 at 15:51

Wicket

39.4k9 gold badges81 silver badges201 bronze badges

Collectives™ on Stack Overflow

How do I send Google App Script email using HTML

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related