Microsoft Office 365 - Get Mail Activity Queries to filter returned emails

News Tutorials
, , , , ,
1

Hello UiPathCommunity,

Microsoft announced that, effective October 1, 2022, They will begin to permanently disable Basic Authentication in Exchange Online in all tenants, regardless of usage, with the exception of SMTP Auth. as Basic Authentication is an outdated industry standard.

Its time to use Microsoft implementation of Modern Authentication (OAuth), which is the newer and more secure version of authentication to Microsoft platforms. We can achieve this in UiPath using Microsoft Office 365 activities.

To enable the Microsoft Office 365 activities, your app must be integrated with the Microsoft identity platform and have the correct Microsoft Graph API permissions assigned to it. Please check the below thread and configure the same in your Organization.

https://docs.uipath.com/activities/docs/office365-setup

Here, we need to pass the query for filtering returned emails.

Note: All Microsoft Office 365 activities should be inside Microsoft Office 365 Scope activity.

image
image918×551 18.9 KB

Below are the queries used for filtering the returned emails using Get Mail activity.

1. Get Emails with Subject contains particular text

               "contains(subject, 'subject you are looking for')"

2. Get Emails with Subject Equals to particular text

               "subject eq 'subject you are looking for'"

3. Get Emails with Subject starts with particular text

               "startsWith(subject, 'subject you are looking for')"

4. Get Emails with Subject ends with particular text

               "endsWith(subject, 'subject you are looking for')"

5. Get Emails with Body contains particular text

               "contains(body/content, 'body you are looking for')"

6. Get Emails with Body Equals to particular text

               "Body eq 'body you are looking for'"

7. Get Emails with Body starts with particular text

               "startsWith(body/content, 'body you are looking for')"

8. Get Emails with Body ends with particular text

               "endsWith(body/content, 'body you are looking for')"

9. Get Emails from specific recipients

               "contains(from/emailAddress/address, 'EmailID')"

10. Get Emails if received date is greater than particular date

               "receivedDateTime gt yyyy-MM-dd"

11. Get Emails if received date is less than particular date

               "receivedDateTime lt yyyy-MM-dd"

12. Get Emails if received date is greater than or equal to particular date

               "receivedDateTime ge yyyy-MM-dd"

13. Get Emails if received date is less than or equal to particular date

               "receivedDateTime le yyyy-MM-dd"

14. Get Emails with Subject contains particular text and body contains particular text

               "contains(subject, 'subject you are looking for') and contains(body/content, 'body you are looking for')"

15. Get Emails with Subject contains particular text or body contains particular text

               "contains(subject, 'subject you are looking for') or contains(body/content, 'body you are looking for')"

16. Get Emails with Subject Not equals to particular text

               "subject ne 'subject you are looking for'"

17. Get Emails with Body Not equals to particular text

               "Body ne 'body you are looking for'"

18. Get Emails with Subject contains particular text

               "contains(subject, 'subject you are looking for') and (hasAttachments eq true)"

19. Get Emails if received datetime is greater than particular datetime.

               "receivedDateTime gt yyyy-MM-ddTHH:mm:ssZ"

20. Get Emails if received datetime is less than particular datetime.

               "receivedDateTime lt yyyy-MM-ddTHH:mm:ssZ"

21. Get Emails if received datetime is greater than or equal to particular datetime.

               "receivedDateTime ge yyyy-MM-ddTHH:mm:ssZ"

22. Get Emails if received datetime is less than or equal to particular datetime.

               "receivedDateTime le yyyy-MM-ddTHH:mm:ssZ"
24 Likes
2

Great compilation of Query to get emails…

1 Like
3

Thanks for the feedback bro @prasadsatish

4

Hi ,

Thank you for the compilation.

My subject is stored in a variable “Email_Subject”.

Can you help me how can I filter that using contains?
I want to use it to filter each email using this activity.

Also I used –

“subject eq '”+ Email_Subject +“'”

to filter my emails.But its failing in unattended mode with Get Mail access denied message even though the authentication type is set to ‘Application Id and Secret’ and its working if I keep the filter blank.It is working in attended mode with ‘Interactive token’ as the authentication type!

Can you please help with your insights here?
I want to try with contains now to see if that works.

Please let me know how do I place a variable here -

image
image1102×110 5.45 KB

Regards,
G

5

@gokul1904

Hope you setup Account properly in Microsoft Azure and you have enough credentials to access Office 365.

If yes then set Authentication Type to UsernameAndPassword pass the credentials under Username and Password (Unattended) section.

6

Thank you for this guide.

Explain this to me, how do you find this parameter?

Because I was using the methods of the office365message variable and it didn’t arrive in this combination.

7

@GCaldas

Try to update Microsoft Office 365 package and check it once.

8

Let me try to explain a little better :).

I want to understand how you identified this attribute.
While I can use “contains(subject, ‘test123’)” and it works.

So, I can get the ‘from’ with mailMessage(0).from .
I was using “contains(from, 'test123)” and of course I got an error.

Your solution works.

1 Like
9

Hey @lakshman

Great post and thanks for sharing.

An additional mail filter example you can use for checking attachments also:
contains(subject,‘subject you are looking for’) and (hasAttachments eq true)

Cheers

Steve

2 Likes
10

Thanks for the feedback @Steven_McKeering.

I will add your suggestion too. Thank you once again.

1 Like
11

Hello,

Can you please type the syntax to retrieve the message by message-id or any other unique identifier?

BR,
Ahmed

2 Likes
12

I would like to add to this that there are both sentDateTime and receivedDateTime properties that can be used in the filters.

Also, you cannot use “equals” for those values.

For example, this won’t work:

sentDateTime eq 2023-03-28

I believe this is because 2023-03-28 is only the date portion, which technically represents 2023-03-28T00:00 (ie 0 hours 0 minutes 3/28/2023). So unless the email was sent at EXACTLY 3/28/2023 00:00am it won’t “eq 2023-03-28”

The way to do this is to combine gt and lt:

sentDateTime gt 2023-03-28 and sentDateTime lt 2023-03-29

This will get emails that were sent any time on 3/28/2023

2 Likes
13

Hi Ahmed, did you find the solution to this?

14

Hi,

I see that there are variants on how to query specific dates, but what is the datetime format in case I want to query the emails on a date after a specific time?
For example, I want to check for emails that have come in on the 26th of April this year, but specifically those, that have been received after 4PM.

What would be the format then? Or is it just not possible, hence why that option was not listed?

15

@kristine.kazaka

Try below expression.

                 "receivedDateTime ge 2023-04-26T16:00:00Z"
1 Like
16

Yup, thank you!
For other’s, who are also not using datetime values as often - it’s the universal timezone used there, so datetime value need to be converted to it before putting in the expression :slight_smile:

1 Like
17

Hi, how would I filter by specific subject and received today?

Thanks

18

The answer is already in this post and its replies.

2. Get Emails with Subject Equals to particular text

               "subject eq 'subject you are looking for'"

…and…

1 Like
19

To add another to this thread. To filter emails based on category:

category/any(a:a eq ‘Some Category’)

1 Like
20

Very helpful.

I am using the below Query:

“contains(subject, ‘subject you are looking for’) and contains(body/content, ‘body you are looking for’)”

and it works fine but to safe guard it I would like to convert subject and body to lower case I tried below and didnt work:

contains(Tolower(subject), 'subject you are looking for')
contains(subject.ToLower(), 'subject you are looking for')