Breaking News

Get Table From Email with UiPath

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.

New Project

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”.

UiPath New Project

Send an email which contains a table to your email address and write a subject. In this example I used the following email:

Subject: POs

Hello,

Please find below the information about your purchase orders.

PONumberTotalItem1Price1Item1Qty
PO24211500Laptop3005
PO111705Desk2353

Kind regards,

Getting Emails

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.

Add Extract Outlook Mail Activity UiPath

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.

Add Write Line Activity UiPath

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.

Add For Each Loop Activity UiPath

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.

Browse For Data Types UiPath

In the Type name text box write mailmessage, select MailMessage under System.Net.Mail and click the OK button.

Select Mail Message Data Type UiPath

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.

Configure Mails For Each Loop UiPath

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!).

Download UiPath Go Activity

Once you’ve downloaded the activity package, you’ll need to install it. Go to UiPath Studio and click on Manage Packages.

Manage Packages UiPath Studio

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.

Install Activity Package UiPath

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.

Add Activity To For Each Loop UiPath Studio

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).

Configure Extract HTML Datatable Activity UiPath Studio

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.

Select Datatable Type UiPath Studio

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.

Configure Tables For Each Loop UiPath Studio

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.

Select Write Range Activity UiPath Studio

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.

Configure Write Range Activity UiPath Studio

Finally, the automation flow should look like this.

Final Flow Extract Tables From Email With UiPath

Good Luck!

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.

About Marian A

One comment

  1. This code save over the original file.
    when i get table from 2 email

    It’s should be save table1.xlsx,table2.xlsx
    But NOT.

    pls Help me.
    Thank you.

Leave a Reply

Your email address will not be published. Required fields are marked *