Build a Mail Merge with Airtable, Gmail, and Standard Library.

This was written by Standard Library, follow us on Twitter for more updates, @StdLibHQ!

A Mail Merge, for the uninitiated, is a way to send multiple copies of a personalized e-mail to different people at the same time and then, optionally, send timed follow-ups. They can be used to send newsletters, for marketing purposes, or to do things like make sure you send scheduled follow-ups with folks you need an answer from regarding important issues. For example, say you’re beta testing a new product and want to let your engaged beta users know you’ve shipped the most recent iteration of your product -- just add them to a Mail Merge! Mail Merges are powerful time-saving tools for people who spend way too much time in their inbox.

Typically, Mail Merge functionality is sold or packaged with general e-mail management tools. Companies like YesWare charge $25 per user per month for this sort of functionality, where other products like Streak offer it for free. Today, we’re going to show you how you can build your own fully customizable DIY Mail Merge solution with Airtable, Gmail and Standard Library in under 10 minutes. It’s the perfect solution for makers and software engineers looking to have a little more control over their e-mail lists.

What you’ll need

1x Airtable account, available at https://airtable.com
1x “Mail Merge” Airtable Base, available at https://airtable.com/shrRX39RJ8i6r0zvb
You can add the base automatically to your workspace with this link: https://airtable.com/addBaseFromShare/shrRX39RJ8i6r0zvb‍
1x Gmail account
1x Standard Library account, available at https://stdlib.com‍
480x seconds in your busy day 😄

Minute 1: Clone the Airtable Base to your Workspace

The first thing we want is to make sure we have a copy of the Mail Merge base in our Airtable workspace. It’s available at https://airtable.com/addBaseFromShare/shrRX39RJ8i6r0zvb. Clicking on the link will prompt you with a screen:

Simply click Add base once you’ve selected your workspace. You’ll see something like this:

Voila! The Mail Merge base has been added. It’ll be indicated with a NEW! marker on the top right. You can click it to proceed, you should see an Airtable base with two tables: People and Stages. The People table with have five fields: E-mail, First Name, Last Name, Last Stage, and Last Stage Sent.

NOTE: “Last Stage Sent” must be populated with a date far in the past when “Last Stage” is 0 for the first e-mail to be sent. You can click on Stages to see the Stages table, which should have three fields: Stage, Subject and Body.m

You can edit these later to contain whatever you’d like. For now, we’ll stick with the default contents. Note that variables (fields from the People table) are demarcated with {{Field Name}} (in these examples, {{First Name}} and {{Last Name}}).

Minute 2: Prepare Your Workflow on build.stdlib.com

Now we visit https://build.stdlib.com to open up Build on Standard Library, our workflow generation and prototyping tool that miraculously generates code for you behind the scenes. We’ll want to select the following event details:

Scheduler → Once a day, 09:00, UTC-8:00 (Los Angeles, San Francisco)

And the following workflow actions:

Airtable → Select Rows by querying a Base

Gmail → Compose and send a Message

When you’re ready to proceed, hit Create Workflow to continue!

Minute 3: Link your Airtable Base, “Mail Merge”

The next step is to link your Airtable Base (“Mail Merge”) and your Gmail account. After hitting Create Workflow, you’ll be presented with a screen that looks like this:

First, we’ll click Link Resource next to Airtable. You’ll be prompted with a popup that shows previously linked Airtable resources (bases), with the option to Link New Resource. We’ll want to click that.

Clicking Link New Resource will bring us to this screen…

Follow the instructions on this screen to open up https://airtable.com/account, where you’ll see the option to Generate API Key. Click it to get your API key assigned.

Once you click Generate API Key, you’ll see this:

Simply click the dots to see your API Key. Copy and paste it back into Build on Standard Library.

Click Finish to proceed, once you’re sure your API key is correct. You’ll see a progress indicator showing the linking progress.

Once complete, you’ll see a list of all your available bases. You should see a Mail Merge base if you added the base to your account successfully in the first step.

Note: If you don’t see the Mail Merge base, you didn’t add it successfully in Minute 1. Add it by clicking this link: https://airtable.com/addBaseFromShare/shrRX39RJ8i6r0zvb. You’ll have to back out of the flow and go through it again to see it listed.

Select Mail Merge as your base and click Finish.

You’ll be prompted with another progress bar as your account links.

Once complete, you’ll be brought back to the Identity screen:

Great! We’ve linked our Airtable base.

Minute 4: Link your Gmail Account

Linking your Gmail account is even easier! Just click the Link Resource button next to Gmail. You’ll be prompted with the resource management screen again, you’ll want to click Link New Resource.

After clicking Link New Resource, the Google OAuth popup window will appear. Select the Gmail account you want to link (and send e-mails from!).

You’ll see a popup asking to be granted access to read and compose e-mails, click Allow.

Again, a progress bar will appear:

And once it’s complete, you’ll see that Gmail has been linked and, thus, all of your required resources have been added!

You can now hit Next to proceed.

NOTE: If at any point you accidentally drop out of this flow, previously linked resources can be linked again trivially without going through this whole process. They’ll be immediately available and visible once you click Link Resource from the Identity management screen.

Minute 5: Prototype and Test your Workflow

Next, we’re brought to the workflow configuration screen. This is a super useful, form-based user interface for basic linear workflows.

To make sure everything is hooked up properly, hit the green PAUSE button next to the Gmail workflow action to pause it…

And then fill out the Airtable step with the following:

table: People

