How to automate copy selection from body from email to excel file?

Question

I'd like to automate the following actions in a sequence:

Scan e-mail folder for mails for specific content (subject or body, both are possible);
If found, I want to search through the body of the e-mail for a specific string which is unique per e-mail;
Copy the unique and specific string;
Paste it into a cell but which cell is depended on the information in the row.

Both Outlook and Excel are of Microsoft 365.

score 1 · Answer 1 · answered May 15 '22 at 15:49

You can automate both applications to get the job done.

First, to find mail items that correspond to your conditions you can use the Find/FindNext or Restrict methods of the Items class. You can simply combine all your conditions into a single query string using the logical AND operator. Read more about these methods in the following articles:

How To: Use Find and FindNext methods to retrieve Outlook mail items from a folder (C#, VB.NET)
How To: Use Restrict method to retrieve Outlook mail items from a folder

Second, to cope the required information from the message body you can use different properties. The Outlook object model supports three main ways of customizing the message body:

The Body property returns or sets a string representing the clear-text body of the Outlook item.

The HTMLBody property of the MailItem class returns or sets a string representing the HTML body of the specified item. Setting the HTMLBody property will always update the Body property immediately. For example:

 Sub CreateHTMLMail() 
   'Creates a new e-mail item and modifies its properties. 
   Dim objMail As Outlook.MailItem 
   'Create e-mail item 
   Set objMail = Application.CreateItem(olMailItem) 
   With objMail 
    'Set body format to HTML 
    .BodyFormat = olFormatHTML 
    .HTMLBody = "<HTML><BODY>Enter the message <a href="http://google.com">text</a> here. </BODY></HTML>" 
    .Display 
   End With 
 End Sub

The Word object model can be used for dealing with message bodies. See Chapter 17: Working with Item Bodies for more information.

From the Word object model you could copy and paste the required information directly to Excel.

score 0 · Answer 2 · answered May 16 '22 at 15:11

You can do this within UiPath as well.

Using the GetExchangeMailMessages activity , you can filter on Subject etc. to read only the messages you are interested in. With this activity you don't need to have Outlook client installed, you use this URL in Server property to connect - "https://outlook.office365.com/EWS/Exchange.asmx".
From the returned messages, I have used Regex.Match function to parse out various details from the Body. This has worked pretty well.
You can use the parsed and matched Body data in variables.
You can use the parsed and matched Body variables to write to a data table/cell and to an excel file etc..

Hope this helps.

How to automate copy selection from body from email to excel file?

2 Answers2