Automatically combining records into one email

[Power_Bridge]

I am not sure if this is possible in Airtable or if it would required Zapier or other integrations (which I am open to!), but here it goes.

We are trying to find a way to automatically combine and send records on a monthly basis. These people submit responses for each project they are working on, so we want to combine all of those project responses together and send them one email that has the status of all of them.

In the above screenshot, we would want to send records 1-5 to Alan, 6-7 to Alison, etc. Right now we do this by hand by selecting each record, right clicking on the checkbox and then selecting the Email option. We would like to find a way to automate this process. We do not want people to get individual emails, as sometimes there are a lot of individual items, so it is important that the records get combined and then sent.

I looked into the SendGrid integration, but that would send individual emails. I feel like there would be a way to do this with Airtable automations or Zapier, but I am not sure how. Can someone help me batch these emails and have them get sent out once per month?

Thanks!

[TheTimeSavingCo · Accepted solution]

Hi Power, yeap, doable but, in my opinion, sorta tricky without scripts

Some considerations before we get into it:

1. What plan are you on, and are the people you’re emailing collaborators in the workspace / base?

I ask this because, on the Free plan, automations can only email collaborators. On the Pro plan, automation emails can be sent to 100 unique emails a day

2. Are you comfortable with scripting, and are you on a Pro account?

I ask this because, if you’re comfortable with scripting and on a Pro account, the workflow will be somewhat simple and straightforward as we’ll utilize an automation script. If you’re not comfortable with scripting or are not on a Pro account, I have a solution for you which I tested and works, but is a bit of a pain to set up

---

Non scripting approach details:

The idea is to create an automation that runs once a month, that will generate an individual record for each person that you need to send an email to. We would then have another automation that triggers off each of the records created in the previous automation.

I’ve linked a version that should work here. You can duplicate the base by clicking the title of the base at the top of the screen, followed by the three horizontal dots on the right, and then the “Duplicate Base” button. You’ll then be able to see all the formulas, how the fields are set up, automations, etc

Quick summary of how it works:

1. Every month, an automation grabs all the records in People that have at least one linked Submission record from last month
2. It then creates individual records in Email Tasks for each People record that it found in the previous step by creating a record in Monthly Email Task Compiled by inserting a comma separated value list of unique into a linked field
3. When a record gets created in Email Tasks, an automation gets triggered that will look for all the submissions linked to that People record from last month, and will also grab the email from the People record.
4. It then sends the email to the found email address, with the found records in a List / Grid format of your choice

If you were doing this via a script you’d let the script handle all the record creation and linking, and just use automations to send the email itself

[kuovonne]

Trying to send one email per person to many people, but the emails need to be able to reference mult... Automations

Welcome to the Airtable community! Trigger the email from your [People] table. You may want to use a rollup field and a formula field to calculate if/when to trigger the email. Then use a “find records” action before your “send email” action to find the records associated with that person. Include the results of the “find records” action in your email message. Or you can use rollup fields to summarize the information from all the linked records and then include the results of the rollup fields…

[Power_Bridge]

Thanks, I will play around with this! The people are not collaborators but we do have a pro plan, so that should not be a problem. I do have all of the reports linked to the individuals in a version of the “People” tab, but I couldn’t figure out how to reference the information included in those linked records when I set up the email automation, but I’ll try your approach.

I have done a little bit of coding, so I was tempted to start dabbling in scripts, as this seems like something that a simple loop function could do quickly (go through this list of people one at a time, find all records that contain the name in this other list and batch the output). I did not look too much into it, but I was having a hard time comprehending how to do it in the Airtable environment, namely how the outputs of certain functions feed into inputs of others. I’m a total beginner here though, so I’m sure there are some obvious things I am just not understanding.

[TheTimeSavingCo]

Hm Power, apologies, the solution by @kuovonne is much much more elegant than mine, and I suggest you use that method instead

I’ve put it together here for you.

As suggested in kuovonne’s post, I have made it trigger the automations via a formula field in the People table, leading to a much more elegant flow that accomplishes what you’re looking for