How can I find E-Mail-Adresses from each single cell of an Excel file?

Help Studio
, ,
1

Hi all,
I’m a newbie for UiPath. I got an Excel worksheet like this( in the screen shot). I extracted all of the data from an website. That’s why all of the informations of a firm were written into a single cell. I’m supposed to detect the E-Mail-Adresses from the column B (if there is) and write them identically in colunm C and then put them into Outlook. How can I achieve this?
Thanks!
Best Regards

Company Information
Company Information1203×737 14.7 KB

2

@111531 First use Read Range activity to read the Excel data then
use For Each Row activity and inside that use Matches activity

2 Likes
3

Hi @111531
Try this way

  1. Use assign activitiy to intialise int32 varaible
    counter=1

  2. Read the datatable and store in dt1 using read range( I think there is no headers for the excel data as per the figure. So please untick the Add headers option in read range)

use for each row to loop through dt1

inside the for each row do the following:
1. Assign activity:

                   email = System.Text.RegularExpression.Regex.Match(row(1).ToString,"(?<=Email:).*").Value

  1. Now use write cell activitiy with details as below

                   filepath :
         
        Sheet Name: 
         
         Cell : "C"+counter.ToString
           
          Value : email

  2. Now use Assign activitiy again to increment counter by one

              counter =counter+1

  3. Now u can use outlook mail activivty to send email to that email by using email

  4. Finally outside the loop, use write range activivty to write the updated datatable to excel

Regards

Nived N
Happy Automation

2 Likes
4

Thank you very much for your quick answer, I’ll try this!

5

Hi
I’ll try this way. Thank you very much for your quick help!

1 Like
6

In the first Assign activity I got this report: “Regular Expression” is not a member of “Text”. Should I change something?

7

Hi @111531

Try this

email = System.Text.RegularExpressions.Regex.Match(row(1).ToString,“(?<=Email:).*”).Value

1 Like
8

Hi @NIVED_NAMBIAR Now I got another report: cannot assign for type “System.Boolean” to type “System.String” in Assign Activity “Assign”. I definied the Variabel “Email” as string, so I dont really understand why

9

Hi it was my careless mistake, now there is no report.

10

Hi @NIVED_NAMBIAR Now in the second Assign Activity I got this report, how can I solve this problem?

Here is the protokoll:20.10.4-beta.1+Branch.release-v20.10.4.Sha.37d549846d5ef51c36ecc7e3bab8ad2ae1436ee9

Source: Assign

Message: Exception has been thrown by the target of an invocation.

Exception Type: System.Reflection.TargetInvocationException

RemoteException wrapping System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> RemoteException wrapping System.IndexOutOfRangeException: Cannot find column 2.
at System.Data.DataColumnCollection.get_Item(Int32 index)
at System.Data.DataRow.set_Item(Int32 columnIndex, Object value)
— End of inner exception stack trace —
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object parameters, Object arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object parameters, CultureInfo culture)
at System.Activities.ExpressionUtilities.IndexerLocationFactory1.IndexerLocation.set_Value(T value) at System.Activities.Location1.ReferenceLocation.set_Value(T value)
at System.Activities.ActivityContext.SetValueCore[T](LocationReference locationReference, T value)
at System.Activities.ActivityContext.SetValue[T](LocationReference locationReference, T value)
at System.Activities.Argument.Set(ActivityContext context, Object value)
at System.Activities.Statements.Assign.Execute(CodeActivityContext context)
at System.Activities.CodeActivity.InternalExecute(ActivityInstance instance, ActivityExecutor executor, BookmarkManager bookmarkManager)
at System.Activities.ActivityInstance.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)
at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)
Unbenannt

11

Hi @111531

Check the above response I told

I had updated my query

Regards

Nived N :robot:
Happy Automation :smiling_face::smiling_face::smiling_face:

1 Like
12

Hi @NIVED_NAMBIAR I’m really appreciated for your help. I have tried the new solution. The programm runs pretty well but it didn’t work out. Some of the cells don’t contain E-Mail-Adress, should I acctully use a “If-Loop”?

Wolfang
Thank you very much

13

U can use email.ToString<>“” in if condition

It implies that email is not empty

So u can make the then and else section Accordingly

1 Like
14

@NIVED_NAMBIAR
I got this Report and I have a workflow like this. The if Loop is in the for each row loopUnbenannt

Workflow
Workflow954×704 42.8 KB

15

Hi use this if condition

System.Text.RegularExpression.Regex.Match(row(1).ToString,“(?<=Email:).*”).Value.ToString<>“”

@111531

16

Hi @NIVED_NAMBIAR with this condition I got the same report like before " “Regular Expression” is not a member of “Text”“. I put in the if condition “EachRow.ToString.contains(”@”). The pragramm ran till end but I didn’t get the Email Adresses extracted. Does that mean the Assign Activity doen’t work well?

17

Hi @111531

Use this if Condition

System.Text.RegularExpressions.Regex.Match(row(1).ToString,“(?<=Email:).*”).Value.ToString<>“”

18

Hi @111531

You can also try to use a regular expression that matches the emails that can be inside your text. Here is an example:

image
image1450×694 30.2 KB

There are too many regular expressions on internet to match email addresses so you can check the one that suits better your cases. Here is a webpage with an example.

Match an email address - Regex Tester/Debugger

Regards,

Andres