Scroll down until you see the green Run with Test Event button and press it…

You should get a result that looks something like this, it means Airtable is connected and working properly!

Great! Now we can get to the fun stuff.

Minute 6: Modify your Workflow with Code

Woohoo. Now we’re rolling. Running a Mail Merge requires a little bit of complex logic, and this is where Standard Library shines as compared to other workflow management tools.

We’ll want to add code that does the following:

Retrieves all Mail Merge stages from the Stages table
Retrieve the maximum value of Stage from the Stages table
Retrieves all people that still have pending stages from the People table‍
People: Last Stage is less than the maximum Stages: Stage‍
Sends the appropriate stage Subject and Body (Stages) to each E-mail (People)
Updates the People table to indicate the appropriate Stage has been sent

Scroll back up to the top of your Workflow Configuration window and find the Developer Mode switch. It’s at the bottom of the workflow actions editor on the right.

Click it to ON. It will disable the workflow actions editor, and voila! You’ll see all the code that’s running your workflow behind the scenes.

In the non-disabled part of the workflow code (everything in white), you’ll want to copy and paste this code to replace the generated code...

‍

```

// Sends every three days when run once a day

const STAGE_DELAY_DAYS = 2.5;

// Prepare workflow object to store API responses

let workflow = {};

// [Workflow Step 1]

console.log(`Running airtable.query[@0.1.3].select()...`);

workflow.stages = await lib.airtable.query['@0.1.11'].select({

table: `Stages`,

where: {}

});

// [Workflow Step 2]

console.log(`Running airtable.query[@0.1.3].max()...`);

workflow.maxStage = await lib.airtable.query['@0.1.11'].max({

table: `Stages`,

field: `Stage`

});

// [Workflow Step 3]

console.log(`Running airtable.query[@0.1.11].select()...`);

workflow.people = await lib.airtable.query['@0.1.11'].select({

table: `People`,

where: {

'Last Stage__lt': workflow.maxStage.max.value, // From previous API call

'Last Stage Sent__recency_gte': STAGE_DELAY_DAYS * 24 * 60 * 60

}

});

// [Workflow Step 4]

workflow.messages = [];

for (let i = 0; i < workflow.people.rows.length; i++) {

// For every person, find appropriate e-mail stage

let person = workflow.people.rows[i];

let stage = workflow.stages.rows

.find(stage => stage.fields['Stage'] === person.fields['Last Stage'] + 1);

if (!stage) {

continue; // If invalid stage, skip

}

// Fill out “{{First Name}}” etc. variables in the message subject and body…

let subject = stage.fields['Subject']

.replace(/\{\{(.*?)\}\}/gi, ($0, $1) => person.fields[$1.trim()])

let body = stage.fields['Body']

.replace(/\{\{(.*?)\}\}/gi, ($0, $1) => person.fields[$1.trim()])

console.log(`Running gmail.messages[@0.1.1].create()...`);

workflow.messages.push(

await lib.gmail.messages['@0.1.1'].create({

to: person.fields['E-mail'], // required

subject: subject,

text: body

})

);

// Update the field data for People

person.fields['Last Stage'] = person.fields['Last Stage'] + 1;

person.fields['Last Stage Sent'] = new Date().toISOString();

}

console.log(`Running airtable.query[@0.1.11].replace()...`);

await lib.airtable.query['@0.1.11'].replace({

table: `People`,

replaceRows: workflow.people.rows

});

```

Minute 7: Test your Mail Merge Workflow and Ship it!

Now that we’ve entered our code, scroll to the bottom of the Workflow editor again and hit Run with Test Event. You should see something like this:

Congratulations! Your Mail Merge sent. To verify, we can check our Airtable Base, Mail Merge and the People table should look something like this:

You’ll notice that Last Stage is now set to 1 and Last Stage Sent has now been populated.

Note: To reset your Mail Merge, simply change Last Stage to 0 and Last Stage Sent to a date earlier than three days ago: like 1/1/1970. Last Stage Sent should be in UTC time.

If we check our sent e-mail in Gmail, we’ll see something like…

Great! Back on our Build on Standard Library flow, you can now hit the Next button. If it has been greyed out, you need to re-run the Test Event to proceed. (No e-mails should be sent if you recently ran it.)

After hitting Next, you’ll be brought to this screen:

Your project will have a name auto-generated based on the event type (in this case, scheduler) and a filename that your project will have a name and filename auto-generated based on the event type (in this case, scheduler).

Simply hit Alright, Ship it! and you’ll see this…

That’s it! You’re all done. Your Mail Merge is now live, and will follow up every three days with the next stage of the Merge. Click View Project to proceed.

Minute 8: Manage Your Project

Once you’ve clicked View Project you’ll be brought to your Project Management screen. It’ll look something like this:

From here, you can Manage Your Environment, Fork Environment, Ship Release and a whole bunch more! To read more about Project Management, please visit the docs page, available at:

https://docs.stdlib.com/workflow-apis/managing-environments/development-environments/

That’s it!

Your custom Mail Merge system is complete. You can always duplicate your Mail Merge base and change the base this integration is connected to at any time by Managing Identity (Auth) via your project manager:

https://docs.stdlib.com/workflow-apis/managing-identity-authentication/

If you have any questions, feel free to e-mail us any time: support@stdlib.com. You can also join our Community Slack, by visiting https://docs.stdlib.com and requesting an invitation + joining from the right hand navigation menu.

Please follow us on Twitter for more updates, @StdLibHQ!