CODEWITHSUNDEEP
javascripthtmlgoogle-apps-script
user3117534
user3117534

Reputation: 53

Using spreadsheet values in html body of email with GmailApp.sendEmail

I have a script that sends an email using GmailApp with an html body. I would like the html body to include specific values from the Spreadsheet that contains it, but each time the email is sent, the scriptlet I add is displayed as plain text.

email.gs:

function sendEmail() {



  var mailTo = '[email protected]'


  var NAME = SpreadsheetApp.getActiveSheet().getRange('Sheet1!A1').getValue();


  var subject = 'Hello!'
  var html = HtmlService.createHtmlOutputFromFile('body').getContent();



  GmailApp.sendEmail('', subject, '', {


    name: 'Friend',
    htmlBody: html,
    bcc: mailTo


  });


}

body.html:

<h1>Good Evening, <? NAME ?> </h1>
<h3>Hello Friend!</h3>

"NAME" should be pulled from the specified cell in the sheet whenever the script is run.

I am guessing that the reason it is displaying as text is GmailApp.sendEmail pastes the raw html into the email and assumes the client will read it, so how could I first get it to process through the script and then paste into the email?

Upvotes: 2

Views: 7491

Answers (2)

TheWizEd
TheWizEd

Reputation: 8598

Or you could use createTemplateFromFile and note "=" in 

function sendEmail() {
  var mailTo = '[email protected]'
  var subject = 'Hello!'
  var html = HtmlService.createTemplateFromFile('body');
  html.NAME = SpreadsheetApp.getActiveSheet().getRange('Sheet1!A1').getValue();
  html = html.evaluate().getContent();

  GmailApp.sendEmail('', subject, '', {
    name: 'Friend',
    htmlBody: html,
    bcc: mailTo
  });

}

<h1>Good Evening, <?= NAME ?> </h1>
<h3>Hello Friend!</h3>

Upvotes: 10

Redskins and Tesla
Redskins and Tesla

Reputation: 133

You have the html body in a different file, and is not accessible by the NAME variable. Try this.

function sendEmail() {



  var mailTo = '[email protected]'


  var NAME = SpreadsheetApp.getActiveSheet().getRange('Sheet1!A1').getValue();

var bodyText = "<h1>Good Evening,"+ NAME +" </h1><br><h3>Hello Friend!</h3>"

  var subject = 'Hello!'


  GmailApp.sendEmail('', subject, '', {


    name: 'Friend',
    htmlBody: bodyText,
    bcc: mailTo


  });


}

Upvotes: 1

Related Questions