Outlook Filter Item Query for subject and received time

Help
1

Dear All,

i have scenario wherein i have to fetch mail on basis of received time and subject and mark it as read only to that particular mail item strictly. for this purpose i have written down a outlook filter query
which is as follow

“@SQL=”+“(”+chr(34)+“urn:schemas:httpmail:subject”“like '%”+Subject+“%')AND(”+chr(34)+“urn:schemas:httpmail:datereceived”+ chr(34)+" >=‘10-12-2018’)"

this one will work when i pass only date but the moment i added the time to it it will not work

“@SQL=”+“(”+chr(34)+“urn:schemas:httpmail:subject”“like '%”+Subject+“%')AND(”+chr(34)+“urn:schemas:httpmail:datereceived”+ chr(34)+" >=‘10-12-2018 12:40’)"

please help me out to correct it

2

Hi. Have you tried using 12:40PM rather than 12:40?

Here is some reference on date comparison:

3

ClaytonM thanks for showing the concern but it is not related to format.

i have figure out the issue and issue is quite strange it is related to timezone i have write DASL query as follow

“@SQL=”+“(”+chr(34)+“urn:schemas:httpmail:subject”“like '%”+Subject+“%')AND(”+chr(34)+“urn:schemas:httpmail:datereceived”+ chr(34)+" >=‘11/12/2018 18:05’)"

and it work this time but my local time is 11/12/2018 23:40.
so now i am focusing on issue with different approach if you have any solution on this then that would be very helpful for me.

4

If you are using Current time, then >= will not find any emails, right? since no emails will have been received yet that is after current time?

Or is there an error message or something? Like what is it doing that is a problem?

Also, if you want to put in Current time, just use concatenation, like:

>='"+Now.ToString("dd/MM/yyyy hh:mm")+"')"

Sorry if I misunderstand.

5

issue is in filter it compares mail time in header with time with PST timezone not with IST time zone so if i do -5.30 hrs minus then it is working not looking for how to use IST time.

6

My thinking was this:
Let’s say you receive a mail that has time in header as “12:00” in PST
Then, you try to filter by a time that is in IST, so it looks for emails after “15:30”.

If you filter it like this, you will never find emails because IST is several hours after the mail time.

So, what I am suggesting is that you convert the time you are comparing with to PST or whatever time that the mails will be going by.

For example, if your current time is stored as Now, you can change it like this:
Now.ToUniversalTime.AddHours(-8).ToString("dd/MM/yyyy HH:mm"
And, concatenate that inside your filter so it’s in the right format.

Now, there is also another way to filter your mails if you choose to take another approach. You can just bring in all mails within a certain number, then use LINQ in vb.net. However, this can sometimes be slower since you need to bring in more emails. This would use the date in the header and a condition to check it with your current time (again you would need to make sure the dates are in the same timezone by converting both sides to UTC). Would look something like this basically, mailmsg.Where(Function(m) CDate(m.Headers(0)).ToUniversalTime >= Now.ToUniversalTime).ToArray

Regards.

1 Like
7

correct i am doing same that subtracting IST -5.30 hrs from mail time and finding the mail.
as u suggested ill try doing this with help of linq query just wanted to know can i check subject line also in that ?

8

@H-Rishi Yeah, just use .Subject I think.

mailmsg.Where(Function(m) m.Subject.Contains(keytext) And CDate(m.Headers(0)).ToUniversalTime >= Now.ToUniversalTime).ToArray
1 Like
9

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.