CODEWITHSUNDEEP
javascripthtmlfor-loopgoogle-apps-script
willy
willy

Reputation: 43

How do I send Google App Script email using HTML

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:

enter image description here

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>

Upvotes: 1

Views: 2863

Answers (2)

Aerials
Aerials

Reputation: 4419

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,
  });
}

Upvotes: 3

Wicket
Wicket

Reputation: 38131

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

Related

Upvotes: 3

Related Questions