Outlook Automation: is there a better way?

[deko]

I just upgraded to Office 2007 and would like to refactor my automation code
to improve speed and perhaps add some new functionality.

My Access 2007 database has code that searches Outlook 2007 for email
messages. The database contains Contacts, each with one or more email
addresses. The current code loops through each message in the Outlook Inbox
trying to match one of the Contact's email addresses with the
SenderEmailAddress property of each mail item. When a match is found, the
message subject and other data is added to a recordset and displayed in a
datasheet in Access.

Here's an abbreviated example:

Dim olapp As Outlook.Application
Dim olmi as Outlook.MailItem
Dim olitms as Outlook.Items
Dim objItem As Object

Set olapp = New Outlook.Application
Set olns = olapp.GetNamespace("MAPI")
Set olFolder = olns.GetDefaultFolder(olFolderInbox)
Set olitms = olFolder.Items

Do While Not rstContactAddresses.EOF
For Each objItem in olitms
If TypeOf objItem Is Outlook.MailItem Then
Set olmi = objItem
If olmi.SenderAddress = rstContactAddresses!EmailAddress Then
Call AddToRecordset(olmi.whatever)
End if
End if
Next objItem
Loop

Is there a better way to do this with VBA? Is it worth trying to use C# and
VSTO here? Is it possible to tap into the indexed Outlook email search
feature? Other new stuff in 2007 that can help?

Thanks in advance.

 

[Dmitry Streblechenko]

Do not loop through all items in the folder, use Items.Find / FindNext.
Off teh top of my head:

set olmi = olitms.Find("[SenderEmailAddress] = '" &
rstContactAddresses!EmailAddress & "'")
while Not (olmi Is Nothing)
Call AddToRecordset(olmi.whatever)
set olmi = olitms.FindNext
wend
--
Dmitry Streblechenko (MVP)
http://www.dimastr.com/
OutlookSpy - Outlook, CDO
and MAPI Developer Tool
-

 

[JP]

In addition to what Dmitry suggested, you could also use the Restrict
Method to limit you search to ONLY those items in the Inbox that match
your criteria. This would significantly reduce the amount of time
spent looping through a folder.

See
http://msdn.microsoft.com/en-us/library/bb220369.aspx


--JP

 

[Dmitry Streblechenko]

Don't use Restrict unless the restriction stays the same: Exchange caches
restrictions for a week by default. Cached restriction arae updated every
time an item in the folder iss modified/added/deleted.
Unless you really want the restriction to be cached, do not use Restrict
against an Exchange store.

--
Dmitry Streblechenko (MVP)
http://www.dimastr.com/
OutlookSpy - Outlook, CDO
and MAPI Developer Tool
-

 

[deko]

Thanks for the heads up, Dmitry.

I'm wondering if there's a way to force the cache to clear itself. Also, I
assume on a local Outlook PST there are no cache issues.

From reading the MSDN article, the Restrict method seems to the better
option for my needs: "The Restrict method is significantly faster if there
is a large number of items in the collection, especially if only a few items
in a large collection are expected to be found." This is the case when
searching a large Inbox for messages matching one or two email addresses.

What's really interesting is the ability to use an SQL query as the filter:

The example in the MSDN article is this:
filter = "@SQL=""http://schemas.microsoft.com/mapi/proptag/0x0037001f"" =
'the right ""stuff""'"

What is that hyperlink? Is that just part of the example in the MSDN
article?

How about: "@SQL = "SELECT EmailAddress from tblContactEmailAddresses" -
would this work as a Restrict filter?

 

[deko]

To answer my own question:

Those links are schema property names used in the SQL-like DASL syntax,
which begins with @SQL=

See: http://blogs.msdn.com/rgregg/archive/2005/12/12/502904.aspx

If you put the below code in a module and run it, you get this:

? FindMessages()
@SQL =("urn:schemas:httpmail:Subject" CI_PHRASEMATCH 'test')
Error Number -2147352567: Cannot parse condition. Error at "CI_PHRASEMATCH".

How is my syntax incorrect? Am I missing something?

