In the first part of the "Using Data from Excel" blog post, we retrieved all the information from the Excel file and stored them in a data table variable called: %ExcelDataTable%. We also added a "Send Email" action that will send a single email to a specific recipient. In the second part of the blog post, we are going to create a Loop in order to send the email to every address in the xlsx file.
Our job should look like this so far:
Now, we need to send a different email for every row that is contained in the data table. To do that, we will have to iterate through the rows of the %ExcelDataTable% variable. We can do that with the "For Each" action:
Let me explain how the "For Each" action works:
You may have noticed that whenever you place a "For Each" action in your job, it is always accompanied by an action called "End Loop". These 2 actions define the loop and every action that is placed between them will be repeated for every item of the list or, like in this case, for every row of the data table.
This sounds very complicated but in fact it is not. Let's see our example so that you can understand exactly how the "For Each" action works:
When the job execution reaches the "For Each" action, it checks the number of rows in the %ExcelDataTable%. For every row, WinAutomation will execute the actions that are between the "For Each" and the "End Loop" actions, and will store the current data row in a variable named %CurrentDataRow%.
So, in our example, the "Send Email" action will be executed 5 times (since there are 5 lines with data in the Excel file that we previously read), and in each iteration, the variable %CurrentDataRow% will have a different value.
Notice, that if we run the job now, the "Send Email" action will be executed 5 times, but it will only send the same email, to the same recipient 5 times. If we want to send an email to a different email address in each iteration, we will have to modify the elements of the email that need to be changed; the recipient's name, the recipient's email address and the referrer's name:
So, for our sample Excel file (that you can see here):
- the first email will be sent to johndoe@example.com, the name will be John Doe and the referrer will be James Smith,
- the second email will be sent to janedoe@example.com, the name will be Jane Doe and the referrer will be Robert Johnson,
... and so on, for all 5 rows that were retrieved from the Excel file.
Comments
Samantha
Jul 14th, 2011 18:35
At the moment, you can automate this individually for each web form you have using mouseclicks and keystrokes in combination with the read from excel file action. In the forthcoming release, web submisions will be by far easier to accomplish. :) Samantha