Help

Automatically combining records into one email

Topic Labels: Automations
Solved
Jump to Solution
1925 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Power_Bridge
4 - Data Explorer
4 - Data Explorer

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.

Project Screenshot

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!

1 Solution

Accepted Solutions
TheTimeSavingCo
17 - Neptune
17 - Neptune

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

  1. 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

See Solution in Thread

4 Replies 4
TheTimeSavingCo
17 - Neptune
17 - Neptune

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

  1. 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

Power_Bridge
4 - Data Explorer
4 - Data Explorer

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.

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