Many times I encountered requests to extract specific tables from the content of an email. I discovered that with UiPath this is quite straightforward, although it has some tricky parts as well.
If you’re still new to UiPath, you can find how to download emails by following this article: How to download emails with UiPath.
First let’s create a new project and name it ExtractMailTable. You can also add a description so you’ll know what this automation is about. You can add “This automation extracts a table from an email content”.
Send an email which contains a table to your email address and write a subject. In this example I used the following email:
Please find below the information about your purchase orders.
For this example, I’m going to use Outlook. Drag a Get Outlook Mail Message activity. I used a filter to get a specific mail I sent to my Inbox. The filter is written using VB language. You can find more information on UiPath Forum here.
The properties I set for this activity are:
Mail Folder – “Inbox”
Account – I left this one blank as it will automatically use the outlook account that is currently signed in on the current machine. If you’re using a shared mail folder, to make this work, instead of the account email address use the shared folder name. Be careful as this field is case sensitive.
Filter – “[Subject]=’POs'”
Only Unread Messages – I unchecked this as I’m using a filter and I know for sure that I want to get these emails no matter if they’re read or not
Top – 50. You can use a different number depending on how many mails you have, more recent than this one.
Messages – I created an output variable called mails of type System.Collections.Generic.List <System.Net.Mail.MailMessage>. You can also create variables by clicking on the output textbox and by pressing Ctrl+K, type the variable name and press Enter. This way, the UiPath Studio will automatically create a variable of the required type for you. You can see that for this example I used Sequence container as all the steps are sequential.
You can add a Write Line activity for tracing purposes. In the text field I wrote “Number of emails found: ” + mails.Count.ToString , so I can track the number of emails the Get Outlook Mail Messages returned.
Looping through Emails
Now that we’ve extracted the email(s) we need to loop through them. For that we’ll need to add a For Each loop activity.
Because we are looping through a list of mail messages (mails variable), we need to modify the Type Argument of the For Each loop. Click on that attribute and select Browse for Types.
In the Type name text box write mailmessage, select MailMessage under System.Net.Mail and click the OK button.
In the in textbox of the For Each loop, enter the mails variable. Once you start typing, you’ll see that the UiPath Studio intellisense will suggest you the variable name.
Getting HTML to Datatable Activity
To get the activity that allows you to extract the tables from an html, you’ll need to go to UiPath Go! This is UiPath marketplace where you can find lots of activities. These activities are all free to download, but for this you’ll need to create your own account.
You can find more information on how to create a UiPath Go! account and how to download activities here.
Navigate to HTML To Datatable, click the download button and click Add to Studio (if you’ve connected your studio to UiPath Go!) or Direct Download (if you didn’t connect your UiPath Studio to UiPath Go!).
Once you’ve downloaded the activity package, you’ll need to install it. Go to UiPath Studio and click on Manage Packages.
Select All Packages and in the Search text box write ExtractDatatablesFromHTML. Click on the found package and select Install. Click the Save button. Now the activity should be available in the activity panel.
Extracting Tables From Email Body
Now that we’ve installed the activity it’s time to use it. Drag an Extract Data Tables From HTML activity inside the For Each loop.
We need to configure the input and the output of this activity, so head over to the properties and set the following:
HTMLText – item.Header(“HTMLBody”).ToString. This will extract the HTML code from the email and convert it into a string.
Tables – Press Ctrl+K and write the datatable variable name. I used mailTables. This will automatically create an array variable of type System.Data.DataTable (that’s because inside the email we can have multiple tables).
By doing this, we’ve extracted the tables from our email. For the purpose of this example, let’s write these tables into excel files so we can see some results as well.
For this we’ll need to drag another For Each loop after our Extract Data Tables From HTML activity, but this time let’s rename the item to table and put our mailTables variable into the in textbox. Also we need to modify TypeArgument to System.Data.DataTable by browsing it as before.
Finally let’s create a variable called index (of type Int32) using Ctrl+K in the Index property of the For Each loop. This will give us access to the current index that being looped.
The last thing that we need to do is to drag a Write Range activity inside this For Each loop. Search for the activity called Write Range in the Activity Panel and drag the one that’s under System->File->Workbook.
Let’s configure this activity. In the WorkbookPathproperty, write “Table” + (index+1).ToString + “.xlsx”. This will name our Excel file Table[index number + 1].xlsx.
In the DataTable property, we need to add table which is our For Each loop object.
Finally, the automation flow should look like this.
If you enjoyed the article Get table from email with UiPath, don’t forget to share it with your friends.
Download the solution
Download the Get Table From Email project from the button below.