After doing some research in our forum, I came across the same question multiple times: "How do I read all the information from an Excel file and then use it to send an email or fill a form?". I am writing this article to give you a step-by-step guide about how you can read all the information that are stored in an Excel worksheet, and use it to send customized emails to multiple recipients. So, let's get started!

The first thing that we need, is an Excel file that contains the information that will be used to send the emails. Our Excel file may look like this:



In order to send a single email to a single recipient, we need to use the "Send Email" action and "hard code" the "Name" the "Email Address" and the "Referrer". All we have to do is drag and drop the "Send Email" action to the workspace,



and enter the static information in the various fields of the action:



As you can see in the screenshot above, the colored boxes represent the information that is stored in a different column of Excel. Specifically, the information from the "Email Address" column in the Excel file should go to the "To:" input field, the information from the "Name" column in the Excel file should replace the hard-coded John Doe, and the information from the "Referrer" column should replace the name "Larry Davis". After all, this is the reason why we are creating this job: to avoid having to manually send the emails to the different recipients.

In order to automate the specific task, there are 2 basic steps:
1) Read the information from the Excel file and store it in a variable
2) Loop through every row of the Excel table and send a different email for each row

Assuming that the path of the Excel file is: C:\My Files\SampleEmailAddresses.xlsx, the first thing that we need to do, is launch it:



Notice the output variable of the "Launch Excel" action. This variable (i.e. %ExcelInstance%) will be used throughout the job to identify the instance of Excel that we have just opened. Every action under "Excel" category requires that you specify the Excel instance to apply the action to.

The next thing that we need to do is read all the contents of the Excel file and save them in a variable. Here is an interesting question though: "Is there a way to know how many rows/entries this Excel file contains? We already know that there are 3 columns but how many rows are there in the xlsx file?". In the sample screenshot above, there are 6 rows of data (including the column names) but do we really want to create a job that will read rows 1-6? What if more rows are added in the future?

There is no need for guessing and there is no need to manually input the number of rows to be read; we will use the "Get First Free Column/Row" action instead:



We drag and drop the "Get First Free Column/Row" action to the workspace and the Properties window of the specific action pops up. In the "Excel Instance:" input field, we click the gear icon and we select the variable that we defined through the "Launch Excel" action (i.e. %ExcelInstance%).



Out of the 2 available variables that we can retrieve, we will only save the First Free Row of the excel instance (since there is a specific number of columns, three). The %FirstFreeRow% variable will contain the number of the first row in the Excel file that is empty. So, in our sample file, the %FirstFreeRow% variable will have the value 7 (since the 7th row of the Excel file is the first empty row).

Now that we know which rows to read, we are ready to use the "Read From Excel" action and save the data from the Excel file into a data table.



A short explanation of the numbers appeared in the screenshot:
(1): Drag and drop the "Read From Excel" action from the action list to the Workspace below the "Get First Free Column/Row" action.
(2): Select the Excel instance that we want to read the information from. In this case, there is only one Excel Instance.
(3): We select to retrieve the values from a range of cells, not a single cell's value only.
(4): The range starts at (Column 1, Row 1) and ends at (Column 3, Row %FirstFreeRow-1%). The %FirstFreeRow-1% is the number of the row that is before the first empty row. So, since, in our example, the %FirstFreeRow% is row no.7, %FirstFreeRow-1% is no.6. and the range that will be read is: (Column 1, Row 1) - (Column 3, Row 6) or from A1 to C6.
(5): When the specific option is checked, WinAutomation will understand that the first row of the data does not contain actual information but it contains the names of the columns (in our case this is true, since the it does contain the column names: "Name", "Email Address", "Referrer").
(6): The name of the variable where the data will be stored is %ExcelDataTable%.

Now, we have the information that we need stored in a variable. The only thing that we have to do to complete step no.1, is close the Excel file through the "Close Excel" action:



The next step:
Loop through every row of the Excel table and send a different email for each row
will be described in the second part of the blog post.