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
    var emailTemplate = 
        "Good Day, {{Name}}!\n\n" +
        "I hope this email finds you well. I am pleased to inform you that your Certificate of Membership is now ready.\n\n" +
        "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.\n\n" +
        "Please keep this certificate in a secure place, as it serves as official documentation of your membership.\n\n" +
        "Once again, congratulations on becoming a part of PICE-NUFSC! We look forward to your active involvement and contributions to our community.\n\n" +
        "Stay tuned for announcements regarding the upcoming membership orientation. We are excited about the year ahead!\n\n" +
        "You can check your e-certificates via:\n\n" +
        "PDF Version: {{File Link PDF}}\n" +
        "PNG Version: {{File Link PNG}}\n\n" +
        "Regards,\n\n" +
        "HANNA CASSANDRA CABALSA\n" +
        "Vice President for Internal Affairs\n" +
        "Philippine Institute of Civil Engineers - National University Fairview Student Chapter";

    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 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);

        // Send the email
        MailApp.sendEmail(emailAddress, subject, message);
    });
}