Skip to main content

Dear all,

 

During the first steps of my bot, I created a folder with a dynamic amount of Excel Files. These Excel files all have the same structure. The Excel files are also named after the persons who will receive the files.

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: john.hoffmann@yahoo.com).

 

The goal now is to send 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:

  1. Get all the file names as String variables: I looped through all files in the folder, which always contains 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 seperate 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)
  3. Find logic to map the Excel-Sheets to the Email-Addresses. Something like: if the name of the Excel-File == ExcelfilelI2] then send an Email to ExcelfilelJ2] -» 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

 

 

 

Hi @JulianRPAdev ,

 

From the scenario given above what I can suggest you is to follow below approach.

 

  • Loop inside the folder using Loop → For each File in Folder.
  • Using File → Get Path, retrieve the path of each file just before opening it through Excel and assign to a variable. (Also, map just the File name separately to another variable)
  • Once you open the Excel within another loop, check the File Name with the respective row record using If condition.
  • If it is matching, get the data from the corresponding Email Address column and assign to a variable.
  • Convert the previously saved File path into a File variable using File → Assign action.
  • Use the Email send action and mention the Email address. 
  • Attach the above file inside the attachment.

I hope it is clear and will help.


Please make to more clear .Need more clarity .Unable to understand the solution


Dear @Padmakumar ,

 

Thanks for your answer!

I solved the use case with separate Python scripts which do exactly as you describe.


Hi Padmakumar,

My problem statement

There may be different files coming through we transfer (Mail).

Now how to open each dynamic files and run the bot according to the constant file name  i have created ,do all manipulations and again send back the file with the original name of the file received through mail.

Unable to get any solution.


Reply