Build your own Email marketing platform


Email marketing is one of the best ways to engage with your customers. There are many email marketing providers in the market like Mailchimp, constant blue, Aweber and they are really good at it. If you are interested in setting up an email subscription using them, please refer to my article Email subscription for WordPress site. But these services become chargeable at one point in time and extra features with paid plans may be an overkill for the small needs of a business. If you are a startup, low on budget, or a big company with limited requirements on email marketing then the best thing would be to build your own email marketing platform, and here I will show you how to make one, send bulk professional emails and that also for free and with scale.

Tools and approach

We will use Google apps script combined with spreadsheets to build our email marketing platform. Spreadsheets stores the contact list and google apps script is published as a web app. A custom client-side HTML form is embedded in the page which redirects the user data to the web app. A new menu option is provided in the spreadsheet using which promotional emails can be sent in bulk to subscribers.

Prepare the spreadsheet

You need a google account to start with, so log in to it first. I will describe below how to prepare the spreadsheet for collecting subscriber’s data. If you want the ready-made sheet, copy it directly from here.

Create a Spreadsheet “Subscribers List” and name the sheet to “SUBSCRIBERS”. Add the header row with fields:-

KeyEmail AddressNameSubscribedDatePhone NumberPhysical Address

If you are wondering what Key field is for, then to answer it, it’s a unique UUID for the user. Will describe it’s use ahead. As Email addresses are unique so in order to avoid duplicates we will need to add data validation to it. Select the “Email Address” column and go to the “Data->Data Validation”.

Data Validation

In the popped up window look for the “Criteria” field and select “Custom formula is” from the menu. Put the custom formula =NOT(COUNTIFS($B$2:$B2,$B2)>1 in the adjacent box. Also, select “Reject Input” and save it. Now if we try to add any duplicates in the “Email address” column, it will be rejected. Also, our spreadsheet is ready now.

Back-end code for Web app

To build our own email marketing platform, we are going to build a google apps script-based web app. This is a bot safe implementation. In the spreadsheet that we created above, go to “Tools->Script editor” and in the newly launched window, copy-paste below code in if you copied the spreadsheet, this code will already be present here):-

