Topic: search for a given input from the email body and assign the result to string variables

outlook

#1

What’s I want this automation to accomplish?
I am trying to build an automation that will loop through all unread emails in a shared mailbox and search each email body for two user inputs (Project Name & Organization) and assign the user inputs to string variables that can be typed into an online form.
What I have done so far?
The workflow I have developed so far for this automation problem can read the two sample emails and loop through each item and print the results. As you can see in my workflow, I have also tried to add each item to a datatable using invoke and add row activities. However, it is not doing what I trying to accomplish. I have read over dozen forum posts here and reviewed some UiPath academy videos but without success at finding a solution.
What do I need help with?
I need help to search within each item from an email to find “Project Name:” and return the user input for the name of the project and store it into a string variable. I also need to find “Organization:” and return the user input for the organization and store it into a string variable. See the text below the body of one of the sample email
Project Name:
Exterior Retrofit
Organization:
Rogers LLC

Finally, I would like to provide a condition to read only emails that contains in their subjects ‘RE: ISM PW Reset’
Thank you so much for your time and your help!


#2

PasswordReset_v3.xaml (18.1 KB)
Output of the workflow I have so far.xlsx (9.9 KB)


#3

Hello there.

I can provide a quick response.

So I’m assuming you have an Enumerable of the mailmessages in which you can run through a For each or (my preference) use vb.net to filter it down.

You can either use a Regex pattern or string manipulation (.Split()) to extract the Project and Org.

It will end up looking something like this:

projectKey = "Project Name:"
orgKey = "Organization:"
subjectKey = "ISM PW Reset"
projectPattern = "(?<="+projectKey+"(.*)"+System.Environment.NewLine+")(.*)"
orgPattern = "(?<="+orgKey+"(.*)"+System.Environment.NewLine+")(.*)"

messagesFiltered = messages.Where(Function(m) m.Body.ToString.Contains( subjectKey ) ).ToArray

For each msg In messagesFiltered
    project = System.Text.RegularExpressions.Regex.Match(msg.Body.ToString,projectPattern).Value.Trim
    org = System.Text.RegularExpressions.Regex.Match(msg.Body.ToString,orgPattern).Value.Trim
   <process other actions>

EDIT: made some corrections

I hope this is helpful.

Regards.


#4

Hi @ClaytonM
Thank you so much for your quick help! I have incorporated your code in my workflow. I am having some activity validation errors and I’d appreciate it if you could take a look and tell me which data types I should change to fix those errors. Here I am attaching the workflow that I have changed to your recommended solution.
Thank you again so much!!PasswordReset_v4.xaml (12.7 KB)


#5

Hi.

The first validation problem is that messagesFiltered is not the correct type. Make it an Array of type Net.MailMessage

The second one is because all your variables are GenericValues. Change them all to String for variable type. Either that or add .ToString whenever you use the variables, but not my preference.

so solutions are change messagesFiltered to type Array[System.Net.MailMessage] and change all Generics to type String.

Regards.


#6

Oh one more tip… you can assign the values for all those variables in the “Default Value” field when you create the variable. The benefit of assigning the value there is that you can change it in the future easily and don’t clutter up your code with many activities; do this for variables that have a starting or global value.


#7

Hi @ClaytonM
First of all, I am very thankful for your precious time and knowledge to help me with this issue. I think we are very close to have this workflow work! I did the changes that you suggested and there is a new error in the assign activities for project and org variables using the regex. I also appreciate the tip to assign values in the default value field to make it easier to change in the future and avoid cluttering up the workflow. I am attaching the updated workflow so that you can take a look to better troubleshoot it. Please feel free to change the workflow and test it with your solution:):smiley:PasswordReset_v4.xaml (12.2 KB)


#8

Hi.

So most of it looks right, but I saw you had 2 For eaches, so essentially the inside one was looping through a different type of variable, thus the late binding error occurs.
See below:
image

All you want to do is loop through each mailmessage, then use msg.Body when you assign the Project and Org (which is already in place).
So, it will simply just be like the below image instead:
image

I also noticed the messageFiltered variable type wasn’t correct. See below to see what it should look like:

Here is the edited workflow for reference: PasswordReset_v4 (1).xaml (10.8 KB)

Regards.


#9

Good morning @ClaytonM !
Thank you again so much for guiding me step by step to make this automation work and for your very helpful suggestion to improve my future automation workflows. I think we are very close for this workflow to work perfectly! I am really excited about that :slight_smile: The only thing not working as it should is getting the project name and organization. I am attaching a screen shot of the output for the first email and marked the project name and organization with a red box. The last two write line activities to append projectkey to the project and orgkey to the org did not work as it should. One last thought, After the project name and organization in each email were successfully returned, I would like to know your opinion on whether I should store the project and org results to .csv file with three columns: iteration index, organization, and project in order to loop through them later to fill out online form for each user. If yes, I’d like to learn how to accomplish that!
Best regards!!


#10

Hello, @hmheimid,

