How to get the correct formatting in an Email when using Google Sheets and App Script-CodePudding

I'm trying to send a bunch of mails to a list of contacts that I have saved in Google Sheets. I copied the code to send the emails, but the emails that are sent aren't in the format that I need them in, and are getting converted to plaintext when being sent. Any help on how to get around this issue?

P.S. My coding knowledge is nil, and I just copied the code from this website : https://productivityspot.com/automatically-send-emails-from-google-sheets/

The code I used is below:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet1=ss.getSheetByName('Sheet1');
  var sheet2=ss.getSheetByName('Sheet2');
  var subject = sheet2.getRange(2,1).getValue();
  var n=sheet1.getLastRow();
  for (var i = 2; i < n 1 ; i  ){
  var emailAddress = sheet1.getRange(i,2).getValue();
  var name=sheet1.getRange(i,1).getValue();
  var message = sheet2.getRange(2,2).getValue();

  message=message.replace("<name>",name);
  MailApp.sendEmail(emailAddress, subject, message);
  }

CodePudding user response：

You are facing a couple hurdles here. The only way you can send a formatted email is with an HTML body. HTML is the markup language used to display a webpage in your web browser, and it can also be displayed by email clients.

So in order to send a nicely (HTML) formatted email from Apps Script, you must first generate the HTML formatted body, then you must include is using the htmlBody parameter. You should also always include a plain text body (as you are now), just incase you have a recipient who doesn't receive HTML email (common for recipients with accessibility needs, using screen readers etc.)

There is no simple way to fetch formatted text from a spreadsheet cell as HTML, so you will need to either store your raw HTML in the spreadsheet, or figure out another location you can store and fetch the HTML body from. In my example below, I hardcode both the plain text and html bodies.

To send an HTML Body with MailApp, you need to use the additional "options" parameter, as shown below.

var html_message = '<body><h1>This is an HTML formatted body</h1><br><br><strong style="color:red">This can get pretty involved.</strong> Usually you would generate this html with some other tool that lets you nicely format your content and export it as HTML.</body>'

var plain_message = 'This is a plain text email body.  It is is much simpler, with no special formatting, but usually it will include all the text from the  html formatted message (above), since usually you want all your recipients to receive the same information'.

MailApp.sendEmail(emailAddress, subject, plain_message, {htmlBody: html_message});

See the advanced options specification here:

https://developers.google.com/apps-script/reference/mail/mail-app#sendEmail(String,String,String,Object)