help needed with filtering mail

[Ed Stevens]

Platform: Win2k, Outlook 2000, Excel 2000

I have been searching this ng's archives looking for a solution to my
problem, but the closest I've come is some out-of-context snippets that
assume more knowledge than I have. Being somewhat inexperienced with
VB and totally new to the Outlook object library, I am looking for some
direct assistance.

I am working on a macro in Excel to populate a worksheet with data
parsed out of mail messages. The source mail folder contains a lot of
items I will not want to deal with and would like to filter out based
on information supplied at run-time. Currently I am doing this
'manually' by each mail item in the folder and examining it, but this
is a waste of a lot of cycles, making it painfully slow, so I am
looking for a way to get a 'pre-selected' collection.

Here is what I have so far:

'=========== begin code sample ===============
Sub Main()

Dim golApp As Outlook.Application
Dim gnspNameSpace As Outlook.NameSpace
Dim fldr As Outlook.MAPIFolder
Dim olmail As MailItem
Dim strCriteria As String

Set golApp = New Outlook.Application
Set gnspNameSpace = golApp.GetNamespace("MAPI")
Set fldr = gnspNameSpace.PickFolder

strCriteria = UCase(InputBox("Which database?"))

For Each olmail In fldr.Items

-- code to determine if I want to work with this mail item
-- i.e. instr(olmail.subject,strCriteria) > 0

-- code to parse the item and populate the Excel worksheet

Next olmail
'=========== end code sample ===============

Only about 10% to 15% of the mail items in the folder will meet my
criteria, so it would be very helpful to be able to work with a
collection that was pre-selected on the value of strCriteria.

Thanks for any asisstance.

 

[Eric Legault [MVP - Outlook]]

All you need to do is call the Restrict method with your criteria against the
Items collection in the chosen folder to return a collection of messages that
meet your criteria. I've modified your code to create a macro that
illustrates this technique:

Sub SearchEmailsBySubject()
On Error Resume Next

Dim golApp As Outlook.Application
Dim gnspNameSpace As Outlook.NameSpace
Dim fldr As Outlook.MAPIFolder
Dim olmail As MailItem
Dim strCriteria As String

'New
Dim objItems As Outlook.Items

Set golApp = New Outlook.Application
Set gnspNameSpace = golApp.GetNamespace("MAPI")
Set fldr = gnspNameSpace.PickFolder

strCriteria = UCase(InputBox("Which database?"))
Set objItems = fldr.Items.Restrict("[Subject] = '" & strCriteria & "'")

For Each olmail In objItems
MsgBox "Subject = " & olmail.Subject
Next

Set golApp = Nothing
Set gnspNameSpace = Nothing
Set fldr = Nothing
Set olmail = Nothing
End Sub

 

[Ed Stevens]

OK, I see how that works and understand better how to structure it.
There is one problem and from my other readings on this ng, I'm afraid
it might not be do-able. That is, what I really need to filter on
would be the left x bytes of [subject]. If I were filtering manually,
it would be like this:

for each olmail in objItems
if left(olmail.subject, len(strCriteria)) = strCriteria then
' work with this one
end if
next

So ... if that functionality can't be done in the filter, how about
returning the collection in a sorted order? If that can be done, I
could modify the above logic to quickly get to the first item I'm
looking for, and bail out of the loop when I'd passed the last one.
Not quite as good as the filter, but at least I wouldn't have to
examine each and every item in the folder.

 

[Eric Legault [MVP - Outlook]]

The Restrict method has limitations, but you can get around this by using the
AdvancedSearch method. See this article for a great example perfect for your
situation:

http://msdn.microsoft.com/library/d.../dnout2k2/html/ODC_CustomViews.asp?frame=true

This reference also contains some syntax options for building DASL search
criteria; I believe the CONTAINS predicate can be used to limit the Subject
line to specific words:

http://msdn.microsoft.com/library/d...s/wss/_exch2k_word_and_character_matching.asp