Copy the body of an Outlook Email and put into an Excel sheet

[deguy42 (CG)]

Hello,

This has two areas i’m struggling with, how to get the body of the emails into an excel sheet in the correct columns, and how to put this data into the next empty row in the excel sheet. I have worked with finding empty rows before but never combining this with placing data from emails into correct columns and an empty row.

Here is what the email looks like and is always in this exact format:

Here are the headers for the excel document:

My idea is to iterate through the emails getting the body, pasting it in the spare excel sheet, re-organise it, and then use a for each row to find the next empty row, and paste the data into there. Then repeat for the next email.

I’d be grateful for any help.

Thank you!

[kumar.varun2 (Varun Kumar)]

Hi @deguy42

Read mail body.
Use Regex pattern to extract the required information.
Save it in a data table.
Read the excel file in a data table
Iterate through the rows of data data table and add the extracted information in the empty row.

Hope it works.

If possible please share the files.

[Chetan_Dhawan]

Hello,

1.Read the email vai ‘get Outlook mail message’ Activity you will get all data in List of Mail Message Variable (Suppose List_MailMessages)
2. Use for each to Loop Through the MailMessage (List_MailMessages) Variable and you can each item of Type “System.Net.Mail.MailMessage” in variable (suppose item)
3. Get Body by using “item.Body” and subject by “item.Subject” and enter in appropriate Datatable row by using add data row activity.
4. After task completion dump this data in Excel.

[deguy42 (CG)]

Hello,

Thank you both for your help, I am stuck at the data row activity part, since I assign a variable to item.body and item.subject but the variable cannot be a datarow type if I do this. How should I go about this?

Thanks

[kumar.varun2 (Varun Kumar)]

you have to extract the information first before adding to data table.
use regular expression for that.
let me give you one example-

To extract Name use assign activity and on the left side provide a string variable (name) and on the right side type this-

System.Text.RegularExpressions.Regex.Match(item.Body, "(?<=Name:).*").ToString

print the value of the name variable using log message activity to check if the name is extracted or not.

[deguy42 (CG)]

Thanks for the help! I have assigned variables for the data. Now do I create a dataRow with these, using an array of my variables?
Also, if I want to grab just the reference number in the item.subject, can I just use the same method?

[kumar.varun2 (Varun Kumar)]

Yes, you can extract information from item.subject also using regex. Also, you can use Matches activity in UiPath to configure the regular expression and extract multiple values in one go. Have a look at the activity. Also go through this post -

Regex help tutorial MEGAPOST – Making your first Regex post, Reusable Regex Patterns, Regex Troubleshooting, Sample Workflow and more

THE PURPOSE OF THIS POST IS TO HELP NEW REGEX USERS If you need help with Regex be sure to provide in your post, a Sample, the Output and the Pattern (as much as you know). CONTENTS: Section : I need a Regex Pattern ASAP! - My first Regex Post Section : Regex samples that you can use! Section : I need help applying regex in UiPath Section : Regex Matches and Groups Section : Le…

It is good for learning regex and how to use it. It is advised to watch a video tutorial to have a better understanding.
In order to add the data to the data table, first build data table. and use the add data row activity to add the row one by one.

if you can provide a sample mail text I can help you with a sample workflow

[kumar.varun2 (Varun Kumar)]

@deguy42 have a look at this xaml file

Mail Body to Excel File.xaml (9.6 KB)

[deguy42 (CG)]

Here is the sample text for the email:
Name: Tom Smith
Address:
154 High Street

Fake place

FA16 KAP

Email Address: TomSmith@fake.com

Member of an association: No

DOB: 01/01/1991
Nationality: WHITE BRITISH

Connection to NH: I currently live in NH,I currently work in NH,I have direct family connections/responsibilities in NH

Will the home be main residence: Yes

Location preference/s:
PlaceA, PlaceC, PlaceD, PlaceF

Signed: Tom Smith
Date: 22/06/2021

I followed your xaml file, and this is definitely in the right direction except due to the structure of the email (as the information is on the following line), the address and the location preference/s are blank in Excel. Would it be better to change the structure of the emails, or is there a way to grab these?

Not only that but I’m having an issue with putting the information in the actual Excel document I get an error: Malformed URI is embedded as a hyperlink in the document. When trying to write range. I tested in a different Excel document to see if it worked and it does, but the actual file I need to put it into I can’t. I think this is due to the address and email addresses being in Hyperlinks in the file, is it better to just remove the hyperlinks? or is there a way around this?

Sorry for asking so much questions, thank you for all the help!

Also, thank you for linking me to the Regex forum, this is something I didn’t even know about.

[kumar.varun2 (Varun Kumar)]

There is no need to change the structure of the mail but have to change regex expression accordingly.

(?<=Address\:)[\w\s\S]+(?=Email Address)|(?<=Location preference\/s\:)[\w\S\s]+(?=Signed)

Use the above regex to extract Address and Location Preferences.

[kumar.varun2 (Varun Kumar)]

@deguy42

Not only that but I’m having an issue with putting the information in the actual Excel document I get an error: Malformed URI is embedded as a hyperlink in the document. When trying to write range. I tested in a different Excel document to see if it worked and it does, but the actual file I need to put it into I can’t. I think this is due to the address and email addresses being in Hyperlinks in the file, is it better to just remove the hyperlinks? or is there a way around this?

Try with Excel Scope Activities(read range and write range).
(Not sure about this but give it a try)

[deguy42 (CG)]

Thank you for all the help!