Untitled

function sendBulkEmails() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var startRow = 2; // Start from the second row
    var dataRange = sheet.getRange(startRow, 1, sheet.getLastRow() - 1, sheet.getLastColumn());
    var data = dataRange.getValues();
    
    // Define your email template with HTML formatting
    var emailTemplate = 
        "Good Day, {{Name}}!<br><br>" +
        "I hope this email finds you well. I am pleased to inform you that your Certificate of Membership is now ready.<br><br>" +
        "Attached to this email, you will find your Certificate of Membership, which recognizes your commitment to the field of civil engineering and your active participation in PICE-NUFSC.<br><br>" +
        "Please keep this certificate in a secure place, as it serves as official documentation of your membership.<br><br>" +
        "Once again, congratulations on becoming a part of PICE-NUFSC! We look forward to your active involvement and contributions to our community.<br><br>" +
        "Stay tuned for announcements regarding the upcoming membership orientation. We are excited about the year ahead!<br><br>" +
        "You can check your e-certificates via:<br><br>" +
        "PDF Version: {{File Link PDF}}<br>" +
        "PNG Version: {{File Link PNG}}<br><br>" +
        "Regards,<br><br>" +
        "HANNA CASSANDRA CABALSA<br>" +
        "Vice President for Internal Affairs<br>" +
        "Philippine Institute of Civil Engineers - National University Fairview Student Chapter<br><br>" +
        "<img src='{{Footer Image URL}}' alt='Footer Image' style='width: 100%; height: auto;' />";

    data.forEach(function(row) {
        var name = row[0]; // Column A: Name
        var emailAddress = row[1]; // Column B: Email
        var pdfLink = row[3]; // Column D: File Link PDF
        var pngLink = row[4]; // Column E: File Link PNG
        var footerImageUrl = "URL_TO_YOUR_IMAGE"; // Replace with the URL of your footer image
        var subject = "Your Certificate of Membership"; // Customize subject
        
        // Log details for debugging
        Logger.log("Sending email to: " + name + " (" + emailAddress + ")");
        
        // Replace placeholders in the template
        var message = emailTemplate
            .replace("{{Name}}", name)
            .replace("{{File Link PDF}}", pdfLink)
            .replace("{{File Link PNG}}", pngLink)
            .replace("{{Footer Image URL}}", footerImageUrl);

        // Send the email with HTML body
        MailApp.sendEmail({
            to: emailAddress,
            subject: subject,
            htmlBody: message
        });
    });
}