Assistance Needed with Extracting Customer Register Numbers

[hema]

Hi All,

I need some assistance with a task I’m working on. Despite multiple attempts, I am only able to extract a single value using regex(used Regex as “Customer Register Number:(.*)”, and I’m hoping someone can help me resolve this issue.

Here’s the scenario: My goal is to extract all Customer Register Numbers (CRNs) present in each outlook email. Note that each email contains multiple CRNs.I have to extract each CRN and then copy each one into a web application.

Please note the following:
CRNs may contain characters such as “-”, “?”, or “~”.
Sometimes, the CRN appears without having details like message ID, keyword, or arrival time.
For example, an email might look like this:

Message ID: AE1XXXXXXXXXXXXXXXXX
Arrived: 2025-04-11 22:11:05 UTC
Customer Register Number: 20000000-100000-500000
keyword: lh-2866-011

Message ID: AE1XXXXXXXXXXXXXXXXX
Arrived: 2025-04-11 22:11:05 UTC
Customer Register Number: 20000000-100000
keyword: ph-2300-001

Message ID: 7E7XXXXXXXXXXXXXXXXX
Arrived: 2025-04-13 20:10:52 UTC
Customer Register Number: 10395~SPH61-2000
keyword: kh-2800-021

Message ID: CAXXXXXXXXXXXXXXX
Arrived: 2025-04-13 20:10:49 UTC
Customer Register Number: 4502194729501292015ABC
Keywork: zK-2870-033

[V_Roboto_V]

Hi @hema

Your Regex of “Customer Register Number:(.*)”, would not work because you are not considering the space. And you are not using Positive Lookbehind on Customer Register Number

You can use this Regex:

(?<=Customer Register Number:\s).*

Your code would be:

System.Text.RegularExpressions.Regex.Match(input, "(?<=Customer Register Number:\s).*").Value

Or, if you want multiple matches:

listOfMatches = System.Text.RegularExpressions.Regex.Matches(input, "(?<=Customer Register Number:\s).*").ToList()

Here, listOfMatches is of the Data Type List<Match>

Output:

1. Output for Single Match:
   

   

2. Output for Multiple Matches.
   

   

You can access them individually as shown below:

If this solves your query, Do mark it as a Solution.
Happy Automation

[hema]

Hi @V_Roboto_V I am encountering an issue with the workflow not able to add as list . Despite trying various scenarios, I consistently receive a value of (0) in each Outlook email only. I have attached my workflow for your reference.

Could you please provide some suggestions to resolve?
issue.docx (157.4 KB)

[Anil_G (Anil Gorthi)]

@hema

Print the value in log message and check may be it is having some other characters and hence the regex is failing

Cheers

[V_Roboto_V]

Hi @hema

1. Could you check the value of Body variable in the immediate’s Panel? or the currentItem.Body.ToString?

2. Could you Hover over the error and let me know what the error is? (as shown in your docx file)
   

   

This will give me more insight on what the root problem is.

[V_Roboto_V]

Hi @hema

I replicated your flow,

The data Type of CRN needs to be changed. My previous solution was meant to be used in an Assign Activity.

Since, you are using Find matching patterns, the Result Property expects the variable of type IEnumerable(Of Match). Not List(Of Match).

Just change the Data Type:

The Properties of Find Matching Patterns Activity should look like this:

You will get the desired output:
As you can see below.

Accordingly, accessing the values individually will also change (compared to my previous solution). You CANNOT access it as CRN(0).Value, instead you should access it as CRN(0).ToString

This will solve the Error. But to check whether the Regex works or not, I Would still recommend checking the value of the Body variable or currentItem.Body.ToString in the Immediate’s Panel.

If this solves your query, Do mark it as a solution
Happy Automation

[hema]

Hi @V_Roboto_V

Thank you for the response. I checked the value of the Body variable in the immediate panel, and it is correct. There is no issue with this.

However, the problem is that I am only receiving the CRN(0) values three times from three Outlook emails, but not the subsequent CRNs. Attached the same for your reference. Could you please advise on how to resolve this?

and moreover i am not getting match collection(4)?

Output:

[V_Roboto_V]

Hi @hema

You are using Add Data Row only once with CRN(0), Shouldn’t it be enclosed in a For Loop so that you can iterate through the CRN list?

There Could be one of 2 possible issues or Both.

1. Regex Pattern is not dynamic enough: For which, I suggest you do the following:
   Show me the CRN list Values from Immediete’s Panel and also the Body. You have said that Body has no issues, but CRN has no values (extracts only 1), This means that there is a problem with the Regex pattern.
   Just show the value of the body and CRN, So I that i can fix the Regex and provide a Dynamic one. I cannot give a solution blindly; I need to debug step by step from the root cause. If the Body has sensitive data, Mask it.

2. You have some issue in the logic.
   I observed your Doc file again, and noticed that the extraction process is inside a For each Message list. This means you are extracting the CRN for each Body and then adding it to the DataTable, which is Fine. But the issue is with the Add Data Row, you are adding only CRN(0). Instead, you should surround the Add Data Row in a For Each Activity. Reference Screenshot:
   

   

Reference Output:

[hema]

Hi @V_Roboto_V Yes enclosed in a For loop i just missed it. Attaching the body here.
Output1.docx (13.9 KB)

I am not getting CRN list values in immediate panel but somehow i am able to get the output. see below

test.xlsx (71.6 KB)

Given this use case, which Robotic Process Automation (RPA) framework would be most suitable?

Summary:

1. Daily emails received in Outlook containing the details.
2. Extract the CRN(Customer register number)s from all the emails.
3. Place the CRNs into the application for processing.
4. Update the status column in the Excel file(test.xlsx) to “completed.”
5. Send an email to the customer with the updated Excel file.
6. Erase the previous day’s data from the Excel file and start with new data the next day.

[V_Roboto_V]

If it is not visible then it is because of the scope. Probably you are checking for the CRN value when it’s outside the scope.