Get Outlook Mail Messages : Filter To Capture Last X Hours Of Emails

[Ashish6541 (Ashish Kumar Giri)]

Hello All,

I have queries for Filter Option in Get Outlook Mail Messages. I have gone through all of topic which is covered on forum, but non of them seems to be valid in my case.

I need to filter email for last 5 Hours Email from now. I have used all the possible solution in forum topic but does not seem to be working fine.

I have tried below Filters. None of them giving runtime and compile time error but, output count is always coming as 0. I am here to get some solution to it.

1 : “[ReceivedTime] > '”+now.AddHours(-5).toString(“D”)+”'" – working but not giving last 5 hours email
2: “[ReceivedTime] >'”+Now.AddHours(-5).ToString()+“'” –Not Working - Email Count = 0
3: “[ReceivedTime] >'”&Now.AddHours(-5).ToString()&“'” – Not Working - Email Count = 0

Along with this - one more question like is not accepted by [Subject] Filter ?
“[Subject] like ‘’%Test%” – Not working

Q: How to get received time of email, after using for each Item. There is no property to get received date. (like item.?)

S O L U T I O N
Please check the last comment of mine for all possible Solutions. I have put every solution at one place. Thank You!

@vvaidya @bobpeers @pelli

[jack.chan]

if using foreach item, try
item.Headers("Date")

There are also other properties in headers if you are interested

MailMessage.Headers Property (System.Net.Mail)

[Ashish6541 (Ashish Kumar Giri)]

@jack.chan Thanks Buddy. This is really helpful. Will you be able to help me with rest of queries?

[jack.chan]

what queries?

[Ashish6541 (Ashish Kumar Giri)]

@Ashish6541

I need to filter email for l ast 5 Hours Email from now . I have used all the possible solution in forum topic but does not seem to be working fine.

I have tried below Filters. None of them giving runtime and compile time error but, output count is always coming as 0 . I am here to get some solution to it.

1 : “[ReceivedTime] > '”+now.AddHours(-5).toString(“D”)+”’" – working but not giving last 5 hours email
2: “[ReceivedTime] >’”+Now.AddHours(-5).ToString()+“’” –Not Working - Email Count = 0
3: “[ReceivedTime] >’”&Now.AddHours(-5).ToString()&“’” – Not Working - Email Count = 0

Along with this - one more question like is not accepted by [Subject] Filter ?
“[Subject] like ‘’%Test%” – Not working

@jack.chan

[jack.chan]

please give me a minute, i reply as soon as possible

[jack.chan]

you can use this to filter by subject + time

"@SQL=urn:schemas:httpmail:subject Like '%" + subjectString + "%' AND urn:schemas:httpmail:datereceived > '" + Now.AddHours(-24).ToString("dd/MM/yyyy hh:mm:ss")+ "'"

1. assign the above text to a variable e.g. filterString
2. use filterstring in the filter property of “Get outlook messages”
   
   

Note: subjectString is just a string variable e.g. e.g if subjectString = “UIPATH” it will filter by all subjects that contain “UIPATH”
Example:

if subjectString = “UIPATH”, then filterString will look like
@SQL=urn:schemas:httpmail:subject Like '%UIPATH%' AND urn:schemas:httpmail:datereceived > '23/12/2020 01:43:00'

If this doesnt work then it might be something to do with your date format. Mine is dd/MM/yyyy but it might be MM/dd/yyyy for you…

[Ashish6541 (Ashish Kumar Giri)]

Thanks for the reply … Is it working for you? For me email count = 0. Please find the screenshot.
Date Format: MM/dd/yyyy HH:mm:ss
@jack.chan

This approach is not working as well. Have a look.

[jack.chan]

yes its working for me. Did you try change the date format?

[Ashish6541 (Ashish Kumar Giri)]

You want me to change the Outlook Datetime format ? I am not sure which date format you want me to change?

This is here in the code,

“@SQL=urn:schemas:httpmail:datereceived > '” + Now.AddHours(-5).ToString(“MM/dd/yyyy HH:mm:ss”)+ “'”

[jack.chan]

i mean change the format from MM/dd/yyyy HH:mm:ss to dd/MM/yyyy HH:mm:ss

If it still doesnt work can you remove the filter and print the date and subject to see the date format?

[Ashish6541 (Ashish Kumar Giri)]

Date is in format of MM/dd/yyyy hh:mm:ss

