CODEWITHSUNDEEP
excelgoogle-apps-script
Janvier
Janvier

Reputation: 147

How can I send image attachment to email

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 }
        });  
 }

}

enter image description here

Upvotes: 1

Views: 344

Answers (2)

Emel
Emel

Reputation: 2442

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: "[email protected]", 
    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.

Upvotes: 1

Tanaike
Tanaike

Reputation: 201378

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.

References

Upvotes: 1

Related Questions