Automatically sending emails to individual records

[Ann]

Hi everyone!

We have started using Airtable for teaching and I’d like to have a mechanism that sends students their grades automatically each week. Each record would have information like the following in a table called ‘Grades’:

• ID: 1, Name: Anna, Email: abc@def.com, Grade HW1: 1, Grade HW2: 0.
• ID: 2, Name: Ben, Email: def@ghi.com, Grade HW1: 1, Grade HW2: 1.

I want to send out an email each Monday to each student individually (they are for data protection reasons not allowed to see each others grades) that looks like this:

Hi [Name],
These are your current grades:
HW1: [Grade HW1]
HW2: [Grade HW2]
Best,
Ann

What is the easiest way to do this? I have tried the “Find records” action in an automation with the “Send email” action but this gives me all field values instead of just those of a single row. I have started to look into scripts but I’ve only started to learn Javascript and am a bit lost.

I’d appreciate any input!

Thank you in advance,
Ann

P.S. An alternative solution could be to have a link or some page that students can access where they put in their ID and then the grade report would be send to the email linked to that ID (we’d actually prefer this solution but it seems a little more complicated). If anyone can help with this, too, we’d be super grateful!

[Vivid-Squid]

You should be able to do this without javascript.

Add a date column or a status (ready to send) then create a view that only shows the grades for this week or that are marked ready to send. Then create an automation that triggers on When a record Matches a view.

[kuovonne]

Welcome to the Airtable community!

In order to send a different email to each student, you need to trigger the email automation from each student record. You can have the information in a rollup field, and email the contents of the rollup field. Or you can do a “Find records” action to look for grade records for that student.

Because you need the automation to trigger for each record, you cannot use the “Scheduled time” trigger. Instead, you need trigger the automation for when the record meets conditions and the result of a formula field. The formula can output the day of the week with DATETIME_FORMAT(TODAY(), "dddd"), and the condition could be when the formula field is “Sunday”.

Alternatively, if you want users to be able to log in to get their grades, you can use a portal system such as MiniExtensions, Stacker, Softr, or Pory.

[Drew_Nemer]

This is similar what I am trying to do.

But I want to sent each employee a list of their past dues. Each record has an employee’s email as a collaborator and a due date.

If today is past the due date, the field is moved to a view titled “Past Dues”. I want to send past dues to each employee, but I don’t want employees to see each other’s past dues, and i have too many employees to make automation for each one.

[kuovonne]

Drew_Nemer:

But I want to sent each employee a list of their past dues. Each record has an employee’s email as a collaborator and a due date.

It sounds like the “past dues” are in linked records. You need a rollup field of the past dues. Then have the conditions for the automation be based on both the formula field for the day of the week, and and the rollup of the past dues.

[Drew_Nemer]

that actually worked quite well! thank you!

Drew