Tutorial: Extracting Data From Automated Emails and Moving It to a Desktop Application

In this tutorial, we will create an automation that makes it easy to extract specific data from blocks of text that follow a certain format. Let's imagine that you work in a bank and receive many email notifications related to new deposit transactions. You must open each email, manually copy the amounts and paste them into a teller application, and then move each processed email to an Outlook folder. The emails are automated, they all follow a template and the same text always appears next to the amounts you need in the email body. For example:

!!!This is an automated message, please do not respond.
A new deposit transaction can be submitted:
Cash in: $10
On Us Check: $20
Not On Us Check: $15
Thank you.!!!This is an automated message, please do not respond.
A new deposit transaction can be submitted:
Cash in: $10
On Us Check: $20
Not On Us Check: $15
Thank you.

The default Project Notebook contains formulas that can extract only the needed amounts. We will manually edit the Project Notebook to extract the values from one email, as described in Step 2: Set up a project and get the necessary files , and then configure our automation to extract the same values from all similar emails.

The automation performs the following tasks for each email in an Outlook Search Folder containing the most recent unread automated emails related to deposit transactions:

Copies the body of the email and pastes the data to the Project Notebook where specific data is extracted using Excel formulas for text manipulation.
Enters the data field by field in a desktop application and submits the data.
Marks the email as read and moves it to an Outlook folder for processed emails.

We will create a project and start by adding a Use Desktop Outlook App activity to indicate the Outlook account to use. We will then add a For Each Email activity, and then, to indicate the automation must be executed for each email, we will place all subsequent activities inside For Each Email. We will add a Write Cell activity to copy the body of each email to a specific cell in the Project Notebook. We will then add the UI automation activities needed to enter the data in our desktop application, and finally activities to mark the email as read and move the email .

Step 1: Create two folders in Outlook.

For this example, we'll use emails with the subject New Deposit Available and a body as mentioned earlier.

To create a Search Folder for unread emails from today that have the specified subject:
1. Open Outlook, then right-click Search Folders in the Folder pane and select New Search Folder.
2. In the New Search Folder window, select Create a custom Search Folder > Choose.
3. In the Custom Search Folder window, enter Today's deposits in the Name box and then select Criteria.
4. In the Messages tab of the Search Folder Criteria window:
  - From the drop-down menus next to Time select received and Today.
  - In the Search for the word(s) box, enter New Deposit Available.
  - From the In drop-down menu, select subject field only.
5. From the More Choices tab, select Only items that are and then unread from the adjacent drop-down menu.
6. Click OK three times to close the windows and save the folder.
7. Create a folder in your Inbox called ProcessedDeposits. This is the folder where the emails will be moved after they are processed.
8. Send a few emails to the email account you use in the automation with the specified subject and a body which follows the specified template, but enter different amounts in each email.
9. After sending them, make sure they show up in the Today's deposits search folder.
Step 2: Set up a project and get the necessary files.
1. Create a new blank project using the default settings .
2. Open the Project Notebook for the project and copy the body of an email to cell B4 in the Text sheet, then manually enter the text to the left of each of our three values in cells D5:D7, Cash in: in cell D5, On Us Check: in cell D6, Not On Us Check: in cell D7. Because there is no text to the right of the values, cells E5:E7 must be empty. The formulas already in cells F5:F7 should extract the values, as you can see in the image below.
  
  Save and close the Project Notebook.
3. Download and extract the archive with the automation project in this tutorial using the button at the bottom of this page. Double-click the file DoubleUI.exe to open the DoubleUI application.
Step 3: Add the Outlook account to the project and iterate through the messages in the Search Folder.
1. In the Activities panel, select the Mail tab and then double-click Use Desktop Outlook App. The activity is added to the Designer panel.
2. In the activity, the default email account is already selected in the Account field. If you want to use a different account, select it from the drop-down menu.
  
  In the Reference as field, leave the default value Outlook as the name by which to refer to the account in the automation.
