Question

Send dedicated mail per Excel File-Name

  • 21 March 2023
  • 4 replies
  • 159 views
J
Badge +2

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 == 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

 

 

 

4 replies

Padmakumar
Userlevel 7
Badge +13

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.

Padmakumar
C
Badge +2

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

J
Badge +2

Dear @Padmakumar ,

 

Thanks for your answer!

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

C
Badge +2

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


Terms & ConditionsCookie settings