Create a Free Newsletter Sender using Google Sheets

adrian bega

In this tutorial I will teach you how to create your own free newsletter sender tool using Google Sheets.

Prepare the list of subscribers and newsletter content

Open Google Sheets, create a new Sheet, give it the name Newsletter Sender.

We will create two sheets.

The first one will contain the list of subscribers email addresses. Name the first sheet Subscribers.

Populate this sheet as shown below, make sure to replace the email address with your own to use for testing.

Name the second sheet Content and populate it at shown below.

Time to write the script that does all the work.

Email Sender Function

Inside Google Sheets, open Script Editor from Tools Menu.

Give a name to your script project then save it.

Add a new function sendNewsletter and paste the following content.

// Populate list of users from the Subscribers sheet
var emailRange = 
   SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Subscribers").getRange("B2");
var emailAddress = 
   emailRange.getValues();
// Prepare Email Content
var subject = 
   SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Content").getRange("B1").getValue();
var content = 
   SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Content").getRange("B2").getValue();
//Send Email Function
MailApp.sendEmail(emailAddress, subject, content);

What this function does is sending the email, using your google email address, to the list of senders gathered from the Subscribers sheet, and the subject and content gathered from the Content sheet.

Create Newsletter Menu

Create a new function onOpen and add the following script to it.

  // Call the SpreadsheetApp and gets the UI
  var ui = SpreadsheetApp.getUi();
  
  //Add Newletter Main Menu
  //Add Send item under it that triggers the sendNewsletter function
  
  ui.createMenu('Newsletter')
  .addItem('Send', 'sendNewsletter')
  .addToUi();

What this function does is add the Newsletter item to the main menu of your google sheet page, and under it, the Send item that will trigger the sendNewsletter function.

Script Permissions

When the script is ran, it must be authorised to acces you Google Sheet data and send email from your account.

Click run, and the script will request the required permissions.

Click on review permissions then select your google account in the following screen.

Give the required permissions to the script by clicking Allow.

Testing the functions

Now it’s a good time to test the functions.

From the script editor, select sendNewsletter function from the menu the click run.

Check your inbox to see if you received the newletter.

Select the onOpen function, then execute it.

Go back to the Google Sheest, and look at the main menu to make sure the Newsletter item is there. You can click on it then on Send to test sending the newsletter.

Executing the script automatically

It’s time to make sure the script is executed and the Newsletter menu is created every time you load your Google Sheet.

Go back yo the Script Editor, click on Edit menu button, the select Current Project Triggers.

Inside the Triggers editor, click Add Trigger.

Inside the Trigger popup window, you should configure the onOpen function to be launched every time the sheet page opens.

Click Save to save yor new trigger.

Conclusion

So this is how you can write and configure your own free newsletter tool.

Of course in terms of complexity, this is just a rudimentary example.

This project can be improved in many ways. You can for example program sending emails on a regular basis. Emails could contain dynamic rich email content coming from external google presentations documents for exemple. There are many possibilities, it’s up to you to improve it to suit your needs.

Thanks for reading, I hope you enjoyed this article!

Adrian

Related posts