[jack.chan]

try remove the filter from “Get Outlook Messages”, after then afterwards, assign this to “listOfEmails”

listOfEmails.Where(function(mailItem) DateTime.ParseExact(mailItem.Headers("Date"), "MM/dd/yyyy HH:mm:ss",Nothing) > Now.AddHours(-5)).ToList

[Ashish6541 (Ashish Kumar Giri)]

Hello @jack.chan,

Let me share some observation for both working and non working part,

Working
1: Use your method mentioned below,

listOfEmails.Where(function(mailItem) DateTime.ParseExact(mailItem.Headers(“Date”), “MM/dd/yyyy HH:mm:ss”,Nothing) > Now.AddHours(-5)).ToList

Benefits: Faster
Query 1: What is this called? Is it kind of SQL queries?

2: I have created below flow,

It will iterate every elements. So it is slow.

Not working:
1: Use search in filter

• Why it is not working?
• Is this because of time zone? while fetching emails date time is taken from some other timezone?
• I noticed, addHours(-24) working but not actually fetching last 24 hours result.

[jack.chan]

@Ashish6541

Working
1: Use your method mentioned below,

listOfEmails.Where(function(mailItem) DateTime.ParseExact(mailItem.Headers(“Date”), “MM/dd/yyyy HH:mm:ss”,Nothing) > Now.AddHours(-5)).ToList

Benefits: Faster
Query 1: What is this called? Is it kind of SQL queries?

its LINQ syntax, it works because listOfEmails is a list, and linq works with lists / datatables etc…

As for using search in filter, im not sure why its not working for you. Probably due to outlook timezone difference as you said. Maybe you have to convert the timezone of the date in your filter first.

But if my above method is working i think you can stick to that for now

[Ashish6541 (Ashish Kumar Giri)]

Sure Thanks @jack.chan.

@loginerror @vvaidya @Palaniyappan
Hello All,
As per my requirement I need to capture last 5 hours emails. This can be achieved by Linq and Iterator after capturing all the emails. But I want to use Filter Option to it. I tried following ways in Filter and none of them working. Could you please suggest what could be the reason?

“[ReceivedTime] > '”+now.AddHours(-5).toString(“D”)+”’"

“@SQL=urn:schemas:httpmail:subject Like '%” + subjectString + “%’ AND urn:schemas:httpmail:datereceived > '” + Now.AddHours(-24).ToString(“dd/MM/yyyy hh:mm:ss”)+ “'”

[jack.chan]

i think outlook dates are always in UTC time, try to change your date in the filter to UTC time as well…

just change Now.AddHours… to Now.ToUniversalTime.AddHours… inside
“@SQL=urn:schemas:httpmail:subject Like '%” + subjectString + “%’ AND urn:schemas:httpmail:datereceived > '” + Now.AddHours(-24).ToString(“dd/MM/yyyy hh:mm:ss”)+ “’”

this would also explain why addHours(-24) is working but not actually fetching last 24 hours result.

[Ashish6541 (Ashish Kumar Giri)]

Awesome @jack.chan.

Looks Like It is working as expected after converting this to Universal time. Our assumption of issue with time zone was right. Something New Learning.

Thanks @jack.chan for all your efforts/help to get this worked. Because of the issue we have now three different way to filter.

[jack.chan]

No problem, glad its solved now. Please mark my reply as solution

[Ashish6541 (Ashish Kumar Giri)]

Hello All,

We have solutions for this with the help of @jack.chan . We have three way to do this.
Putting all the solution together for better reach

Way 01 : (Linq)

• Fetch all the email without filter option set in list
• Assign : RHS :

listOfEmails.Where(function(mailItem) DateTime.ParseExact(mailItem.Headers(“Date”), “MM/dd/yyyy HH:mm:ss”,Nothing) > Now.AddHours(-5)).ToList

Way 02 : (For Each Iterator)

• Fetch all the email without filter option set in list
• Use for each to iterate through all email
• Check below condition inside IF

DateTime.Parse(item.Headers(“Date”)) > DateTime.Now.AddHours(-5)

Way 03 : (Use Filter)

• In filter convert the time to UTC. And add the below code to Filter Option.

“@SQL=urn:schemas:httpmail:datereceived > '” + Now.ToUniversalTime.AddHours(-12).ToString(“dd/MM/yyyy hh:mm:ss”)+ “'”