Combining text field inputs with existing (other) table date

[ombdw]

Hi

I am very much new to AirTable so this may be a beginner’s question but I would appreciate the help very much. I try to describe the situation in detail below.

Context: I am setting up a more efficient lead generation & reach-out tool for sales purposes, automating some manual tasks such as data scraping & email generation. We have 3 email templates that we use, depending on the product that we want to advertise, plus we send to two types of contacts - English or German speaking (i.e. up to 6 different email templates)

Goal: I want to set up the process, so that once I have a Contacts table filled, I manually define for each contact which kind of email they should receive and in which language. Then those emails, based on the templates, with personalized tokens (i.e. Hello {Name}, ….) are stored in a new table “Personalized mail”. (then I will use that data to input to Make.com and send these email drafts to my Outlook draft folder).

—
Here how my tables set up look ideally:

Table “Contacts”
Name
Surname
Email
Company
Language (preferred communication language, selected manually after record is added)
Prompt name (the type of email prompt to be sent, selected manually after record is added)
Sender (selected manually after record is added)

Table “Email prompts” (predefined templates, more or less static table)
Prompt name
Language
Subject (e.g. “Special offer for you, {Name}!”)
Body

Table “Personalized emails” (output table I want to achieve)
Email (reference from Contacts table, for which person is this email generated)
Subject (so here where i need the “magic to happen”…. e.g. “Special offer for you, John!”)
Body

[TheTimeSavingCo]

Try using a combination of lookup fields and formula fields that will help you substitute the text in: 

SUBSTITUTE(
  {Subject (from Email prompts)} & "",
  "{Name}",
  {Name (from Contacts)} & ""
)

Link to base

[ombdw]

Dear Adam @TheTimeSavingCo 

This works wonders! I finally got a step ahead, thank you so much! 

I unfortunately struggled into 2 problems as follows:

1) Some Email prompts Subjects contain {Name} and some {Company} (i.e. name of the company, since we are also in B2B). Ideally, I define all substitute rules in the formula and whichever reference is found, it is automatically substituted.

2) In the Body of the Email prompt, I have multiple fields to be substituted, mostly the following are {Name} from Contacts, {Company}, and {Sender} (for signature, of person sending the email). Same here, I define all substitute rules in the formula and whichever reference is found, it is automatically substituted.

Is it possible to account for these in the personalized emails field formulas too?  (i.e. multiple Substitute conditions?)

Best,
Dom

[TheTimeSavingCo]

Yeap, use an "IF" to with "FIND" to check whether your text contains "{Name}", and if it does substitute the Name value, if not substitute the Company one

For the multiple fields, you'd need to nest the `SUBSTITUTE` formulas

---
Substituting one or two fields is simple enough, but if there are a few it'd be a lot easier to use an automation for this instead I reckon.  You'd trigger the automation from the "Personalized Emails" table and give it a conditional based on which "Email Prompt" was selected, and you'd set up your "Update Record" action to create the email with the Contact tokens in it:

I've updated the base with the automation as well