const SHEET_NAME = 'SUBSCRIBERS'; /* * onOpen adds a new Menu item "Mail" and sub-item "Send Mail" * to the bounded spreadsheet. This is used to send bulk mails */ function onOpen(e) { SpreadsheetApp.getUi() .createMenu('Mail') .addItem('Send Mail', 'sendMail') .addToUi(); } /* * sendMail presents an interactive dialogue box to the user. * User can enter the subject and body of the mail. The mail body * can be plain text as well as an HTML template. */ function sendMail() { var html = HtmlService.createHtmlOutputFromFile('mailbody') .setTitle('Promotional Mail') .setWidth(600) .setHeight(300); SpreadsheetApp.getUi() .showModalDialog(html, 'Promotional Mail'); } /* * getSheetData finds out all the users from the spreadsheet * who are subscribed and sends mail with provided * subject and body */ function getSheetData(mailsubject, mailBody) { const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME); var users = sheet.getRange(2, 1, sheet.getLastRow() - 1, 4); const KEYINDEX = 1; const EMAILINDEX = 2; const NAMEINDEX = 3; const SUBSCRIBEDINDEX = 4; const numRows = users.getNumRows(); for (var row = 1; row <= numRows; row++) { if (users.getCell(row, SUBSCRIBEDINDEX).getValue() === 'NO') { continue; } var email = users.getCell(row, EMAILINDEX).getValue(); var name = users.getCell(row, NAMEINDEX).getValue(); var recepientName = name; if (recepientName === "") { recepientName = email.split("@")[0]; } var htmlTemplate = HtmlService.createTemplate(mailBody); htmlTemplate.customer_name_email = recepientName; htmlTemplate.UnsubscribeLink = ScriptApp.getService().getUrl() + '?id=' + users.getCell(row, KEYINDEX).getValue(); var message = htmlTemplate.evaluate().getContent(); GmailApp.sendEmail(email, mailsubject, message, { htmlBody: message }); } return 'Mail sent successfully'; } /* * doGet is triggered when a get call is done to the web app URL. * It's used here to unsubscribe a user. User gets the unsubscription link * in the promotional link. */ const doGet = (event = {}) => { const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME); const { parameter } = event; const { id = 'Anonymous' } = parameter; var key = id; var textFinder = sheet.createTextFinder(key); var search_row = textFinder.findNext().getRow(); sheet.getRange(search_row, 4).setValue('NO'); return ContentService.createTextOutput('You are successfully unsubscribed. Subscribe again later to get our newsletter.'); }; /* * doPost is triggered when an HTTP POST request is made * to the web app URL. Here it's used to collect user's * subscription details and store it in the spreadsheet. */ const doPost = (e) => { const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME); var result; const [header] = sheet.getRange('A1:1').getValues(); const KEY = header.indexOf('Key') + 1; const DATE = header.indexOf('Date') + 1; const EMAIL = header.indexOf('Email Address') + 1; const NAME = header.indexOf('Name') + 1; const PHONE = header.indexOf('Phone Number') + 1; const ADDRESS = header.indexOf('Physical Address') + 1; const SUBSCRIBED = header.indexOf('Subscribed') + 1; var date = new Date(); var email = var name = var phone =; var address = e.parameter.address; var nextFreeRow = sheet.getLastRow() + 1; if (!(e.parameter.b_cd69ee5dc1310b069012ad6a9_f4a591430d === "")) { return ContentService.createTextOutput('Well tried bot, but I caught you').setMimeType( ContentService.MimeType.TEXT); } if (ValidateEmail(email) === false) { return ContentService.createTextOutput('Please provide a valid email address.').setMimeType( ContentService.MimeType.TEXT); } try { sheet.getRange(nextFreeRow, EMAIL).setValue(email); sheet.getRange(nextFreeRow, KEY).setValue(Utilities.getUuid()); sheet.getRange(nextFreeRow, DATE).setValue(date); sheet.getRange(nextFreeRow, NAME).setValue(name); sheet.getRange(nextFreeRow, PHONE).setValue(phone); sheet.getRange(nextFreeRow, ADDRESS).setValue(address); sheet.getRange(nextFreeRow, SUBSCRIBED).setValue('YES'); result = ContentService.createTextOutput('You have been successfully subscibed.').setMimeType( ContentService.MimeType.TEXT ) } catch (e) { result = ContentService.createTextOutput('You are already subscribed!!').setMimeType( ContentService.MimeType.TEXT); } finally { return result; } }; /* * ValidateEmail is used to verify a valid email address */ function ValidateEmail(email) { var mailformat = /^\w+([\.-]?\w+)*@\w+([\.-]?\w+)*(\.\w{2,3})+$/; if (email.match(mailformat)) { return true; } else { return false; } }
Code language: JavaScript (javascript)

Read the comments for each of the functions. It will help to understand what’s the role of each function. If you understand it, it will help you to tweak it for your specific needs. Now in the “Sendmail” function, we are using HTML to display user dialogue. To create this HTML, go to “File->New->HTML file” and name it to “mailbody.html”. Paste the below HTML code in it and save it:-

<!DOCTYPE html> <html> <head> <base target="_top"> </head> <body> <h4>Enter the text to send as mail body</h4> <input type="text" value="" name="subject" id="subject" size="40" placeholder="Email Subject..." required> <br><br> <textarea id="digitalbetel" name="digitalbetel" rows="10" cols="80" placeholder="Enter mail body" required></textarea> <div class="clear" style="text-align:center"><input type="submit" value="Send Mail" name="Send Mail" id="sendMail" class="button"></div> <div id="output" style="text-align:center"></div> <script> var t = document.getElementById('sendMail'); t.addEventListener('click',gsData); function gsData(){ var mailBody = document.getElementById('digitalbetel').value; var mailsubject = document.getElementById('subject').value;, mailBody); } function onSuccess(result){ output.innerHTML = result; } </script> </body> </html>
Code language: HTML, XML (xml)

Now click “Publish->Deploy as web app”. In the popped up window choose a new version and other settings as specified in the below image. Please make sure “Who has access to the app” is selected as “Anyone, even anonymous”.

Deploy Web App

Click on “Deploy” and copy the URL presented in field “Current web app URL” from the new window for future reference:-

Web App URL

Create Client side subscription form

At this stage, our web app is ready to serve client requests. Let’s create a client-side email subscription form. Open your favorite editor and create a new file and copy-paste below HTML to it. Change the <form> tag “action field” value (Line no.6) to point to your web app URL that we copied earlier post-deployment and save it as Form.html:-

