I have a Google Spreadsheet with a list of emails. I want to build a routine that send email automatically to those email addresses. I also want to attach different PDF/xlsx to this email as per the file name given in the google sheet. The PDF/xlsx file is located in my Google Drive.

When running the following script, I'm getting the following error:

Cannot retrieve the next object: iterator has reached the end. (line 31, file "Code")

I'm new to Google Spreadsheets, please can you help me out?

This is the code:

function launch_mail() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Mail 2')
  var startRow = 3  // First row of data to process
  var numRows = sheet.getLastRow();   // Number of rows to process
  var dataRange = sheet.getRange(startRow, 1, (numRows), 10)

  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[0];
    if (emailAddress != "") {
    var message = row[4];
      var file = DriveApp.getFilesByName(row[6]);
    var emailSent = row[5];
    var options = {
       cc: row[1],bcc: row[2],attachments: [file.next().getAs(MimeType.pdf)]
      if (emailSent != "EMAIL_SENT" && emailSent != "N/A"&& emailSent != "n/a") {  // Prevents sending duplicates
      var subject = row[3];
      MailApp.sendEmail(emailAddress, subject, message,options 
      sheet.getRange(startRow + i, 6).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.