Checking content against Excel info


#1

Hi all

I want to check the contents of an email against a long list of words in an Excel sheet. Any idea how I’d go about this?

Thanks!


#2

Use Get Outlook Email activity to get the body of the email into a string.

Keep Excel words in an Array/datatable and iterate using for each

For Each word in Excel Array

emailSubject.Contains(word)
//do your logic.


#3

Could you elaborate on the checks purpose?
This seems pretty interesting, but the solution should vary depending on if you’re just looking for 1 match, all matches, how long is the list vs average length of an email, do the actions vary depending on which word was found etc.


#4

Hi @andrzej.kniola, of course!

So in Excel, I’ll have a list of names to search through. The purpose of the checks is to determine who it is from, which means it’ll just be looking for one match.

The length of the list will be a couple of hundred, so I thought Excel would be an easier/quicker way of looking. If a better way of doing this springs to mind, please let me know! The action won’t vary depending on the result, it’ll be the same no matter what word was found.

Thanks @vvaidya but the emails won’t be shown on Outlook but a case management system, so I’m using a screen scrape. I should’ve specified, apologies.

In my amateur-ness, I’ve come up with this:

However, you can see the error I’m getting. I haven’t had to do the loop process before, or for each, so I’m clueless.

Thanks!


#5

That’s not an error message, the right syntax to read the row value is either row(index).Tostring or row(“columnname”).ToString.
your syntax can be used to know the datatype of the row.


#6

Ahh sorry I didn’t mean error message, I meant what’s coming up instead of the text in the row.

I’ve got this now?


#7

If your word list is in first column pls try row(0).Tostring instead of row(index).Tostring.


#8

That work, brilliant! Thank you so much.

Another quick question, how would I get it to stop searching once it’s found the word?


#9

User break activity at the end of your Search pattern Found Logic in For Each loop


#10

Amazing, thank you :slight_smile:


#11

One last question, sorry!

Say I’ve reached the end of the list of words in Excel, how would I get it to then look at another range in Excel (either the same document or a different one) and check through a different list of words?


#12

Same Excel - If you already have data in datatable, you can use row(1) (if the 2nd list is in 2nd column)

Different Excel - Use Read Range and load data into a DataTable (new) and do the same.

I would use a flow chart here looping back to excel application scope with new file path based on result.


#13

Thank you!