• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract email from outlook to excel with multiple filters

ssaashish

New Member
I am struggling with this code to get the list of emails from outlook with filters of subject that contains, sender name and between two dates. The code I've used is commonly used but doesn't work for me. I get the error of cannot parse condition but unable to figure out the issue. Posting this after one week of trying various methods. I have attached the SS of error message and references used.

>>> use code - tags <<<
Code:
Sub emailextract()

Dim olapp As Outlook.Application
Dim OutlookNameSpace As Namespace
Dim Folder As MAPIFolder
Dim OutlookMail As Variant
Dim i As Integer
Dim DateToCheck As String
Dim searchString As String
Dim oT As Outlook.Table
Dim oFilterItems As Object
Dim oFilterItem  As Object
Dim oItems As Object

Set olapp = New Outlook.Application
Set OutlookNameSpace = olapp.GetNamespace("MAPI")
Set Folder = OutlookNameSpace.GetDefaultFolder(olFolderInbox)

fdate = Format(Range("From_Date").Value, "MM/dd/yyyy h:mm:ss AM/PM")
tdate = Format(Range("To_Date").Value, "MM/dd/yyyy h:mm:ss AM/PM")
srchSender = Range("Sender").Value
srchsub = Range("Subject").Value

searchString = Range("subject").Value
Debug.Print "searchString = " & searchString

searchString = "@SQL=""urn:schemas:httpmail:subject"" LIKE '%" & srchsub & "%' "
'searchString = "[Subject]=" & Chr(34) & srchsub & Chr(34)
'searchString = "@SQL=(""urn:schemas:httpmail:subject"" like '%" & srchsub & "%')"
    Debug.Print "searchString = " & searchString
searchString = searchString & " AND "
searchString = searchString & "[ReceivedTime] >= '" & Format(fdate, "ddddd h:nn AMPM") & "' AND [ReceivedTime] <= '" & Format(tdate, "ddddd h:nn AMPM") & "'"
searchString = searchString & " AND "
searchString = searchString & "[SenderName] = '" & srchSender & "'"
    Debug.Print "searchString = " & searchString
Set oFilterItems = Folder.Items.Restrict(searchString) 'this is where i get the cannot parse condition error
Debug.Print oFilterItems.Count & " items found."
oFilterItems.Sort "[SentOn]", True  'Sort the results, Descending order (True)

i = 13
With Sheets("eMail Extract")

    'For Each oFilterItem In oFilterItems
    For Each OutlookMail In Folder.Items.Restrict(searchString)
         'If oFilterItem.Class = 43 Then           
            .Cells(i, 1).Value = OutlookMail.ReceivedTime
            .Cells(i, 2).Value = OutlookMail.SenderName
            .Cells(i, 3).Value = OutlookMail.Subject
            .Cells(i, 4).Value = OutlookMail.Body
            If OutlookMail.Attachments.Count > 0 Then .Cells(i, 5).Value = "Yes" Else .Cells(i, 5).Value = "No"
            .Cells(i, 4).WrapText = False
            i = i + 1      
    'End If

Next OutlookMail

End With

End Sub
 

Attachments

  • VBA Error Line.jpg
    VBA Error Line.jpg
    227.5 KB · Views: 16
  • VBA Error.jpg
    VBA Error.jpg
    248.9 KB · Views: 16
  • VBA References Used.jpg
    VBA References Used.jpg
    63.9 KB · Views: 11
Last edited by a moderator:
Judging by the error message, it looks like there must be a syntax error in the SQL SELECT statement. Can you post the entirety of that SQL statement, after it's been assembled into searchString? Maybe one of us can spot the error there.
 
No, not that. I see these statements:
Code:
searchString = "@SQL=""urn:schemas:httpmail:subject"" LIKE '%" & srchsub & "%' "
searchString = searchString & " AND "
searchString = searchString & "[ReceivedTime] >= '" & Format(fdate, "ddddd h:nn AMPM") & "' AND [ReceivedTime] <= '" & Format(tdate, "ddddd h:nn AMPM") & "'"
searchString = searchString & " AND "
searchString = searchString & "[SenderName] = '" & srchSender & "'"
Set oFilterItems = Folder.Items.Restrict(searchString)
So by the time searchString gets to the Restrict method it all adds up to (I think) this:
Code:
[Subject]=<Chr(34)>"urn:schemas:httpmail:subject" LIKE '%<srchsub>%' <Chr(34)> AND [ReceivedTime] >= '<Format(fdate, "ddddd h:nn AMPM")>' AND [SenderName] = '<srchSender>'
What does that expand to after all the <variables> are plugged in? Maybe the error is in there somewhere?

One thing I'm a little suspicious of: Does your database know what to do with times formatted with the 12-hour clock and am/pm? I'd normally use the 24-hour clock, but I don't know what database app you're searching with this SQL statement.
 
This is what it expands to, generated by Debug.Print "searchString = " & searchString

searchString = "urn:schemas:httpmail:subject" LIKE '%6 PM%' AND [ReceivedTime] >= '5/1/2023 12:00 AM' AND [ReceivedTime] <= '5/25/2023 12:00 AM' AND [SenderName] = 'Shivram Aashish'

I am not pulling data from a database/database app. I am trying to pull the required data from Microsoft Outlook application, from 'Inbox' folder. Since outlook inbox's 'Received Time' column has date in the formst of ddd mm/dd/yyyy h:mm AM/PM, i used the same in my code. SS attached.MS Outlook recd. time format.jpg
 
Last edited:
That's what I wanted. I don't see any obvious syntax errors in it, though, and I've never used this sort of thing to select emails from Outlook—didn't even know it's a feature.

(I asked about database apps because it looks a lot like a SQL Select statement, but I guess not.)

The error message certainly seems to say that it doesn't know how to evaluate some part of that string, though. Since I'm ignorant on the details, my suggestion is to simplify it: Cut it down to just one of the conditions, the simplest one, [SenderName] probably. If that works—if it gives you a set of emails from Shivram Aashish—then you can add in the other conditions one at a time and you'll see which one is in error. Even if you don't see what's wrong with it, you can at least experiment once you know where to start.

If even the simplest search string fails with the same error:
Code:
[SenderName] = 'Shivram Aashish'
then you know there's something fundamentally wrong with the way you're approaching the Restrict method. Well, either that or I suppose it could be the syntax of that string—maybe it wants double quotes instead of single? But start shorter, anyway, and build the conditions back up once you have something working.
 
The condition works when I exclude the 'like' subject condition and pull the email details into the excel file. it fails if I include it. Like you mentioned, I did not identify any syntax errors but for sure the code doesn't know to execute some part of the condition. And I believe it is the 'like' subject condition since it works without it. And for my requirement, that subject condition is required to work. :(
 
And I don't see anything in the documentation about the LIKE operator. I do see, however, a passing mention of " the ci_startswith or ci_phrasematch operators"; if you can find out how those work, sounds like one of them might do the job for you.

I, knowing nothing about this, would have just run through all the mail items in a folder and tested each one for the individual fields I wanted. Something like this:
Code:
For Each oitm in MyFolder
  If oitm.Type <> olMailItem Then Goto IterateItem
  If Not oitm.Subject Like "%6 PM%" Then Goto IterateItem
  If oitm.ReceivedTime < #2023-05-01# Then Goto IterateItem
  If oitm.ReceivedTime > #2023-05-24# Then Goto IterateItem
  If oitm.SenderName <> "Shivram Aashish" Then Goto IterateItem

  ' Now you have an item that fits your conditions, and you can proceed with the desired operations.

IterateItem:
  next oitm
 
Back
Top