'Automatically Generating an Email Notification When Certifications are Expiring

I have an excel spreadsheet containing the expiration dates for all certifications for each member of my team. As the expiration approaches, the cell automatically color shifts based on the remaining validity of the certification.

What I am looking to do is send an email notification when the certification is expiring in 90 days or less.

function sendEmail() {
  // EMAIL Check & Date
  // Fetch Cert Dates & Employees
  var CertExpiration = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange(4,3,11,9); //All Cert Dates on Sheet
  var CertDate = CertExpiration.getValue(); // Get Certificate Date as a value
  var Employee = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange(4,2,9,1); //Retrieve list of employee Names
  var EmployeeName = Employee.getValue(); // Set variable for email notification
  var Today = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange(2,2,1,1); // Sets Today to Today() on sheet
  // Check Cert Expiration
  if (CertDate - Today < 90);  
    {
    // Fetch the email address
     var emailAddress = '[email protected]';
    // Send Alert Email.
    var message = 'Certificate for ' + EmployeeName + ' expiring soon'; //
    var subject = 'CERT EXPIRING SOON | CV CERT TRACKING';
    MailApp.sendEmail(emailAddress, subject, message);
   
    }
}

I would like this email to contain a list of all EmployeeName that match the condition (CertDate - Today <90) if possible. Additionally, a method by which to prevent the email from being sent multiple times. Currently, the email sends successfully, but only contains the first referenced EmployeeName.

Reference Document



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source