BOT's #7: Sending multiple HTML emails with Google Apps Script

#productivity #mailing #mailmerge

There are many reasons why you may need to send multiple emails, such as:

• Sending a marketing campaign to a list of subscribers.
• Notifying multiple recipients about an important announcement or event.
• Providing individualized information to a group of people, such as students, staff or clients.
• Sending invitations or reminders for an upcoming event or meeting.
• Sharing important information or updates with a team or organization.

When sending multiple emails, using a script can help you save time and ensure that all emails are sent accurately and consistently. It can also allow you to personalize each email, such as using recipient names or including specific details relevant to each person.

To send an email in bulk using Google App Script, you can follow these steps:

1. First, create a google sheet with column headers of details you intend to use. In my case, I created this Google Sheet.

2. After creating the Google Sheet, you can create a Google Doc that you will use to design your email body template. Remember, its details must be aligned with the email body details you created in the Google Sheet in step 1. Here is our template that aligns with step 1.

3. Open the Script Editor in Google Sheet (created in step 1)? by selecting "Tools" > "Script editor".

4. In the Script Editor, type the following code:

javascript

function sendHtlmEmails() { 
 var guestName = 0;
 var hotelName = 1;
 var checkInDate = 2;
 var checkOutDate = 3;
 var roomType = 4;
 var numberOfGuests = 5;
 var specialRequests = 6;
 var hotelTeamName = 7;
 var subject = 8;
 var map = 9;
 var imageLink = 10;
 var emailRecipient = 11;
 var carbonCopyEmail = 12;
 var emailSent = 13;
 var timeStamp = 14;

 var htmlTemplate = HtmlService.createTemplateFromFile("htmlEmailTemplate");

 var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("bulkSendHtml");

 var data = ws.getRange(1, 1, ws.getLastRow() - 1, 14).getDisplayValues();

 var hasdata = 0;

 data = data.filter(function (r) { return r[2] !== '' });

? //start sending details of each spreadsheet row
 data.forEach(function(row, index){
 ? //check if row is the headers, if so we skip it
 ? if (index === 0) return;
 ? //check if the Email has already been sent by looking at the timestamp
 ? if (row[13]) return;
? 
 ? //In these lines, we replace our replacement tokens with values from our spreadsheet row



 ? htmlTemplate.guestName = row[guestName];
 ? htmlTemplate.hotelName = row[hotelName];
 ? htmlTemplate.checkInDate = row[checkInDate];
 ? htmlTemplate.checkOutDate = row[checkOutDate];
 ? htmlTemplate.roomType = row[roomType];
 ? htmlTemplate.numberOfGuests = row[numberOfGuests];
 ? htmlTemplate.specialRequests = row[specialRequests];
 ? htmlTemplate.hotelTeamName = row[hotelTeamName];
 ? htmlTemplate.subject = row[subject];
 ? htmlTemplate.map = row[map];
 ? htmlTemplate.imageLink = row[imageLink];
 ? htmlTemplate.emailRecipient = row[emailRecipient];
 ? htmlTemplate.carbonCopyEmail = row[carbonCopyEmail];
 ? htmlTemplate.emailSent = row[emailSent];
 ? htmlTemplate.timeStamp = row[timeStamp];


 ? var htmlMessage = htmlTemplate.evaluate().getContent();

 ? GmailApp.sendEmail(row[emailRecipient],
 ? ? row[subject],
 ? ? "Please open this email with a client that supports html",
 ? ? { name: "Stay Reservations", htmlBody: htmlMessage,cc: row[carbonCopyEmail] } //You can always customise the senders name to suite a preferred name
 ? );


 ? ws.getRange(1 + index, 14).setValue(new Date()).setNumberFormat("yyyy-MM-dd hh:mm:ss");
 ? ? ? //here we make sure the cell is updated right away in case the script is interrupted

 ? ? ? ? SpreadsheetApp.flush();

? 
 })        

If the above codeblock is confusing for you, kindly revisit by previous BOT’s #5 for an understanding.

5. Save the script as 'sendHtlmEmails'.

6. Now copy and paste the information on the email body template (step 2 GoogleDoc) and paste on the visual editor on the word to html site. You can make adjustments on the visual editor for a preferred HTML Editor output.

7. In the HTML Editor, type the following code (this code should be copied and pasted from step 6):

HTML

<div dir="ltr" style="margin-left:0pt;" align="left">
 ? <table style="border:none;border-collapse:collapse;table-layout:fixed;width:468pt;">
 ? ? ? <tbody>
 ? ? ? ? ? <tr style="height:0pt;">
 ? ? ? ? ? ? ? <td style="border-left:solid #9900ff 2pt;border-right:solid #9900ff 2pt;border-bottom:solid #9900ff 2pt;border-top:solid #9900ff 2pt;vertical-align:top;padding:2pt 2pt 2pt 2pt;overflow:hidden;overflow-wrap:break-word;">
 ? ? ? ? ? ? ? ? ? <p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt;"><span style="font-size:11pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre;white-space:pre-wrap;"><span style="border:none;display:inline-block;overflow:hidden;width:610px;height:407px;"><img src="<?= imageLink ?>" style="width: 100%; height: 432px;"></span></span><span style="font-size:11pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre;white-space:pre-wrap;"><br></span><span style="font-size:8pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre;white-space:pre-wrap;">Dear <?= guestName ?>,</span></p><br>
 ? ? ? ? ? ? ? ? ? <p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt;"><span style="font-size:8pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre;white-space:pre-wrap;">We are very excited to confirm your upcoming stay at <b><?= hotelName ?></b>. We are thrilled to have you as our guest and we are dedicated to making your stay as comfortable and enjoyable as possible.&nbsp;</span></p><br>
 ? ? ? ? ? ? ? ? ? <p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt;"><span style="font-size:8pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre;white-space:pre-wrap;">The details of your booking are as follows:</span></p>
 ? ? ? ? ? ? ? ? ? <p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt;"><span style="font-size:8pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre;white-space:pre-wrap;"><b>- Check-in date:</b> <?= checkInDate ?></span></p>
 ? ? ? ? ? ? ? ? ? <p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt;"><span style="font-size:8pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre;white-space:pre-wrap;"><b>- Check-out date: </b><?= checkOutDate ?></span></p>
 ? ? ? ? ? ? ? ? ? <p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt;"><span style="font-size:8pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre;white-space:pre-wrap;"><b>- Room Type: </b><?= roomType ?></span></p>
 ? ? ? ? ? ? ? ? ? <p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt;"><span style="font-size:8pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre;white-space:pre-wrap;"><b>- Number of Guests: </b><?= numberOfGuests ?></span></p>
 ? ? ? ? ? ? ? ? ? <p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt;"><span style="font-size:8pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre;white-space:pre-wrap;"><b>- Special Requests: </b><?= specialRequests ?></span></p><br>
 ? ? ? ? ? ? ? ? ? <p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt;"><span style="font-size:8pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre;white-space:pre-wrap;">We want to ensure that you have a seamless experience with us. If you have any further questions or concerns, please do not hesitate to reach out to us.&nbsp;</span></p><br>
 ? ? ? ? ? ? ? ? ? <p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt;"><span style="font-size:8pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre;white-space:pre-wrap;">Thank you again for choosing to stay at <?= hotelName ?>. We look forward to welcoming you soon!</span></p><br>
 ? ? ? ? ? ? ? ? ? <p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt;"><span style="font-size:8pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre;white-space:pre-wrap;">See&nbsp;</span><a href="<?= map ?>" style="text-decoration:none;"><span style="font-size:8pt;font-family:Arial;color:#1155cc;background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:underline;-webkit-text-decoration-skip:none;text-decoration-skip-ink:none;vertical-align:baseline;white-space:pre;white-space:pre-wrap;">map</span></a><span style="font-size:8pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre;white-space:pre-wrap;">&nbsp;for ease of navigation.</span></p><br>
 ? ? ? ? ? ? ? ? ? <p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt;"><span style="font-size:8pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre;white-space:pre-wrap;"><b>Warm regards,</b></span></p>
 ? ? ? ? ? ? ? ? ? <p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt;"><span style="font-size:8pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre;white-space:pre-wrap;"><?= hotelTeamName ?></span></p>
 ? ? ? ? ? ? ? </td>
 ? ? ? ? ? </tr>
 ? ? ? </tbody>
 ? </table>
</div>        

You’ll notice, the replaced placeholders with aligned placedholders on the script editor.

8. Save the code as 'htmlEmailTemplate'.

9. Now that you are done with this step, you can go back and run the 'sendHtlmEmails' function by selecting "Run" > "sendHtlmEmails".

The script will send a new email for each row in your 'data' array, replacing the placeholder text in the template with the corresponding content within the Google Sheet.

I hope this helps you in saving time and increasing efficiency the next time you are sending multiple Google emails with related messages.

About the author

Rogers is a data and emerging tech enthusiast. He is the Co-founder and VP of Operations at OurPass.

OurPass is a global neobank providing businesses of all sizes with access to every banking, payment, and business management tool they need to start, grow and scale their businesses.

Our mission is to create a borderless world of successful businesses and we are constantly building new tools to ensure that whatever stage a business is in, they have access to every tool they need to thrive, all in one place.

Founded in 2021 as a one-click checkout company, we made a pivot in July 2022 when we saw that beyond offering a niche service, we could provide end-to-end solutions that helped entrepreneurs grow every aspect of their businesses.

In just a few months since our transition, we have become a leading business bank serving thousands of customers including some of the biggest retail outlets in Nigeria such as Spar, Shoprite, and Eat N Go (parent company of Dominos, Cold Stone Creamery, and Pinkberry), processing about a million transactions monthly.

OurPass also holds a Microfinance Banking License from the Central Bank of Nigeria.

To unlock limitless smart business banking that OurPass app offers, download the app from the App Store or Google Play Store, create an account, and join thousands of businesses growing with us.

Connect with us, join our vibrant community on Facebook, Twitter, Instagram, LinkedIn, and YouTube, and stay up to date with the latest information about our services.