demo-attachment-472-woman-freelancer-female-hands-with-pen-writing-on-P369BAX1

How To Generate An Email Template In Excel

by

A client recently asked us to build them an Excel-based tool that could generate emails. They provided us a template for the text in the body of the email, but some elements needed to update based on user inputs. For example when the user selects a customer, the email text should show the correct name, address and telephone number for that customer. Imagine a mail merge, but where we only create one email at a time.

There are plenty of online resources that explain how to generate emails using VBA. So in this post we’re going to focus on the email text. Online searches proved to be somewhat unhelpful when adding the following restrictions:

  • Avoid using VBA
  • Avoid using helper cells
  • Avoid using heavily nested functions

In theory, none of these restrictions were necessary. But it felt like a nice little challenge to find a simple formula solution without using VBA. We also wanted to make the solution flexible enough to automatically accommodate new fields added to the template.

After a bit of trial and error, we found an elegant solution using one of Excel’s newer functions: REDUCE. This function reduces an array to a single value by applying a LAMBDA function to each array element. The returned output is the final accumulated value. The function parameters are:

  • Initial value: Sets the starting value for the accumulator.
  • Array: An array to be reduced.
  • Lambda: A LAMBDA that is called to reduce the array. The LAMBDA takes two parameters:
    • Accumulator: The value totalled up and returned as the final result.
    • Value: The calculation applied to each element in the array.

Let’s look at a simple example of how to use the REDUCE function:

EXAMPLE: REDUCE FUNCTION

Suppose I want to sum square values of an array. Our initial value of the accumulator is zero. We pass in the array of input values. Then the LAMBDA function takes the accumulator value (“a”) and adds on the square of the next value (“b”) in the array. The result is the final accumulator value after the entire array has been passed through the LAMBDA function.

An important thing to note here is that the accumulator does not have to be a sum. It could be a product or in our case a text string substitution.

SET UP

First we set up our inputs in a workbook. Using a Table takes advantage of the auto-resizing property and improves clarity of referencing. The first column contains the text strings that will be in the email template text. The second column contains the values we want to replace them with. Next we insert the email template text.

CREATING THE FORMULA

It is a little complicated on first look, so let’s work through it line-by-line.

  • LET – enables definition of variables to be used in the REDUCE function
  • Template – refers to the cell containing the template email text
  • FindText – refers to the table column containing text strings to remove from the template
  • ReplaceWith – refers to the table column containing text strings to insert
  • Counter – defines an array of values from 1 up to the number of rows in the set up table
  • REDUCE:
    • Initial value: Template – the accumulator starts as a text string of the template email text
    • Array: Counter – pass in an array from 1 to the number of rows, enabling us to loop through each row of the set up table
    • LAMBDA:
      • x – refers to the accumulator value being passed in to the function
      • y – refers to the array value being passed in to the function
      • SUBSTITUTE:
        • x – the text to start with
        • INDEX(FindText,y) – the text string found in row y of the FindText table column
        • INDEX(ReplaceWith,y) the text string found in row y of the ReplaceWith table column

In the first iteration of the REDUCE function: the Counter value is 1 and the function performs the following substitution:

SUBSTITUTE ( E3 , "[Insert Customer]" , "[Roundwood Park Ltd]" )

We will call the output of this substitution “RESULT-1”.

Then in the second iteration of the REDUCE function: the Counter value is 2. This time the accumulator value passed in is the result of the first iteration, “RESULT-1”. The function then performs the following substitution:

SUBSTITUTE ( RESULT-1 , "[Insert Product]" , "Widget 321" )

This process continues until all values of the Counter array have passed through the REDUCE function. The final result is the end value of the accumulator.

Here’s how the output looks:

TAKING IT FURTHER

You may have noticed that the template email text includes “[Insert Sign Off]” but our set up table has no equivalent entry. So the generic template text is still appearing in our output email. This is where setting up an inputs table really pays off.

We can add “[Insert Sign Off]” as an entry to the Find Text column and the set up table will automatically resize to accommodate. The REDUCE function also then automatically includes this extra iteration. So the output will show the extra string replacement, without any changes required to the formula.

WRAP UP

There’s a tidy single formula solution to replace many strings in a block of text. If you want to use it through a workbook you could also add it as a named LAMBDA function to reuse elsewhere.

For more ways to improve your Excel efficiency, feel free to get in touch.

Comments are closed.