Automated Email- HTML

This post includes the Google Sheets interface and Apps Script code discussed in the YouTube video. For detailed explanations on how to use Apps Script and other specifics, please refer to the YouTube video below. You can create more visually engaging email content by using HTML.

Google Sheet

Create Columns.

Each column will contain the information needed to send the emails. For example, you may have columns like “Name,” “Email,” and a “Sent” column that tracks whether the email has been sent. Input columns (green) are “name” and “email” and output (yellow) is “sent”.

HTML

I will share some websites where you can preview your HTML code. These platforms allow you to test and view your HTML content live, making it easier to see how it will appear in a browser before sending it out. You can use these sites to check your email templates or any other HTML code for formatting and design issues.

https://www.w3schools.com/html/tryit.asp?filename=tryhtml_lists_intro

Apps Script code

You can further customize the subject (the subject line of the email) and body (the content of the email) in the code as per your specific needs. In the provided script, subject and body are highlighted in red in the code, and you can replace the text inside them with your own custom message.

function sendEmailHtml() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1"); // Replace "Sheet1" with the name of your sheet
  if (!sheet) {
    Logger.log("Sheet not found. Please check the sheet name.");
    return;
  }

  var nameRange = sheet.getRange("A2:A" + sheet.getLastRow()); // Names
  var names = nameRange.getValues();

  var emailRange = sheet.getRange("B2:B" + sheet.getLastRow()); // Email addresses
  var emails = emailRange.getValues();
  
  var emailSentRange = sheet.getRange("E2:E" + sheet.getLastRow()); // Sent status
  var emailSentValues = emailSentRange.getValues();

  var msgRange = sheet.getRange("D2:D" + sheet.getLastRow()); // Message content
  var messages = msgRange.getValues();

  const subject = "Important: Subject"; // subject
  const timeZone = Session.getScriptTimeZone();

  for (var i = 0; i < emails.length; i++) {
    const name = names[i][0];
    const email = emails[i][0];
    const sentStatus = (emailSentValues[i][0] || "").toLowerCase();

    // Skip entries where email is invalid or already marked as sent
    if (!email.includes("@") || sentStatus.includes("sent")) continue;

    // Define the HTML message with personalized name
    const htmlMessage = `
     
        <h1 style="background-color:DodgerBlue;text-align:center;border: 2px solid black;color:white;margin: 20px;padding: 20px;">
          Payment Due Notice
        </h1>
        <h2>General Information</h2>
        <p>Hello ${name}, <br> This is a general notification.</p>
        <h4>- Lists</h4>
        <ul>
          <li>List 1</li>
          <li>List 2</li>
          <li>List 3</li>
        </ul>
      
    `;

    try {
      // Send the email
      MailApp.sendEmail({
        to: email,
        subject: subject,
        htmlBody: htmlMessage         //********************** Html */
      });

      // Log the timestamp in the sheet, marking it as sent
      const timestamp = Utilities.formatDate(new Date(), timeZone, "MMM dd, yyyy HH:mm");
      sheet.getRange("E" + (i + 2)).setValue(`Sent at: ${timestamp}`);
      Logger.log("Email sent to: " + email);

    } catch (error) {
      Logger.log("Error sending email to " + email + ": " + error.message);
    }
  }
}