How can we send automatic emails using Google Sheets?

 

Example sample data 

 

Add this Script in AppsScript of google sheet where above data is exits

function sendEmailsFromSheet() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var lastRow = sheet.getLastRow();

  // Start from row 2 to skip header

  var data = sheet.getRange(2, 1, lastRow - 1, 4).getValues();

  data.forEach(function(row, index) {

    var name = row[0];

    var email = row[1];

    var subject = row[2];

    var message = row[3];

    // Skip empty rows

    if (!email || !subject || !message || !name) return;

    var emailBody =

      "Hello " + name + ",\n\n" +

      message + "\n\n" +

      "Regards,\n" +

      "Your Name";

    MailApp.sendEmail({

      to: email,

      subject: subject,

      body: emailBody

    });

    // Optional: mark email as sent in column E

    sheet.getRange(index + 2, 5).setValue("Sent at "+getTimestampString());

  });

}

function getTimestampString() {

  return Utilities.formatDate(

    new Date(),

    Session.getScriptTimeZone(),

    "yyyy-MM-dd HH:mm:ss"

  );

}