Public Function FindMessages()
On Error GoTo HandleErr
Const QT As String = """"
Dim olapp As Outlook.Application
Dim olns As Outlook.NameSpace
Dim olfldr As Outlook.Folder
Dim olmi As Outlook.MailItem
Dim olitms As Outlook.Items
Dim objItem As Object
Dim strFilter As String

Set olapp = New Outlook.Application
Set olns = olapp.GetNamespace("MAPI")
Set olfldr = olns.GetDefaultFolder(olFolderInbox)
Set olitms = olfldr.Items

'===== available fields ====='
'attachmentfilename
'httpmail -BCC
'httpmail -CC
'content-disposition-type
'content-media-type
'httpmail -Date
'datereceived
'httpmail -From
'HasAttachment
'htmldescription
'Importance
'normalizedsubject
'Priority
'httpmail-reply-to
'Sender
'Subject
'textdescription
'thread -topic
'to

'===== Keywords ====='
'CI_PHRASEMATCH
'CI_PHRASEMATCH

strFilter = "@SQL =(" & QT & "urn:schemas:httpmail:Subject" & QT & "
CI_PHRASEMATCH 'test')"
Debug.Print strFilter
objItems = olitms.Restrict(strFilter)
Debug.Print objItems.Count
Exit_Here:
On Error Resume Next
Set olitms = Nothing
Set olfldr = Nothing
Set olns = Nothing
Set olapp = Nothing
Exit Function
HandleErr:
Debug.Print "Error Number " & Err.Number & ": " & Err.Description
Resume Exit_Here:
End Function

 

[deko]

Beta 2

does not error out, but the filter does not seem to work
note: messages exist in the Inbox that wd match filter criteria

? FindMessages()
562 unfiltered
@SQL="urn:schemas:httpmail:Subject" CI_PHRASEMATCH 'Flight'
0 filtered

Public Function FindMessages()
Const QT As String = """"
On Error GoTo HandleErr
Dim olapp As Outlook.Application
Dim olns As Outlook.NameSpace
Dim olfldr As Outlook.Folder
Dim olmi As Outlook.MailItem
Dim olitms As Outlook.Items
Dim matchedItems As Outlook.Items
Dim strFilter As String

Set olapp = New Outlook.Application
Set olns = olapp.GetNamespace("MAPI")
Set olfldr = olns.GetDefaultFolder(olFolderInbox)
Set olitms = olfldr.Items

'===== available fields ====='
'attachmentfilename
'httpmail -BCC
'httpmail -CC
'content-disposition-type
'content-media-type
'httpmail -Date
'datereceived
'httpmail -From
'HasAttachment
'htmldescription
'Importance
'normalizedsubject
'Priority
'httpmail-reply-to
'Sender
'Subject
'textdescription
'thread -topic
'to

'===== Keywords ====='
'CI_STARTSWITH
'CI_PHRASEMATCH

'strFilter = "@SQL=" & QT & "urn:schemas:httpmail:HasAttachment" & QT &
" = 1"
strFilter = "@SQL=" & QT & "urn:schemas:httpmail:Subject" & QT & "
CI_PHRASEMATCH 'Flight'"
Debug.Print olitms.Count & " unfiltered"
Debug.Print strFilter
Set matchedItems = olitms.Restrict(strFilter)
Debug.Print matchedItems.Count & " filtered"
Exit_Here:
On Error Resume Next
Set matchedItems = Nothing
Set olitms = Nothing
Set olfldr = Nothing
Set olns = Nothing
Set olapp = Nothing
Exit Function
HandleErr:
Debug.Print "Error Number " & Err.Number & ": " & Err.Description
Resume Exit_Here:
End Function

 

[deko]

the missing link...

http://msdn.microsoft.com/en-us/library/aa123730(EXCHG.65).aspx

