open_in_new content_copy 
function sendWeeklyEmail() {
  // Get today's date
  var currentDate = new Date();
  
  // Define the dates when the email should be sent (7th, 14th, 21st, and 28th of each month)
  var sendDates = [7, 14, 21, 28];
  
  // Check if today's date matches any of the send dates
  if (sendDates.includes(currentDate.getDate())) {
    // Replace placeholders with actual values
    var sheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/156drujrNlAnAv5dt3hyCqQcPuF285rvfIJrQzryIVt8/edit#gid=119876675").getSheetByName("MAILER");
    var range = sheet.getRange("B2:D12");
    var data = range.getValues();
    var backgrounds = range.getBackgrounds()

    // Prepare the HTML email body
    var htmlBody = "<html><body>";
    
    // Add desired text
    htmlBody += "<p>Dear Sir,</p>";
    htmlBody += "<p>Please find below the weekly RM Consumption of Dadri Unit. Sheet Metal & PU. </p>";
    
    // Add table with data and background colors
    htmlBody += "<table border='1' style='border-collapse: collapse;'>";
    for (var i = 0; i < data.length; i++) {
      htmlBody += "<tr>";
      for (var j = 0; j < data[i].length; j++) {
        var color = backgrounds[i][j];
        var formattedCell = "<td style='padding: 5px; ";
        if (color != "") {
          formattedCell += "background-color: " + color + ";";
        }
        formattedCell += "'>" + data[i][j] + "</td>";
        htmlBody += formattedCell;
      }
      htmlBody += "</tr>";
    }
    htmlBody += "</table>";
    
    // Add closing remarks
    htmlBody += "<p>Thank you.</p>";
    htmlBody += "</body></html>";

    // Send the email
    MailApp.sendEmail({
      to: "kunalsoni@meenakshipolymers.com",
      cc: "deshendra.sharma@meenakshipolymers.com,anju.rawat@meenakshipolymers.com,ashwani.kumar1@meenakshipolymers.com", 
      subject: "Weekly RM Consumption of DADRI Unit (Automail - Don't Reply) ",
      htmlBody: htmlBody
    });
  }
}