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:
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.
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.
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.
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 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?
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 -
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
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.