Excel VBA @SQL String filter

[akika]

I'm struggling on making this statement works
I need to filter out sender email subject like "undeliverable" and "Automatic reply" and email addresses "no-replies@notif.com", " times@mail.com"
when running code it not returning anything.

eFilter = "@SQL= (NOT urn:schemas:httpmail:fromemail Like '%no-replies%'" & _
Not (urn:schemas:httpmail:subject" & _
                   " Like '%undeliverable%'  Or " & _
                         "urn:schemas:httpmail:subject" & _
                   " Like '%Automatic reply%')" and
Not (urn:schemas:httpmail:fromemail" & _
                   " Like '%no-replies%'  Or " & _
                         "urn:schemas:httpmail:fromemail" & _
                   " Like '%times%')" & _
          " And urn:schemas:httpmail:datereceived = " & checkDate & _
          " And urn:schemas:httpmail:datereceived <= " & tdyDate & ")"

 

[BobBridges]

Well, just at a glance I see a few minor problems here. I take you're trying to assign a long character string to a variable named eFilter, but two of your strings are missing the starting quote. And I think you're missing an "or" between the first and second conditions. Come to think of it, I'll bet you need the first "or" (the one between the two Subject checks) to be an "and". I won't look further; I recommend you type out the whole thing again, dividing your lines more sensibly, like this:

eFilter = "@SQL= " _
  & "(Not urn:schemas:httpmail:fromemail Like '%no-replies%'" _
  & " Not (urn:schemas:httpmail:subject Like '%undeliverable%' _
  & " Or urn:schemas:httpmail:subject Like '%Automatic reply%')" _
  & " And Not (urn:schemas:httpmail:fromemail Like '%no-replies%' _
  & " Or urn:schemas:httpmail:fromemail Like '%times%')" _
  & " And urn:schemas:httpmail:datereceived = " & checkDate _
  & " And urn:schemas:httpmail:datereceived <= " & tdyDate & ")"

Then indent your lines here and there to be sure it's clear to your eyes which clauses are dependent on others, check your parens for the same thing, and think about your Ands and Ors. That should help clarify it in your mind.

 

[akika]

It working
Thxs for tipsss.
in the filter condition, can i point to an excel sheet cell to get list of values for the subject?

ps i never did IT dont know much about the basis , trying to learn to ease my business work.
Any link on tutorials for dummies will be appreciated.

 

[BobBridges]

You can store values in a worksheet so you can modify the filter value more easily, but it gets a little complicated. Normally I would ambitiously try to allow a variable number of conditions, one in each row of the worksheet, and assemble them all in the eFilter string, but if you do that you can't just have "subject" and "%Automatic reply%"; you have to think about the ANDs, ORs and parentheses. So let's start with something simple like just checkDate: Put the checkDate value in A2 of a worksheet named "Settings". Fetch it from the worksheet and use it in the eFilter statement like this:

Set ows = ThisWorkbook.Worksheets("Settings") 'get the worksheet
Set ocs = ows.Cells 'shortcut to the worksheet cells
checkDate = ocs(1, 2).Value 'fetch the value in A2 into checkdate
eFilter = "@SQL= " _
  & "(Not urn:schemas:httpmail:fromemail Like '%no-replies%'" _
  & " Not (urn:schemas:httpmail:subject Like '%undeliverable%' _
  & " Or urn:schemas:httpmail:subject Like '%Automatic reply%')" _
  & " And Not (urn:schemas:httpmail:fromemail Like '%no-replies%' _
  & " Or urn:schemas:httpmail:fromemail Like '%times%')" _
  & " And urn:schemas:httpmail:datereceived = " & checkDate _
  & " And urn:schemas:httpmail:datereceived <= " & tdyDate & ")"