Send email in Appscript

gistfile1.txt

function sendPersonalizedEmails() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2"); // Get "Sheet 2" by name
  var data = sheet.getDataRange().getValues();  // Get all data in the sheet
  
  var subject = "Your Personalized Message"; // Email subject
  
  // Loop through each row starting from row 2 (to skip the header)
  for (var i = 1; i < data.length; i++) {
    var name = data[i][0];      // Name (column A)
    var email = data[i][1];     // Email (column B)
    var message = data[i][2];   // Message (column C)
    var sent = data[i][3];      // Sent status (column D)
    
    // Only send the email if it hasn't been sent yet
    if (sent.toLowerCase() !== 'yes') {
      var personalizedMessage = "Hi " + name + ",\n\n" + message + "\n\nBest regards,\nYour Company";

      // Send the email
      MailApp.sendEmail({
        to: email,
        subject: subject,
        body: personalizedMessage
      });
      
      // After sending the email, update the "Sent" column to "Yes"
      sheet.getRange(i + 1, 4).setValue('Yes');  // Set 'Yes' in column D (Sent status)
      
      Logger.log('Email sent to: ' + email);  // Log the email sent for debugging purposes
    }
  }
}