Automated emails

Monisa Nayak

Hello,

I created a Grid sheet with various columns and wanted to generate automated emails out of it.

I need a solution for the following that:The existing columns are Granted By, Granted To and Valid Until.

I want a automated email to be sent based on the below requirements:

"Legal requirements" field = legalization (with or without apostille) and "Valid until" = today plus 4 months -> Reminder email to be sent to "Local contact point" and CC our team

"Legal requirements" field = e-signature or signature and "Valid until" = today plus 3 months -> Reminder email to be sent to "Local contact point" and CC our team

Reminder emails should be repeatedly sent once a week until we change the field "Status" to "in work".

As a preliminary template the reminder text could be:

Subject: PoA for <Granted by> needs to be renewed

Text: Dear Colleagues, the PoA issued by <Granted by> to <Granted to> will expire on <Valid until>. Please contact your Pricing-Team as soon as possible for the renewal of the PoA.

Jake Gustafson

Trigger: When a date is reached; run custom every 1 week (pick your desired day of week)

Conditions: [Status] = some value that is not 'in work' (I don't think we have enough context to know for sure what this should be set to)

Condition Path 1: [Legal requirements] is one of (legalization w/apostille; legalization w/o apostille) AND [Valid until] is in the next (days) 120 (if this hard coded number of days is not acceptable, then you might need to use some helper columns to work with actual years & months to calculate based on those).

Action Path 1:
- Alert Someone ([Local contact point] - you can use the Contacts in a cell for this
- Alert Someone (your team) - you will have to hard code these unless you have an alias that is in a field

Condition Path 2: [Legal requirements] is one of (e-signature or signature) AND [Valid until] is in the next (days) 90 - same disclaimers as above with hard coded number of days versus actual calendar months may require helpers

Action Path 2:
- Alert Someone ([Local contact point] - you can use the Contacts in a cell for this
- Alert Someone (your team) - you will have to hard code these unless you have an alias that is in a field

Although your reminder emails are essentially the exact same, the condition paths route uniquely based on the parameters you defined. So you do have some flexibility to customize each.

Hope that helps, comment if you need additional guidance.

Remember that dynamic values in the emails need double curly brackets and exact spelling of the columns {{Granted by}}