0

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 EMAIL_SENT = "EMAIL_SENT";
  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
      SpreadsheetApp.flush();
      }
    }
  }
}

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.