3. In the Activities panel, drag the For Each Email activity and drop it inside the Use Desktop Outlook App activity.
4. In the For Each Email activity, click Plus on the right side of the In emails from field, and then select Outlook > [Search Folders] > [Today's deposits].
  
  You have indicated that you want to iterate through the emails in the Outlook Search Folder "Today's deposits".
Step 4: Copy the email body to a cell in the Project Notebook to extract text.
1. In the Activities panel, select the Excel tab, then drag the Use Excel File activity and drop it inside the For Each File In Folder activity.
2. Under the Excel file field, browse to the "Project_Notebook.xlsx" Excel file.
3. Inside the Use Excel File activity, add a Write Cell activity.
4. In the Write Cell activity:
  - Click Plus on the right side of the What to write field and then select CurrentMail > Body to indicate that you want to copy the body of the current mail in the iteration.
  - Click Plus on the right side of the Where to write field, and then select Project Notebook (Notes) > Indicate in Excel to open the file in Excel. In the file, select the Text sheet, and then the B4 cell, and click Confirm in the UiPath tab in the Excel ribbon. You have indicated that you want to paste the email body to cell B4 in the Text sheet of the Project Notebook.
Step 5: Add the desktop application and the activities to perform in it.
1. In the Activities panel, select the App/Web tab, and then drag an Use Application/Browser activity and drop it inside the For Each Email activity below the Write Cell activity. A Use Application/Browser activity is added to the Designer panel.
2. In the Use Application/Browser activity, click Indicate Application, and then move the mouse pointer on the DoubleUI application. When the window is highlighted, click anywhere in DoubleUI.
  
  The Use Application/Browser activity is updated and a screenshot of the window appears inside the activity.
3. In the Activities panel, select the App/Web tab, and then drag a Type Into activity and drop it inside the Use Application/Browser activity. A Type Into activity is added in the Use Application/Browser activity.
4. In the Type Into activity:
  - Click Indicate target on screen, then move the mouse pointer on the Cash In field in the DoubleUI.exe application and, when it is highlighted, click the field. The text field is identified as the target and the Cash In label next to it is identified as an anchor. Click Confirm in the Selection Options window to save the selection. An image of the target and anchor is displayed in the Type Into activity.
    
    You have indicated that you want to type into the field Cash In from the DoubleUI.exe application.
  - Click Plus on the right side of the Type this field, and then select Excel > Indicate in Excel. In the Excel file, select the F4 cell (the Cash In value) and click Confirm in the UiPath tab of the Excel ribbon. The Type this field displays your selection as [Excel] Text!F5.
    
    You have indicated that you want to copy the Cash In value from the Excel file to the Cash In field in the DoubleUI.exe application.
5. Repeat steps 3-4 two more times to add Type Into activities to copy the On Us Check and Not On Us Check values from the Excel file (cells F6 and F7 respectively) to the corresponding fields in the DoubleUI application.
6. In the Activities panel, select the App/Web tab, and then drag a Click activity and drop it inside the Use Application/Browser activity after the Get Text activity. A Click activity is added in the Use Application/Browser activity.
7. In the Click activity, click Indicate target on screen, then move the mouse pointer on the Accept button in the DoubleUI.exe application and, when it is highlighted, click the button. The Accept button is identified as the target and the Cancel button is identified as an anchor. Click Confirm in the Selection Options window to save the selection. An image of the target and anchor is displayed in the Click activity.
  
  You have indicated that you want to click the Accept button from the DoubleUI.exe application.
Step 6: Mark the email as read, then move it to an Outlook folder.
1. In the Activities panel, select the Mail tab and then drag a Mark Email as Read/Unread activity and drop it inside the For Each Email activity below the Use Application/Browser activity. A Mark Email as Read/Unread activity is added to the Designer panel.
2. In the Mark Email as Read activity:
  - Click Plus on the right side of the Email field and then select CurrentMail to indicate that you want to mark the current mail in the iteration.
  - From the Mark as drop-down menu, select Read.
3. In the Activities panel, drag a Move Email activity inside the For Each Email activity below the Mark Email as Read/Unread activity. A Move Email activity is added to the Designer panel.
4. In the Move Email activity:
  - Click Plus on the right side of the Email field and then select CurrentMail to indicate that you want to move the current mail in the iteration.
  - Click Plus on the right side of the Move to field, and then select Outlook > [Inbox] > [ProcessedDeposits]. You have indicated that you want to move the email to the ProcessedDeposits folder.
5. Click Save in the StudioX ribbon to save the automation, then click Run to execute the automation.
  
  For each email in the Outlook Search Folder, the body is copied to Excel, the data is extracted and entered in the DoubleUI desktop application, the email is then marked as read and moved to a different folder.
Download example