urn:schemas:mailheader:approved
urn:schemas:httpmail:attachmentfilename
urn:schemas:mailheader:bcc
urn:schemas:httpmail:bcc
urn:schemas:httpmail:cc
urn:schemas:mailheader:cc
urn:schemas:mailheader:comment
urn:schemas:mailheader:content-base
urn:schemas:mailheader:content-class
urn:schemas:mailheader:content-description
urn:schemas:mailheader:content-disposition
urn:schemas:httpmail:content-disposition-type
urn:schemas:mailheader:content-id
urn:schemas:mailheader:content-language
urn:schemas:mailheader:content-location
urn:schemas:httpmail:content-media-type
urn:schemas:mailheader:content-transfer-encoding
urn:schemas:mailheader:content-type
urn:schemas:mailheader:control
urn:schemas:httpmail:date
urn:schemas:mailheader:date
urn:schemas:httpmail:datereceived
urn:schemas:httpmail:displaycc
urn:schemas:httpmail:displayto
urn:schemas:mailheader:disposition
urn:schemas:mailheader:disposition-notification-to
urn:schemas:mailheader:distribution
urn:schemas:mailheader:expires
urn:schemas:mailheader:expiry-date
urn:schemas:httpmail:flagcompleted
urn:schemas:mailheader:followup-to
urn:schemas:httpmail:from
urn:schemas:mailheader:from
urn:schemas:httpmail:fromemail
urn:schemas:httpmail:fromname
urn:schemas:httpmail:hasattachment
urn:schemas:httpmail:htmldescription
urn:schemas:httpmail:importance
urn:schemas:mailheader:importance
urn:schemas:mailheader:in-reply-to
urn:schemas:mailheader:keywords
urn:schemas:mailheader:lines
urn:schemas:mailheader:message-id
urn:schemas:httpmail:messageflag
urn:schemas:mailheader:mime-version
urn:schemas:mailheader:newsgroups
urn:schemas:httpmail:normalizedsubject
urn:schemas:mailheaderrganization
urn:schemas:mailheaderriginal-recipient
urn:schemas:mailheaderath
urn:schemas:mailheaderosting-version
urn:schemas:httpmailriority
urn:schemas:mailheaderriority
urn:schemas:mailheader:received
urn:schemas:mailheader:references
urn:schemas:mailheader:relay-version
urn:schemas:httpmail:reply-by
urn:schemas:mailheader:reply-by
urn:schemas:httpmail:reply-to
urn:schemas:mailheader:reply-to
urn:schemas:mailheader:return-path
urn:schemas:mailheader:return-receipt-to
urn:schemas:httpmail:savedestination
urn:schemas:httpmail:saveinsent
urn:schemas:mailheader:sender
urn:schemas:httpmail:sender
urn:schemas:httpmail:senderemail
urn:schemas:httpmail:sendername
http://schemas.microsoft.com/exchange/sensitivity
urn:schemas:mailheader:sensitivity
urn:schemas:httpmail:subject
urn:schemas:mailheader:subject
urn:schemas:httpmail:submitted
urn:schemas:mailheader:summary
urn:schemas:httpmail:textdescription
urn:schemas:mailheader:thread-index
urn:schemas:mailheader:thread-topic
urn:schemas:httpmail:thread-topic
urn:schemas:httpmail:to
urn:schemas:mailheader:to
urn:schemas:mailheader:x-mailer
urn:schemas:mailheader:x-message-completed
urn:schemas:mailheader:x-message-flag
urn:schemas:mailheader:x-unsent
urn:schemas:mailheader:xref

 

[deko]

This works nicely, too:

Dim olapp As Outlook.Application
Dim olfldr As Outlook.Folder
Dim exp As Outlook.Explorer
Dim strSearch As String

Set olapp = New Outlook.Application
Set olfldr = olns.GetDefaultFolder(olFolderInbox)

strSearch = "body:beta _
subject:msdn _
from:[email protected] _
received:>=5/1/2008 12:00 AM <6/1/2008 12:00 AM"

Set exp = olapp.Explorers.Add(olfldr, olFolderDisplayNoNavigation)
exp.Search strSearch, olSearchScopeCurrentFolder
exp.Display

With additional code I can dynamically select a folder as well as pass in
variables for those 4 search criteria.

 

[Dmitry Streblechenko]

Correct, PST is not subject to this limitation.
There is no way to clear the cache.

--
Dmitry Streblechenko (MVP)
http://www.dimastr.com/
OutlookSpy - Outlook, CDO
and MAPI Developer Tool
-