MAILMERGEGENERATOR

function sendMailMerge() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();
  
  if (data.length < 2) {
    SpreadsheetApp.getUi().alert("Not enough data in the sheet.");
    return;
  }
  
  const headers = data[0];
  const emailColIndex = headers.indexOf("Email Address");
  
  if (emailColIndex === -1) {
    SpreadsheetApp.getUi().alert("Could not find column named 'Email Address'. Please check your headers.");
    return;
  }
  
  const templateSubject = "Update for {{First Name}}";
  const templateBody = `Hi {{First Name}},\n\nHere is your custom link: {{Custom Link}}\n\nBest,\nTeam`;
  
  let emailsSent = 0;
  
  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    const emailAddress = row[emailColIndex];
    
    if (!emailAddress || String(emailAddress).trim() === "") continue;
    
    let currentSubject = templateSubject;
    let currentBody = templateBody;
    
    // Replace all {{Header}} with actual row data
    for (let j = 0; j < headers.length; j++) {
      const header = headers[j];
      const value = row[j] || "";
      
      const regex = new RegExp("\\{\\{" + escapeRegExp(String(header)) + "\\}\\}", "gi");
      currentSubject = currentSubject.replace(regex, value);
      currentBody = currentBody.replace(regex, value);
    }
    
    try {
      MailApp.sendEmail({
        to: String(emailAddress),
        subject: currentSubject,
        body: currentBody
      });
      emailsSent++;
    } catch (e) {
      Logger.log("Failed to send to " + emailAddress + ": " + e.toString());
    }
  }
  
  SpreadsheetApp.getUi().alert("Successfully sent " + emailsSent + " emails!");
}

function escapeRegExp(string) {
  return string.replace(/[.*+?^$\{\}()|[\]\\]/g, '\\$&'); // $& means the whole matched string
}

function onOpen() {
  SpreadsheetApp.getUi().createMenu('Mail Merge')
      .addItem('Send Emails', 'sendMailMerge')
      .addToUi();
}