<!-- Begin Signup Form --> <style type="text/css"> #embed_signup_form{background:#46a5d3; clear:left; font:14px Helvetica,Arial,sans-serif; width:300px;} </style> <div id="embed_signup_form"> <form action="" method="post" id="personalized_subscription_form" name="personalized_subscription_form" class="validate" target="_blank" novalidate> <div id="Subscribe"> <label for="EMAIL"></label> <input type="email" value="" name="email" id="EMAIL" placeholder="email address" required> <input type="text" value="" name="name" id="NAME" placeholder="Name"> <input type="text" value="" name="phone" id="PHONE" placeholder="Phone Number"> <input type="text" value="" name="address" id="ADDRESS" placeholder="Address"> <!-- To safeguard from bot sign up. Real people won't fill this --> <div style="position: absolute; left: -5000px;" aria-hidden="false"><input type="text" name="b_cd69ee5dc1310b069012ad6a9_f4a591430d" tabindex="-1" value=""></div> <div class="clear"><input type="submit" value="Subscribe" name="subscribe" id="embedded-subscribe" class="button"></div> </div> </form> </div> <!--End Signup Form-->
Code language: HTML, XML (xml)

If you are aware of HTML, you can easily change the look and feel of it. Now, embed this email subscription form’s HTML code to your website’s HTML page. If you are using WordPress, you can do it easily by the “Custom HTML” widget. For example, I want to display it in the sidebar, so I drag and drop “custom HTML” widget to Sidebar, enter “Title” as “Email Subscription” and in the “Content” field paste the contents of form.html saved earlier:-

Client side form

Execute and verify

So, now we are done with our development work and it’s time to see our email marketing platform in action. Open, your website’s page as a visitor where you embedded the subscription form, and enter the user’s/visitor’s details and click “Subscribe”:-

Subscription form

If you are subscribed successfully, you will see the message “You have been successfully subscribed.” in a new window. To verify, go to the spreadsheet “subscriber’s List” and you will see the user is present there:-

Subscription verification

Send Mail to all users

In the spreadsheet go to “Mail->Send mail”. As mentioned earlier the body can be an HTML template also which enables you to send stylish and professional emails. For example, refer to the HTML template below:-

<!DOCTYPE html> <html> <head> <base target="_top"> </head> <body style="background-color:grey;"> <h2>Checkout our new posts</h2> <p> Hello <?= customer_name_email ?> </p> <p><strong>There are new posts awaiting for you.</strong></p> <p>Hope you will enjoy these great reads</p> <a href="">Technology</a> <br> <a href="">Blogging</a> <p>Best Regards,</p> <p>Abhishek,<br></p> <p>To unsubscribe, please click </p><a href="<?= UnsubscribeLink ?>">here</a> </body> </html>
Code language: HTML, XML (xml)

In-Line no. 9 we have a placeholder for user/customer’s name or email id. The “getSheetData” method of our backend code replaces this with either user’s name or email ID. In Line 18, there is a placeholder for the Unsubscription link which is unique for each user. Unsubscription link contains the user’s “Key” or UUID from the spreadsheet. The “doGet” function looks for this UUID and marks that user as unsubscribed. So now enter the subject and HTML template in the dialogue box as below:-

Send bulk mails

click on “send Mail” button and all the subscribed users will receive a personalized email. Lets look at how this mail looks:-

Mail received

Professional enough right. Feel free to modify the HTML template to make it look as fancy as you want.

Validate unsubscribe

In the personalized mail received by the user, we have the text in the end “To unsubscribe, please click here”. Let’s click the link as the user. On successful unsubscription you can see the text message:- “You are successfully unsubscribed. Subscribe again later to get our newsletter.” Go to the spreadsheet and verify the “Subscribed” column for this user :-

Unsubscription verification

The entry is “NO” corresponding to this user in the “Subscribed” column. This user will not receive any promotional mail further.

So, this is how we build our own email marketing platform. with the power of coding in your hand, you can customize it as per your need without spending a single penny. As far as scale is concerned, in a single Google spreadsheet you can have 5 million cells. With this limit and considering our seven fields, you can have ~700000 subscribers. Good enough for a startup as well as a biggie. Hope you enjoyed the article. You can thank me by commenting and sharing the post 🙂

Click here to view posts you may like.

Leave a Reply

Your email address will not be published. Required fields are marked *