google-apps-script - 根据单元格中的值发送带有或不带有内联图像的电子邮件

Question

下午好。我正在使用下面的代码发送电子邮件，其中包含通过 ID 从电子表格中的单元格中检索到的内联图像。但是，如果具有内嵌图像 ID 的单元格为空白，则脚本不会发送消息。有没有办法调整此代码以使其在两种情况下都能正常工作？（在单元格中有和没有 ID 值）谢谢

  var ss = SpreadsheetApp.getActive().getSheetByName('SendMail')
  var lr = ss.getLastRow();

  var quotaLeft = MailApp.getRemainingDailyQuota();
  //Logger.log(quotaLeft);

  if((lr-1) > quotaLeft) {
     Browser.msgBox("You have " + quotaLeft + " left and you're trying to send " + (lr-1) + " emails. Emails were not send.");
  } else {



    for (var i = 2;i<=lr;i++){


      var currentEmail = ss.getRange(i, 1).getValue();
      var currentSubject = ss.getRange(i, 2).getValue();
      var templateText = ss.getRange(i, 3).getValue();
      var currentname = ss.getRange(i, 4).getValue();
      var reply = ss.getRange(i, 5).getValue();


      Logger.log("Cell: " + ss.getRange(i, 6).getA1Notation());
      Logger.log("Image: "+ DriveApp.getFileById(ss.getRange(i, 6).getValue()));

      var image = DriveApp.getFileById(ss.getRange(i, 6).getValue()).getBlob();

      var message = templateText.replace("{name}",currentname);
      message += "<br/><br/><img src=\"cid:sampleImage\">";

      MailApp.sendEmail({

        to: currentEmail,
        replyTo: reply,
        subject: currentSubject,
        htmlBody: message,
        inlineImages: {sampleImage: image},    
      });


    } //close for loop

  } //close else statement

} //close sendEmails```

标签： google-apps-script

Answer 1

这应该这样做。

如果图像 ID 不正确或文件格式不正确，这些更改也会捕获和可能发生的错误。

  function sendMail(){

  var ss = SpreadsheetApp.getActive().getSheetByName('SendMail')
  var lr = ss.getLastRow();

  var quotaLeft = MailApp.getRemainingDailyQuota();
  //Logger.log(quotaLeft);

   if((lr-1) > quotaLeft) {
     Browser.msgBox("You have " + quotaLeft + " left and you're trying to send " + 
    (lr-1) + " emails. Emails were not send.");
   } else {

    for (var i = 2;i<=lr;i++){

  var currentEmail = ss.getRange(i, 1).getValue();
  var currentSubject = ss.getRange(i, 2).getValue();
  var templateText = ss.getRange(i, 3).getValue();
  var currentname = ss.getRange(i, 4).getValue();
  var reply = ss.getRange(i, 5).getValue();
  var imageFile = ss.getRange(i, 6).getValue();
  var image = ""
   Logger.log("Cell: " + ss.getRange(i, 6).getA1Notation());
   Logger.log("Image: "+ DriveApp.getFileById(ss.getRange(i, 6).getValue()));

    if(imageFile){
     try{
    image = DriveApp.getFileById(imageFile).getBlob();
      } catch (e) {
   templateText += "<p>Image merge failed: " + e;         
   }
  }

  var message = templateText.replace("{name}",currentname);
  message += "<br/><br/><img src=\"cid:sampleImage\">";

  if(image){
  MailApp.sendEmail({
    to: currentEmail,
    replyTo: reply,
    subject: currentSubject,
    htmlBody: message,
    inlineImages: {sampleImage: image},    
  });

    } else {

  MailApp.sendEmail({
    to: currentEmail,
    replyTo: reply,
    subject: currentSubject,
    htmlBody: message,  
  });
}

    } //close for loop

  } //close else statement

 } //close sendEmails```