BulkSend emails with respective attachments in Gmail using appscript

Question

I would like to send bulk google mails with attachments using a spreadsheet. In this spreadsheet, i have put the email address, the content template, and the respective attachment urls.

While i try and execute the below code, everything went well except for the line item #21 "attachments.push(file.getAs(MimeType.PDF));", which shows up an error. While i remove the line#21 and execute, i can send the bulk emails with a HTML Attachment instead of a PDF which contains virus and it is marked as spam.

Can someone help me with the correct code to execute the same and send a pdf directly instead of HTML

var EMAIL_SENT = "EMAIL_SENT";
function sendEmails() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2;  
var numRows = 3; 
var blobs = [];

var dataRange = sheet.getRange(startRow, 1, numRows, 5)

var data = dataRange.getValues();
for (var i = 0; i < data.length; ++i) {
var row = data[i];
var emailAddress = row[2]; 
var subject = "Attachments";       
var message = row[4];       
var options = {muteHttpExceptions:true};
var attachments = UrlFetchApp.fetch(row[3], options).getBlob();     
attachments.push(file.getAs(MimeType.PDF));
var emailSent = row[0];     
if (emailSent != EMAIL_SENT) {  
  
  GmailApp.sendEmail(emailAddress, subject, message, {attachments: attachments,});
  
  sheet.getRange(startRow + i, 1).setValue(EMAIL_SENT);
 
  SpreadsheetApp.flush();
    }
   }
  }

score 0 · Accepted Answer · edited Mar 16 '21 at 06:24

Modification points:

When I saw your script, I thought that the reason of your issue at attachments.push(file.getAs(MimeType.PDF)) is due to var attachments = UrlFetchApp.fetch(row[3], options).getBlob();. At this line, attachments is the HTTPResponse object. For this, your script of attachments.push(file.getAs(MimeType.PDF)) try to put file.getAs(MimeType.PDF) to attachments. By this, the error occurs. And, file is not declared.
And, when I saw your Spreadsheet, I noticed that the URL of the file is the link of the publicly shared file like https://drive.google.com/file/d/###/view?usp=sharing. From this situation, I thought that those files might be publicly shared or your files. If my understanding is correct, I thought that you might be able to be directly retrieved the file using Drive Service of DriveApp and the method getBlob. Furthermore, as you can see in GmailApp: sendEmail, the attachments parameter inside of the option parameter works with BlobSource variables, the same that returns getBlob.

When above points are reflected to your script, it becomes as follows.

Modified script:

var EMAIL_SENT = "EMAIL_SENT";

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;
  var numRows = 3;
  var dataRange = sheet.getRange(startRow, 1, numRows, 5);
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[2];
    var subject = "Attachments";
    var message = row[4];
    var fileId = row[3].split("/")[5];
    var emailSent = row[0];
    try {
      var file = DriveApp.getFileById(fileId);
      if (emailSent != EMAIL_SENT) {
        GmailApp.sendEmail(emailAddress, subject, message, { attachments: [file.getBlob()] });
        sheet.getRange(startRow + i, 1).setValue(EMAIL_SENT);
        // SpreadsheetApp.flush(); // I think that this might not be required to be used.
      }
    } catch(e) {
      console.log("File of " + row[3] + "cannot be retrieved. Error message is " + e.message);
    }
  }
}

Note:

In above script, when the file of URL (row[3]) cannot be retrieved, that URL can be seen at the log.

Reference:

Thank you for your response, i tried running the code and i am able to send the PDFs, but the problem here, Mail 1 has Number.1 attachment, Mail 2 has Number.1 and Number.2 attachments and mail 3 has Number.1, Number.2 and Number.3 attachments. While ideally mail 1 should have 1 attachment and Mail 2 should have Number.2 attachment and follows. Could you please help me with the same — Neethu Singuluri, Mar 07 '21 at 10:17
@Neethu Singuluri Thank you for replying. I apologize for the inconvenience. From your replying, I understood that you want to send an email by attaching the file of each row. By this, I updated my answer. Could you please confirm it? If I misunderstood your replying, I apologize again. — Tanaike, Mar 07 '21 at 12:43
Thank you so much for your support and time. I am able to execute the code with Zero errors. This is really helpful for me. Thank you again :) :) — Neethu Singuluri, Mar 08 '21 at 10:14
@Neethu Singuluri Thank you for replying and testing it again. I'm glad your issue was resolved. Thank you, too. — Tanaike, Mar 08 '21 at 12:18

BulkSend emails with respective attachments in Gmail using appscript

1 Answers1

Modification points:

Modified script:

Note:

Reference: