Google Sheets – Fix Email Formatting Issue with Image Signature

gmailgoogle sheetsgoogle-apps-script

Sending emails from Google Sheets I have a compiled text (with line breaks) which is stored in var message. I have been trying to include a picture for the signature. To display it, I used the HTML body in the options parameter (as suggested here). This is the code:

function sendEmails() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange(1, 2);  // Fetch the range of cells B1:B1
var subject = range.getValues();   // Fetch value for subject line from above range
var range = sheet.getRange(1, 9);  // Fetch the range of cells I1:I1
var numRows = range.getValues();   // Fetch value for number of emails from above range
var startRow = 4;                  // First row of data to process
var dataRange = sheet.getRange(startRow, 1, numRows,9 ) // Fetch the range of cells A4:I_
var data = dataRange.getValues();  // Fetch values for each row in the Range.
var googleLogoUrl = "http://die-masterarbeit.de/media/grafik/logo.png";
var googleLogoBlob = UrlFetchApp
                     .fetch(googleLogoUrl)
                     .getBlob()
                     .setName("googleLogoBlob");
for (i in data) {
var row = data[i];
var emailAddress = row[0];      // First column
var message = row[8];           // Ninth column

MailApp.sendEmail(emailAddress,
             subject,
             message,
{
name: 'Peter Peschel',
htmlBody: "<img src='cid:googleLogo'>",
inlineImages:
  {
    googleLogo: googleLogoBlob,
  }
})};
}

What happens is that the message variable is not displayed at all. I have experimented in calling on the message variable in the htmlbody again, but this completely takes out the formatting (especially line breaks) of the message.

MailApp.sendEmail(emailAddress,
             subject,
             message,
{
name: 'Peter Peschel',
htmlBody: message + "<img src='cid:googleLogo'>",
inlineImages:
  {
    googleLogo: googleLogoBlob,
  },
})};
}

Any suggestions on how to best display the image while keeping the formatting of the message?

Best Answer

You are using the format sendEmail(recipient, subject, body, options) of the MailApp.sendEmail method. As the documentation says, if options include htmlBody, it will be used instead of the body parameter. This is why your first attempt ignored "message".

Since you need to send an HTML message, its content must be formatted as HTML; in particular, line breaks should be <br> instead of the control character \n.

Solution: replace \n with <br> in the message body. Here is an example of a script sending the content of cell A1. It provides plainMessage in case the recipient's email client does not render HTML, and htmlMessage with inline image 

function sendA1() {
  var plainMessage = SpreadsheetApp.getActiveSheet().getRange(1,1).getValue();
  var htmlMessage = message.replace(/\n/g, '<br>') + "<img src='cid:googleLogo'>"; 
  MailApp.sendEmail('user@domain.com', 'my subject', plainMessage, { 
    htmlBody: message,
    inlineImages:
      {
        googleLogo: googleLogoBlob 
      }       
  }); 
}

By the way, since you are using options extensively, it would make sense to switch to the format sendEmail(message) where message is an object holding all the parameters. This makes for a more readable code: 

MailApp.sendEmail({
  to:       'user@domain.com', 
  subject:  'my subject', 
  body:     plainMessage, 
  htmlBody: message,
  inlineImages:
    {
      googleLogo: googleLogoBlob 
    }
});
Related Topic