CODEWITHSUNDEEP
vbaoutlook
RAJA THEVAR
RAJA THEVAR

Reputation: 421

How to filter mails using received time using vba

I am trying to find a way to filter email based on multiple criteria however i while running the below code i am getting an error "Cannot Parse Condition.Error at"09". The ReceivedDate is 8/24/2008 9:55:30 PM

ReceivedDate = Me.cballocation.Column(1)
Sender = Me.cballocation.Column(2)
Subject = Me.cballocation.Column(0)

sFilter = "[subject] = '" & Subject & "' and DateValue[ReceivedTime]=" & Format$(ReceivedDate, "ddddd h:nn AMPM") & " and " & "[Sender]= '" & Sender & "'"
Set Ns = ol.GetNamespace("MAPI")
Set ml = Ns.Folders("MIMUMBAI").Folders("Inbox").Folders("Completed")
Set ml = ml.Items.Restrict(sFilter)

Upvotes: 2

Views: 4277

Answers (3)

Eugene Astafiev
Eugene Astafiev

Reputation: 49395

First of all, you need to format the date and time object so Outlook can understand it for comparing with actual values. For example, you can use the ToSting method of the DateTime structure:

DateTime dt = new DateTime(DateTime.Now.Year, DateTime.Now.Month,
                           DateTime.Now.Day, 23, 59, 00, 00);
string dateTimeEnd = dt.ToString("MM/dd/yyyy hh:mm tt");
string searchCriteria = "[Start]<=\"" + dateTimeEnd + "\"" + " AND [End]>=\""+ dateTimeStart +"\"";

You may find the following articles with the sample code in VB.NET included helpful:

In case of VBA macros you may use the Format function. Here is what MSDN states:

Dates and times are typically stored with a Date format, the Find and Restrict methods require that the date and time be converted to a string representation. To make sure that the date is formatted as Microsoft Outlook expects, use the Format function. The following example creates a filter to find all contacts that have been modified after January 15, 1999 at 3:30 P.M.

  sFilter = "[LastModificationTime] > '" & Format("1/15/99 3:30pm", "ddddd h:nn AMPM") & "'"

Upvotes: 0

Dmitry Streblechenko
Dmitry Streblechenko

Reputation: 66225

DateValue[ReceivedTime] is not a valid condition. You must use a range

([ReceivedTime] > Date1) AND ([ReceivedTime] < Date2)

Upvotes: 1

dnep
dnep

Reputation: 562

There is indeed an error in the filter string. Where you used:

sFilter = "[subject] = '" & Subject & "' and DateValue[ReceivedTime]=" & Format$(ReceivedDate, "ddddd h:nn AMPM") & " and " & "[Sender]= '" & Sender & "'"

You should have used:

sFilter = "[subject] = '" & Subject & "' and DateValue[ReceivedTime]='" & Format$(ReceivedDate, "ddddd h:nn AMPM") & "' and " & "[Sender]= '" & Sender & "'"

The filter conditions for date and time must be passed as strings - and yours were missing the singlequotes.

Upvotes: 0

Related Questions