Project: Generate Emails from Excel Data

This scenario demonstrates how to extract customer data from multiple Excel files and then use that data to automatically generate individualized emails.

Scenario Overview

In this scenario, an email will be generated for every client company that has an outstanding amount owing, as shown below.

The email includes the following data points:

  1. Email address of the contact person

  2. First name of the contact person

  3. Name of the client company

  4. The amount owed

  5. Standard fixed text around the texts above

The data required must be obtained from two separate Excel files:

CustomerBalanceList.XLS

This Excel file lists only the names of client companies that have outstanding amounts. It lists the name of the company and the amount owed.

CustomerContactList.XLS

This Excel file lists contact details for all client companies and for the contact person at each.

Note that each email to be sent requires data from both Excel files.

Download Project Files

  1. Download the project file here.

  2. Unzip and copy the files to the folder %AppData%/Nice_Systems/AutomationStudio/Projects.

  3. Download the Excel files here.

  4. Copy the Excel files to the folder c:/temp.

Recommended Implementation Approach

This scenario follows the implementation steps below:

  1. Load the required Excel data into the project.

  2. Build a list of data required for all emails.

  3. Generate the body text for each email and send.

  4. Test the solution.

The workflow mirrors steps 1 to 3:

Load Excel Data into the Project

When provided with external data files, the intuitive approach is often to upload all the data in those files and then manipulate the data within Automation Studio.

In this case, we need to search for data in the file CustomerContactList.XLS. For example, for every company listed in CustomerBalanceList.XLS, we need to look up the first name and email address of the contact person in CustomerContactList.XLS. It will be more useful to upload each column of each Excel file into a separate list variable so that we will be able to use the lists' Get Value at Position and Find Position by Value functions later on to search for and retrieve data.

  1. Create the following list variables:

    List Name

    Description

    Amount Owing

    A list of all the values in the balance_owed column (B) in CustomerBalanceList.xlsx.

    Companies_All

    A list of all the values in the company_name column (C) in CustomerContactList.xlsx.

    Companies_Email

    A list of all the values in the email column (K) in CustomerContactList.xlsx.

    Companies_FirstName

    A list of all the values in the first_namecolumn (A) in CustomerContactList.xlsx.

    Companies_Owing

    A list of all the values in the company_name column (A) in CustomerBalanceList.xlsx.

    See Create a List Variable.

  2. Add a step to a blank workflow.

  3. To populate the list Companies_Owing, use the Get Excel Column Values function to retrieve the data in column A from CustomerBalanceList.xlsx, from cell A2 downward. The path to the file is c:/temp/CustomerBalanceList.xlsx.

  4. To populate the list Amount_Owing, use the Get Excel Column Values function to retrieve the data in column B from CustomerBalanceList.xlsx, from cell B2 downward. The path to the file is c:/temp/CustomerBalanceList.xlsx.

  5. To populate the list Companies_All, use the Get Excel Column Values function to retrieve the data in column C from CustomeContactList.xlsx, from cell C2 downward. The path to the file is c:/temp/CustomerContactList.xlsx.

  6. To populate the list Companies_FirstName, use the Get Excel Column Values function to retrieve the data in column A from CustomeContactList.xlsx, from cell A2 downward. The path to the file is c:/temp/CustomerContactList.xlsx.

  7. To populate the list Companies_Email, use the Get Excel Column Values function to retrieve the data in column K from CustomeContactList.xlsx, from cell K2 downward. The path to the file is c:/temp/CustomerContactList.xlsx.

  8. Run the workflow. Note that the Excel files must not be open in Excel when running the workflow.

    You should find that:

    • All three lists read from CustomerContactList.xlsx have 29 entries corresponding to the 29 companies listed in that file. Open up the lists to verify that their contents match the data in the Excel file.

    • Both lists read from CustomerBalanceList.xlsx have 7 entries corresponding to the 7 companies listed in that file as owing money. Open up the lists to verify that their contents match the data in the Excel file.

Assemble a List of Data Required for Emails

In this step we will build a list that will contain the following data for each email to be sent:

  • First name of the contact

  • Email of the contact

  • Company name

  • Amount owed

We will not include the actual body text to send in the email in this list. We will assemble those texts separately in the next step.

To store the data above, we will create:

  • A user-defined type that we will call Email_Data.

  • A list of type Email_Data that we will call Email_Data_List.

  • A complex variable of type Email_Data that we will populate for each client company in turn before adding its contents to the list, that we will call Email_Data_Temp.

  1. Create a user-defined type. Name it Email_Data and add text parameters called Company Name, First Name, Email Address, and Amount Owed.

  2. Create a list variable of type Email_Data. Name it Email_Data_List.

  3. Create a complex variable of type Email_Data. Name it Email_Data_Temp.

  4. Add a step to the workflow.

  5. We will iterate through the list Companies_Owing to build up the list Email_Data_List. Use a For-each statement. We will call the iterator company and set the list to iterate through Companies_Owing.

  6. We will need a counter to help us select items from the list Amount_Owing. Create a number variable called counter.

  7. Increment counter by 1 using the Add Number function.

  8. Write the name of the company in the current For-each iteration to the Company Name property of Email_Data_Temp.

    Select the company (list item) as shown below:

  9. To populate the Amount Owed property of Email_Data_Temp, use the Get Value at Position function to retrieve the amount owed value from the Amount_Owing list at the position of counter.

  10. We now need to find the row in the lists built from CustomerContactList.xlsx that corresponds to the company for which we are collecting data in the current For-each iteration.

    First, create a number variable called index.

    Use the Find Position by Value function to find the row in Companies_All that corresponds to the current company name. Write that value to the variable index.

  11. Retrieve the value at position index from the list Companies_FirstName using Get Value at Position and write that to the First Name property of Email_Data_Temp.

  12. Retrieve the value at position index from the list Companies_Email using Get Value at Position and write that to the Email Addressproperty of Email_Data_Temp.

  13. Now that all the properties of Email_Data_Temp have been populated, we can add its contents to the list Email_Data_List. It is important to add a Duplicate of Email_Data_Temp so that Email_Data_List is not modified every time Email_Data_Temp is modified.

  14. Run the workflow. Note the Excel files must not be open in Excel when running the workflow.

    You should find that the list Email_Data_List includes 7 rows - one for each client company with an outstanding amount.

    Open up a few entries in the list to ensure that the first name, amount owed, and email address all correspond to the same company name in the Excel files.

Formulate Email Body Text and Create Emails

In this step, we will iterate through the list Email_Data_List. For each company in the list, we will assemble a text string to use for the email body text based on the values of the properties in the company's entry in Email_Data_List, and will then create an email.

  1. Add a step to the workflow.

  2. We will iterate through the list Email_Data_List. Use a For-each statement. We will call the iterator company and set the list to iterate through Email_Data_List.

  3. Create a text variable called Temp_Email_Text that will store the text to be used for the email body.

  4. Concatenate the texts needed for the email body using a combination of the values in Email_Data_List and entered text.

  5. Now send the email using the Create Outlook Email Message and Send function. Populate its parameters as follows:

    Parameter Name

    Value

    Subject

    Amount Owed

    Recipient

    company(List Item).Email Address

    cc

    Empty Text

    bcc

    Empty Text

    body

    Temp_Email_Text

    attachments

    Empty Text
    send

    False

    Note: In a real solution this would be set to True, but to prevent sending junk emails while testing this solution, this value should be set to False.

Test the Solution

After running the full workflow, seven emails should be opened, each fully prepared for one of the client companies that have amounts outstanding. Check that the contents of the emails are correct as per the Excel files.