image crash when sending email with app script

Question

I am using the script to send the bithday email and here is my script.

/**
 * @OnlyCurrentDoc
 */

function sendMails() {
  var wrkBook = SpreadsheetApp.getActiveSpreadsheet();

  var employeeEmail = wrkBook.getSheetByName("Wish");
  var employeeCount = employeeEmail.getLastRow();
  var employeeList = employeeEmail.getRange(2,1,employeeCount,12).getValues();

  var day = new Date();
  var currentDate = Utilities.formatDate(day,SpreadsheetApp.getActive().getSpreadsheetTimeZone(),"yyyy-M-d")
  var year = Utilities.formatDate(day,SpreadsheetApp.getActive().getSpreadsheetTimeZone(),"yyyy")

  var name = "";
  var emailAddress = "";
  var ccEmail = "";
  var bccEmail = "";
  var imageURL = "";
  var signature = "";
  var message = "";
  var borderColor = "#000000";
  var backgroundColor = "#ffffff";
  var textColor = "#000000";

  for (var i=0;i<employeeList.length;++i){
    if (currentDate == year + "-" + employeeList[i][2]+"-"+employeeList[i][1]){
      var htmlContent = HtmlService.createHtmlOutputFromFile('view').getContent();

      var name = employeeList[i][0];
      var emailAddress = employeeList[i][3];
      var ccEmail = employeeList[i][4];
      var bccEmail = employeeList[i][5];
      var imageURL = employeeList[i][6];
      var signature = employeeList[i][7];
      var message = employeeList[i][8];

      //style
      // htmlContent = htmlContent.replace("border_color", borderColor);
      // htmlContent = htmlContent.replace("background_color", backgroundColor);
      // htmlContent = htmlContent.replace("text_color", textColor);

      //content
      htmlContent = htmlContent.replace("Employee_Name", name);
      htmlContent = htmlContent.replace("Dynamic_Message", message);
      htmlContent = htmlContent.replace("download", imageURL);
      htmlContent = htmlContent.replace("Dynamic_Signature", signature);

      //breakline
      htmlContent = htmlContent.replace("<br>", "\n");

      //Send out email
      MailApp.sendEmail(
        emailAddress, 
        "Happy Birthday " + name,
        htmlContent,{
          cc:ccEmail,
          bcc: bccEmail,
          htmlBody: htmlContent,
        }
      );
    }
  }
}

view.html

<!DOCTYPE html>
<html>

<head>
  <meta charset="utf-8">
  <title>Untitled Document</title>
</head>

<body>
    <table style="border:2px solid border_color; background-color: background_color; color: text_color;" width=750px>
      <tr>
        <p style="font-family: 'Freehand', cursive; font-size: 36px;">&nbsp; Dear Employee_Name</p><br><br>
      </tr>
      <tr>
        <p style="font-family: 'Roboto Slab', serif; font-size: 36px;"> &nbsp; Dynamic_Message </p><br>
      </tr>
      <tr>
        <img src="https://drive.google.com/uc?export=view&id=download" width="750" height="750" alt="" />
      </tr>
      <tr>
        <p style="white-space: pre-line">
          <p align=left, style="font-family: 'Pacifico', cursive; font-size: 36px;">Dynamic_Signature </p>
        </p>
        <br>
      </tr>

    </table>
</body>

</html>

google sheet table

However when I run this script with end up receive the email with crashed image

is there any solution to solve this? and also how should I change the code if I want to send the image with inline image instead of using this way.

First thing you should do is inspect the image's URL. Is it valid? Was access granted to that URL? In Chrome, just right-click the broken image and select 'Open image in new tab'; that should give you much of the information you need to analyze the problem. — Ruud Helderman, Dec 09 '22 at 14:41
https://stackoverflow.com/questions/31428198/send-e-mail-with-inlineimages — Cooper, Dec 09 '22 at 19:10

score 0 · Answer 1 · answered Dec 09 '22 at 18:08

The code and screenshots you show suggest that the final image src field in view.html will look like this:

https://drive.google.com/uc?export=view&id=14m3ix-ViXb9OTtAdHzJOOX8_5TMPq4HS

That is not a valid link to an image file. You can test it in a browser to see that it errors out with a 404 error.

To find the correct URL, try downloading the file from Drive to your local disk, and observe the URL reported by the Downloads window of the web browser you are using. Then modify the HTML file as required.

image crash when sending email with app script

1 Answers1