In one of my jobs, I’m responsible for the communication and scheduling of people into events. I was asked to prepare some bulking emails for a series of dates and various recipients. I wanted to make this process as easy on myself as possible, so I created a Google Sheet that would account for all of the dates, recipients, necessary people involved (from my workplace), and a meeting link.

This allowed me to not be overly concerned with whether I had all of the necessary information for each email that I was sending. Once I had a template all sorted, it was just a series of changing the intended date and copy-pasting (with some minor format adjustments) into a new email.

Here is the email template that I used:

Subject: Invitation to Attend Our Virtual Event

Dear [Recipient 1] and [Recipient 2].

I hope this email finds you well. I wanted to extend a warm invitation to you for our upcoming virtual event, [Event Name].

We believe that your insights and expertise would greatly contribute to the discussions we’ll be having.

Date: [Insert Date]

Time: [Insert Time]

Virtual Venue: [Insert Link]

Please mark your calendar for this event, as we anticipate a stimulating exchange of ideas and opportunities to network with fellow professionals in our industry.

As they will be your guide for the day, please feel free to reach out to [Reference Person’s Name] at [Reference Person’s Email Address] should you have any questions or require further information.

Looking forward to your participation!

Warm regards,

[Your Name]

[Your Position]

[Your Company/Organization]

From this template, we need to make sure that we account for a few different things:

• List of attendees,
• Event name,
• Dates,
• Times,
• Virtual links,
• People from the organisation who are official contacts, and
• Account for the different event types.

Here’s the Google Sheet link and let’s get into it:

https://docs.google.com/spreadsheets/d/1sgVQo1cGbKJ_L2yLiwhzlLpcKvon9FaZV8mBcmI_uIo/edit#gid=0

Setting up the Data

Let’s get started by collating the list of attendees. I’m going to make sure that all of their data is normalised before importing it into Google Sheets. Please note that all of these tables are listed in descending date order (newest date to older):

Next are the guides, or company’s contact persons, for each different event:

Then the list of events where there are allocated invites:

Now that we have all of that set up, we can start to work through the various parts of the email. Let’s start with the Event Date as this should be the easier part.

Event Date

For this variable in our problem, we can assign a cell the date and change it, as necessary, to test that our formulas are working. So, in Sheet1, I’m going to assign B1 to be the date cell:

The text located in cell A3 is exactly that — a text datatype. Turn this email text into a function by adding ‘=’ to the beginning of it and creating the entire cell as a string/text function:

We can start entering in variables that will change when we change the date. Let’s start with the date (B1). I’ll find the date portion of the email and start to edit it out:

Now I can replace [Insert Date] with the information in cell B1:

We can see that this is the first thing we need to account for. Google Sheets will pull the raw data information from cells when you reference them, which is why we’re seeing the value 45518 in the date section:

So, let’s integrate the TEXT() function around the B1 cell reference:

That’s looking better:

Event Name

Let’s start to change our event title based on the date of the event. Depending on how the data has been arranged, you could use VLOOKUP (‘Date’ arranged first in the data sheet) or we can use INDEX-MATCH, which we’ll be doing in this circumstance. This is due to the table displayed below. Note that we have the event names and event dates, listed by latest first (descending):

Since the date is not first in this table, we need to use the INDEX-MATCH process so that we can look to the left of the table. For more information on this, see this previous post. Let’s us INDEX and select all the data that we’re interested in (the table in the sheet ‘data’):

Next, we watch to match the row in that table where the dates line up (15/08/2024), or row 9 (in this table):

We now need to set which column that this data type (Date) is located in this table. I’m going to select cells K3:K12):

This is the important part. Similar to the fuzzy match in VLOOKUP (check that linked previous post), this next variable allows us to find the closest match. This is possible when we have the dates organised in descending order. So, I’m going to set this as -1:

Lastly, we want the output to be the name of the event, which is column 1:

INDEX(data!$K$3:$L12,MATCH(B1,data!$L$3:$L$12,-1),1)

We can now see in the output that we have the title of the event ‘Navigating the Future: Charting Paths of Innovation’:

Let’s test it by changing it to the 15th — this should give us ‘Innovative Frontiers: Mapping Out Tomorrow’s Possibilities’:

Event Time

We can now combine the techniques we used for the two previous attributes (date and event name) to report back the time of the event. We’ll use INDEX-MATCH for the event information, and TEXT to translate it into “hh:mm” format.