If the Project name and Organization information is always one after the other & each on it’s own row, isn’t it simpler to just write the email in a txt file using “[Write text file activity](https://activities.uipath.com/docs/write-text-file)”, then file.ReadAllLines("txtfile") and find the row that contains “Project name”. After that, the Project name = row+1 and Organisation will be row+3.
Check this out: get_Project_name_and_Organisation.zip (3.2 KB)


#11

Hi.

I noticed in your screenshot that you have multiple newlines between each line. So you will need to implement that in the regex pattern.

I decided it would be better to replace the newlines with vblf when performing the regex extraction.
So here is the recommended edits to the pattern and regex lines when you store Project and Org:

projectPattern = “(?<=”+projectKey+"\s*)(.+)"
orgPattern = “(?<=”+orgKey+"\s*)(.+)"

System.Text.RegularExpressions.Regex.Match(String.Join(vblf,msg.Body.ToString.Split({System.Environment.NewLine},System.StringSplitOptions.RemoveEmptyEntries)),projectPattern).Value.Trim
and
System.Text.RegularExpressions.Regex.Match(String.Join(vblf,msg.Body.ToString.Split({System.Environment.NewLine},System.StringSplitOptions.RemoveEmptyEntries)),orgPattern).Value.Trim

Essentially, for the pattern I changed it so “\s*” is any whitespace after the key word and “.+” is any non-newline character of 1 or more characters, which will make it more dynamic I think if the line breaks are inconsistent. And, in the Regex extractions, I just do a .Split by the newline character then rejoin back with vblf carriage return.

I hope this helps resolve it. If it’s still extracting an empty string, if you can upload the Body as a .txt file or something I could check the patterns with that.

Regards.


#12

To reply with my opinion on this, I prefer to not create external files to store temp data if I can avoid it. So normally, I would store the messages in a MailMessages variable, then filter it to what I want to process (like you are currently), then use a For each on those messages to process them (like you are currently). So you could just place the code that inputs that information into a form inside the For each after you store the Project and Org.

On the other hand, it can be beneficial to store a details log of all the actions performed throughout the process. Basically, to do this you would perform your process like normal (For each msg, and input Project and Org into form like you would), then you would store the details into a data table variable.

To create a data table variable from scratch (meaning you do not have an Excel file already), you can use the Build Data Table activity and create String Columns for like “Project Name”, “Organization”, and “Status”, or however you want to set it up. Then, use Add Data Row inside the For each to add the item to the data table, and use the ArrayRow parameter, for example you can add 3 columns like {Project,Org,"COMPLETED "+Now.ToString}. Finally, you can use Write CSV or Write Range to bring this table into the file; you can place this activity inside the For each to update file after each item or after you are done to update entire file at once.

To go further you can use Read CSV or Read Range to store existing details and use Merge or Append Table. However, you must think about the potential for duplicates when you start joining existing data; you can remove the duplicates in a few ways but I recommend looking this up (I think .Distinct or .ToTable() are possible solutions… there will be a learning curve there.

I hope this answered your question and did not confuse you. :stuck_out_tongue:

Regards.


#13

Hello @ClaytonM !!
Thank you again so much for your valuable knowledge which helped me tremendously. You have saved me a lot of time and I don’t how I would have come this close to a final solution without you!! The project name is now successfully retrieved so we should celebrate that!! Org is still returning blank. I am going to upload the most recent workflow after I included your edited codes. I am optimistic we can return the org name successfully!
Best regards,
HousseinePasswordReset_v4.xaml (12.2 KB)


#14

Yes, I think I understand your recommendation here and that I what I would do!
Best regards,
Housseine,


#15

Hi @crimaru !
Thank you for your suggestion and taking your time to build a sample workflow.I tested it and it works. I also agree that it is simple. The only problem I have encountered is that it currently returns only the project name and organization for one user. Let’s say I have 5 user inputs, I will need to have the project name and organization for each user (the text will be really long if I have 20 emails that trying to extract project name and organization for each one).


#16

I think you uploaded an old version of your workflow. Can you check that and reupload?


#17

I am sorry for making that mistake. I went back to the previous version and updated it, but I am now getting an error about late binding in the project and org assign activities. I swear that the workflow ran after I updated it with your most updated codes, but it is now showing this late binding error. This is a new issue in addition to returning a blank value for org name.PasswordReset_v4.xaml (12.2 KB)


#18

Hey there. You got 2 For each loops again. Refer back to my previous post that points that out. :stuck_out_tongue:


#19

Hi @ClaytonM !
Yes, you are right. I deleted the extra for each and there is no validation errors anymore. However, it is still not returning the organization:thinking: I am really sorry for taking a lot of your time on this issue. We are almost there to have it work! Here is the workflow attached.
Best regards,PasswordReset_v4.xaml (10.6 KB)


#20

Can you get a text file of the message body by doing msg.Body inside a Write Text File, then upload that here? I can’t really tell what the issue is without seeing the source text. It most likely just needs an adjustment to the pattern.

Thanks.