Automation Anywhere: Send dedicated mail per Excel File-Name

Question

During the first steps of my bot, I created a folder which stores a dynamic amount of Excel Files. These Excel files all have the same structure (same column names and input only the amount of Excel File differs from each Bot run). Each Excel files is named after the persons who will receive the file.

Example: folder with 3 Exel-Files

Hoffmann John.xlsx

Einstein Albert.xlsx

Ansen Anna.xlsx

Each Excel-File contains the Name (for instance Hoffmann John) at the field position I2 and the Email-Adress at the field J2 (for instance: [email protected]).

The goal now is to enhance the Bot with steps which send automatically an email to each person when there is a Excel-File with the same name in the folder. The email always has the same text, but should only contain the respective Excel-file in the attachment. So I need to somehow find a logic which checks the dynamic created files in the folder, then sends an Email to each person with the respective Excel-File.

My solution approach:

First I need all the dynamic Excel files as file variables to attach them in the Send: Mail Action.

1. Get all the file names as String variables: I looped through all files in the folder, which always contain the before-created Excel-Files. I created a name and extension variable. It shows me the name and the .xlsx in a message box. I could not manage to get each file name into a separate variable with string assign within the loop (it only shows all file names and to filter for each name in the loop is not possible. At least I don’t get it).

2. Transform all String variables with the Excel Sheet names to file variables (Email: Send action attachment only works with file variables). Problem: When I want to do the "File:Assign" action, it does not take String Variables only other File-Variables. How to solve this?

3. Find logic to map the Excel-Sheets to the Email-Addresses. Something like: if the name of the Excel-File == Excelfile[I2] then send an Email to Excelfile[J2] -» if the name of the Exce-File is the Same as the Person’s Name then send an Email to the respective Email-Address.

I’m happy about any hints!

Thank you