Email automation trigger - x days before event

[Peter1]

I have a calendar view and would like to send emails to the persons in one of the record’s fields. That is, if the value in the ‘Clerk’ field is Peter, I’d like to send an email to Peter. Additionally, I’d like to send emails 2 weeks prior to the event, 1 week prior to the event and then 2 days prior to the event. Is this possible?

[Hannah_Wiginton]

Hey Peter!

Welcome to the Airtable community!

Yes, this is possible.

Assuming your Events table has an Event Date field, you need to create a Formula field in your Events table. Let’s label it Days Until Event

In the formula field, enter the formula

DATETIME_DIFF({Event Date}, NOW(),'days')

This will calculate the difference between the current date/time and the date of the event.

Now, be sure you have either a field with the Clerk’s name and another field with their email OR a Collaborator field. (you can have these fields in your Events table or you can have it another table as long as you use a linked record with a lookup field)

Add data in these fields inside the first record in your Events table to set up, test, and create an automation.

---

In Automations, create an automation where the Trigger is When record matches conditions

Select your Events table

Add a condition of When ‘Days Until Event’ = 14
or ‘Days Until Event’ = 7
or ‘Days Until Event’ = 2

-Test the trigger

For Actions, choose Send email

In the To field, find your email field and click Insert

In your subject, you can write whatever you want or you can use values from your table

In the Message field, you can use values from your field like:

Hey Person,

It’s Days Until Event days until Event Name!

-Test the action

[Compagnie_de_da]

Thank you Hannah, this is useful. I know it's an old thread but I'm going to take a chance.

I built the matching condition and the EMAIL action. 

But I'm having an issue with the email address. It is indeed a linked record with lookup to another table.

I get an invalid email address when testing. "Invalid email address in the "to" field: flda..."

I suspect my email field is not set up properly. How to start trouble shooting for this? Any direction would be appreciated. Thanks

---

Now, be sure you have either a field with the Clerk’s name and another field with their email OR a Collaborator field. (you can have these fields in your Events table or you can have it another table as long as you use a linked record with a lookup field)