How can I send image attachment to email

Question

As I have created list of qr codes to scan and then I want to send this images to email by using Google app script.

current result

image is not sent to gmail. codes

 function sendEmail(sheetName = "emails") {
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName).activate();
  var spreedsheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = spreedsheet.getLastRow();
  //var ssa = SpreadSheetApp().getSheetByName(sheetName);  // spread sheet object

const messageBody = "Dear receiver, We would like to invite you on upcoming seminar please use this link {actual link} and show receiptionis this bar code {actual code}"
const subjectText = "Seminar invitation"

 // loop rows or spread sheet
  for (var i=2; i<= lastRow; i++) {
    var receiverName =  spreedsheet.getRange(i, 1).getValue();
    var receiverEmail = spreedsheet.getRange(i, 2).getValue();
    var seminarLink =  spreedsheet.getRange(i, 3).getValue();
    var barcode =  spreedsheet.getRange(i, 4).getBlob();
    var msgBdy = messageBody.replace("receiver",receiverName).replace("{actual link}", seminarLink);

      var photoBlob = barcode
     MailApp.sendEmail({
                to: receiverEmail,
                subject: subjectText,
                htmlBody: messageBody + "<br /><img src='cid:qrCode'>",
                inlineImages: { qrCode: photoBlob }
        });  
 }

}

About `I have created list of qr codes to scan`, in your situation, how did you create your QR codes? For example, I thought that if those are from the URL, the image can be directly retrieved from the URL using Google Apps Script. — Tanaike, May 27 '22 at 12:09
Oh, my bad for description, images are created from =IMAGE("https://chart.googleapis.com/chart?cht=qr&chs=150x150&chl="&C2) with that google sheet excel @Tanaike — Janvier, May 27 '22 at 12:59
Thank you for replying. From your reply, I could understand that your goal can be achieved using Google Apps Script. So, I proposed a modified script as an answer. Could you please confirm it? If that was not useful, I apologize. — Tanaike, May 27 '22 at 13:18

Emel · Answer 1 · 2022-05-30T13:23:24.543

Update 2

As said in the comments, replace this line:

var barcode =  spreedsheet.getRange(i, 4).getBlob();

To:

var barcode = UrlFetchApp.fetch(spreedsheet.getRange(i, 4).getFormula().match(/\"(.*?)\"/)[1]).getAs('image/png')

Update

As you are inserting the image via a IMAGE Formula, you can easily parse the value, obtain the URL and get the Blob via UrlFetchApp

Sample:

const sS = SpreadsheetApp.getActive()

function testSendImage() {
  const img = sS.getRange('A1').getFormula()
  const urlImg = img.match(/\"(.*?)\"/)[1]
  const fetchImage = UrlFetchApp.fetch(urlImg).getAs('image/png')
  MailApp.sendEmail({ 
    to: "some@gmail.com", 
    subject: "QR", 
    htmlBody: 'Check this QR <img src="cid:fetchImage" />', 
    inlineImages: { fetchImage: fetchImage } })
}

In the current state, there is no method for extrancting the image from a cell, however there is a Feature Request on Google's Issue Tracker, you can click here to review it.

Remember to click in the top right if you want this feature to be implemented.

In any case, you review this StackOverflow question for workarounds or alternative methods.

Thanks @Emel I sends image but it is a default image all receiver get the same QR Code whilst they are different in excel cells — Janvier, May 27 '22 at 13:36
Sure, this is only a `How to do it` you have to tweak it to your script. — Emel, May 27 '22 at 13:42
From what I see, you only have to replace `var barcode = spreedsheet.getRange(i, 4).getBlob();` with `var barcode = spreedsheet.getRange(i, 4).getFormula().match(/\"(.*?)\"/)[1]` an add `barcode = UrlFetchApp.fetch(urlImg).getAs('image/png')`, o directly `var barcode = UrlFetchApp.fetch(spreedsheet.getRange(i, 4).getFormula().match(/\"(.*?)\"/)[1]).getAs('image/png')` @Janvier Check the answer update — Emel, May 27 '22 at 13:47

score 1 · Answer 2 · answered May 27 '22 at 13:17

From your following reply,

images are created from =IMAGE("chart.googleapis.com/…) with that google sheet excel

In this case, I thought that your goal can be achieved using Google Apps Script.

When I saw your script, getValue() is used in a loop. In this case, the process cost will become high. In this modification, this issue is also modified.

Modified script:

function sendEmail(sheetName = "emails") {
  var spreedsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var lastRow = spreedsheet.getLastRow();
  const messageBody = "Dear receiver, We would like to invite you on upcoming seminar please use this link {actual link} and show receiptionis this bar code {actual code}";
  const subjectText = "Seminar invitation";
  const range = spreedsheet.getRange("A2:D" + lastRow);
  const values = range.getValues();
  const formulas = range.getFormulas();
  values.forEach(([receiverName, receiverEmail, seminarLink], i) => {
    const url = formulas[i][3].split('"')[1];
    const photoBlob = UrlFetchApp.fetch(url).getBlob();
    var msgBdy = messageBody.replace("receiver", receiverName).replace("{actual link}", seminarLink);
    MailApp.sendEmail({
      to: receiverEmail,
      subject: subjectText,
      htmlBody: messageBody + "<br /><img src='cid:qrCode'>",
      inlineImages: { qrCode: photoBlob }
    });
  });
}

When this script is run, the values are retrieved from "A2:D" of the sheet. And, each value is retrieved from each row. In the case of the column "D", the URL is retrieved from the formula, and the image blob is retrieved using UrlFetchApp. And, those values are put as the email.

How can I send image attachment to email

current result

2 Answers2

Update 2

Update

Modified script:

References