How do I open password protected file using UiPath?

Question

I am using UiPath to create a robot to get files from email. Some files are password protected and some files are not protected.

The password-protected files are sent in with a password in email body.

Example email
From: ABC <[email protected]>
Sent: Monday, 5 October 2020 10:54 AM
To: BCD <[email protected]>
Subject: Files

The password is: ......

There can be 10 emails and 2 have password-protected files do I let the robot know which files are password protected and to open the password-protected Excel file and move the data to a mega Excel sheet containing all the files from the 10 emails.

I am unsure of the activities to put in the workflow to perform these functions. I am also unsure if the below method I did is the right way to approach this.

Answer 1

This is just one option you have. As I had good experiences in the past with the BalaReva.Excel.Activities, maybe it's worth to be tested by you.

So as the image shows, this Connect comes with plenty of Libraries. You need to download the whole package and take the functionality ProtectUnProtectSheet. That activity should work on your case.

For a full example check out this.

Answer 2

I'm not sure at what step of your flow you are having problems with, but here are a few pointers on some of the things you need to do after you manage to iterate through each email (which you seem to be doing already):

• to extract passwords from email bodies. You could do two things here: either apply a Regex directly on the string with the content (if you are confortable writing a few 1-2 line of vb.net code) or you go the long route of using IntelligentOCR package, Digitize the text (you will have to write it to a file first), extract the data using "Extraction Scope" and "Regex Extractor", and take it from the results json with extracted data
• open Excel and use the password: there is a package called UIPath.Activities.Excel that can do this.

Answer 3

Did you try to get System.Net.Mail.MailMessage.Body (https://learn.microsoft.com/en-us/dotnet/api/system.net.mail.mailmessage.body?view=netcore-3.1) and parse the string to search the password? So in your for each loop, you can use "mail.Body" and retrieve all email text.