2

I need help in how to put this in a restrict syntax. the original restrict works:

Set myTasks = Fldr.Items.Restrict("[ReceivedTime]>'" & Format(Date - daysAgo, "DDDDD HH:NN") & "'")

Now i was told that it's possible to put 2 filter in a restrict so i tried:

Set myTasks = Fldr.Items.Restrict("[ReceivedTime]>'" & Format(Date - daysAgo, "DDDDD HH:NN") & "' And Not [Subject] like '%Undeliverable%'")

I tried the code above but i'm getting an error "Condition is not Valid". Basically, condition 1 filters emails based on the date it was received which does work alone. The 2nd condition that i need is to make sure that the loop will ignore emails that has "Undeliverable" in the subject line. Please help on how to put the correct syntax.

0m3r
  • 12,286
  • 15
  • 35
  • 71
wh3resmycar2
  • 191
  • 1
  • 13
  • Without digging too deep deep on this, have you tried it with undeliverable first, then sort once those are eliminated? – Cyril Mar 07 '17 at 13:11
  • hi, actually i haven't. the sharedmailbox is receiving thousands of emails a day so the receivedtime is quite essential to narrow the scope down. i think the issue is with the syntax for And Not which i need assistance with. to be honest it is a struggle for restrict syntax for me. – wh3resmycar2 Mar 07 '17 at 13:23
  • I was just looking at the documentation for `Restrict` and found this: There is no way to perform a "contains" operation. For example, you cannot use Find or Restrict to search for items that have a particular word in the Subject field. Instead, you can use the AdvancedSearch method, or you can loop through all of the items in the folder and use the InStr function to perform a search within a field. You can use the Restrict method to search for items that begin within a certain range of characters. https://msdn.microsoft.com/en-us/library/office/ff869597.aspx – Darren Bartrup-Cook Mar 07 '17 at 13:29
  • ok so how do i throw a string value contains to an InStr? if (InStr(1, olmail.subject, "undeliverable", vbtextcompare) > 0 then? – wh3resmycar2 Mar 07 '17 at 13:49
  • After that last comment I then found this on SO: http://stackoverflow.com/questions/21549938/vba-search-in-outlook. Change `'%sketch%'` to `'%Undeliverable%'` and it worked.... added the date condition in and I got `Condition is not valid`. Go figure. – Darren Bartrup-Cook Mar 07 '17 at 13:51
  • You could check the class of each item in your `myTasks` collection with just the date filter applied - an undeliverable will be `olReport` (late binding - `46`), an email will be `olMail` (`43`), a task request is `olTaskRequest` (`49`) - a full list is here: https://msdn.microsoft.com/en-us/library/bb208118(v=office.12).aspx – Darren Bartrup-Cook Mar 07 '17 at 14:10
  • Does the Subject has only `Undeliverable` or there is more? can you show example – 0m3r Mar 08 '17 at 08:22
  • anybody who can help with making this statement into a NOT? strfilter = "@SQL=" & Chr(34) & "urn:schemas:httpmail:subject" & Chr(34) & " like '%undeliverable%'" ... The filter should be like this: if the Subject contains the world undeliverable, then it will be excluded. thanks – wh3resmycar2 Mar 09 '17 at 13:26

1 Answers1

0

To answer the question you asked, rather than the question you want answered.

Two restricts can be applied one after the other but both have to work. In this case it appears the Not Subject filter may not be possible.

You can first filter the items in the folder, if it is possible, with a Not version of this filter "@SQL=" & Chr(34) & "urn:schemas:httpmail:subject" & Chr(34) & "like '%Undeliverable%'" as described in the comments. https://stackoverflow.com/a/27350173/1571407

Set myTasks = Fldr.Items.Restrict(Not .... '%Undeliverable%'")

Second, filter Mytasks, not Fldr:

Set myTasks = myTasks.Restrict("[ReceivedTime]>'" & Format(Date - daysAgo, "DDDDD HH:NN") & "'")
Community
  • 1
  • 1
niton
  • 8,771
  • 21
  • 32
  • 52
  • ok as i've mentioned the SQL filter syntax is a challenge. so can you show me the Not version of that filter? or do i just change Like to Not as the operator? – wh3resmycar2 Mar 08 '17 at 05:57
  • i tried changing the Like to Not and got a parse error. The not version will most likely solve this. please advise. thanks. – wh3resmycar2 Mar 08 '17 at 06:16
  • while waiting for your response. what i did was setup 2 mytasks one to search only for Undeliverable and tag them as "undeliverable" if it contains undeliverable and mytasks2 after the first loop without the undeliverable condition. looks like it works. but could be slow as i ended up doing 2 loops. if we can convert the strfilter to a NOT statement that will extremely help. – wh3resmycar2 Mar 08 '17 at 06:22
  • anyone who can help in transforming this statement to NOT? strfilter = "@SQL=" & Chr(34) & "urn:schemas:httpmail:subject" & Chr(34) & " like '%undeliverable%'" – wh3resmycar2 Mar 09 '17 at 13:25