First, let’s get the event, based on the date we’re searching. I’m going to reference the Event table (data sheet, E3:I22) and I’ll be looking for the Event Date column (5). Like before, the table has been arranged in descending order, so we want to MATCH the date that we’re searching (Sheet1!B1) against the Event Date column (I, column 5 in our circumstance) and set the search type to -1 so that it finds the smallest value greater than or equal to the searching date:

INDEX(data!$E$3:$I$22,MATCH(B1,data!$I$3:$I$22,-1),5)

Now, we can wrap that INDEX-MATCH function in a TEXT function and report back the hours and minutes:

TEXT(INDEX(data!$E$3:$I$22,MATCH(B1,data!$I$3:$I$22,-1),5),“hh:mm”)

And let’s confirm by setting the date to the 29/08/2024 (which should be ‘Innovation Odyssey: Journeying into New Territories’ at 11:00):

Virtual Link

Remember, this table is listed in descending date order. We have the event names, dates, and their respective Zoom links.

Using the same process as before, we can set up the INDEX-MATCH for the virtual like. We want to match the searchable date to the second column (L) and return the third column (M, Link):

INDEX(data!$K$3:$M$12,MATCH(B1,data!$L$3:$L$12,-1),3)

We can see that we have successfully matched the Zoom link to the date:

(Guide) Reference Person’s Name and Email Address

Let’s now match the respective allocated guide to each one of the sessions (ordered by descending date):

I’ll be matching the date in the third column and return, first, the first column (person’s name):

INDEX(data!$A$3:$C$12,MATCH(B1,data!$C$3:$C$12,-1),1)

Now let’s do the same process for the same person’s (on that date) email address:

INDEX(data!$A$3:$C$12,MATCH(B1,data!$C$3:$C$12,-1),2)

Attendees

These attendees are the [Recipient]’s in the email, who are the experts in the field. We have arranged for 2 people each session for this series:

Let’s do the same previous matching process for the first recipient:

INDEX(data!$E$3:$I$22,MATCH(B1,data!$I$3:$I$22,-1),1)

For the second recipient, we need to work our way back up from the bottom of the “list”. Since each date has two entries, the first matching process (just above) is finding the first (highest) entry of the two. So, we need to get the next one in the list, by traversing downwards (positive 1):

INDEX(data!$E$3:$I$22,MATCH(B1,data!$I$3:$I$22,-1)+1,1)

Date Trigger

If you have seen my 10 FAQs post, this next process will be very familiar, but let’s walk through it again just in case you need a reminder. I’m going to create a list of all the date/times that the events are being hosted using the UNIQUE function in the data sheet:

=UNIQUE(L3:L12)

Let’s go back to Sheet1 and click on cell B1. This is where we want the dropdown list ‘button’ to be created. Select Data > Data validation:

Select + Add rule:

Select Dropdown (from a range):

This is where we are going to retrieve that list of dates that we have just created:

When we click on cell B1 (on Sheet1), we can now see all the various dates that we can pick from:

For a test, let’s change it to 15/08/2024 and check that we have all of the correct information:

• Attendees: Ava Martinez, Noah Johnson,
• Event name: Innovative Frontiers: Mapping Out Tomorrow’s Possibilities,
• Date: 15/08/2024
• Time: 11:00
• Zoom link: https://sciinnovations.zoom.us/j/91142115212
• Guide name: Alexander Carter
• Guide email: alexander.carter@sciinnovations.com

That looks good. Let’s try one more, the second last entry — 11/09/2024:

• Attendees: Madison Adams, Benjamin Wilson,
• Event name: Unleashing Potential: Harnessing Innovation for Growth
• Date: 11/09/2024
• Time: 11:00
• Zoom link: https://sciinnovations.zoom.us/j/48538258790
• Guide name: Emily Patel
• Guide email: emily.patel@sciinnovations.com

Now we can finish off the last bits and pieces; [Your Name], [Your Position], and [Your Company/Organization]:

Extras

Email generation

Note: I generated a list of random names (first and last; E3:E22) then used the following formula to create their email addresses: =SUBSTITUTE(LOWER(E3),” “,”.”) & VLOOKUP(RANDBETWEEN(1,5),$N$16:$O$20,2,0)

See how you go adapting this to your own process. Have you already created one of your own? Have you been able to create something better than the template that I’ve made? Feel free to share what you’ve got so I can learn a few new things that I don’t already know!