Using VBA in Access - Find Sent Items on OUtlook iwht matching criteria in the Subject Line

Access_MS used Ask the Experts™
With help from another expert yesterday I have got the code below to do the following
- From Access
- Look at the sent items in the Outlook Sent Items Folder
- Find an e-mail with a certain phrase in the subject line
- forward that e-mail to a new recipient

If possible - I would like to now restrict the items I iterate through in the Sent Items Folder to just the ones containing my phrase (Too many items to go through them all)

I dont know how to change it to only look at just the e-mails with the subject line I have declared in the variable ~ strFilter.  

Do you know if this is possible and if so how would I do this? Thanks

Code I have so far is....

Dim mOutLookApp As Outlook.Application
Dim mNameSpace As Outlook.NameSpace
Dim sentBox As Outlook.MAPIFolder

'Dim mitem As Outlook.MailItem
Dim mItem As Object

Dim strFilter As String
Dim strExistingSubject As String

    Set mOutLookApp = New Outlook.Application
    Set mNameSpace = mOutLookApp.GetNamespace("MAPI")
    mNameSpace.Logon , , False, True

    strFilter = "Set up Request:  2010082013311101"
'ideally here I would just like to look at the e-mails with the above string in the subject line...
'i tried to use "Restrict" but I think I do not have the correct syntax
    Set sentBox = mOutLookApp.Session.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderSentMail)
    sHTMLBody = ""
    For i = 1 To sentBox.Items.Count
        Set mItem = sentBox.Items(i)
        'then you can process each item
            'forward the message

            strExistingSubject = mItem.Subject
            If strFilter = strExistingSubject Then
           'show for testing only
            'add in other stuff
            mItem.To = "M@abc.xy"
            mItem.Body = "Hello" & vbCrLf & mItem.Body
            End If
    Next i
End Sub
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I don't think i understand the Q.

You appear already to be looking only at the items which match your filter.


Thanks for your reply - a bit more explanation..

At this point in the code I am looking at all of the e-mails in the sent items folder

 Set sentBox = mOutLookApp.Session.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderSentMail)

I then step through them one by one to find the one I want - it works but I am going through 1000's of e-mails so...

I want to know if there is a way to only find the e-mails that match without having to step through them all - I found "Restrict" in my Outloook code manual, as a way to only find certain items however I've tried and  but I dont know how to use this in the context I have here.

Is that any clearer?
Thanks & regards



Software Quality Lead Engineer
Top Expert 2011
General syntax is:

strSubject = "SOmething for the subject I am looking for"
strFilter = "[Subject] = '" & strSubject & "'"
Set findObj = sentBox.items.Restrict(strFilter)

A loop on findobj, i.e. :

For maiCount = 1 To findobj.count

Can then be used for just the specific items

Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Haven't tried from access to be absolutely certain but if you want to use wilodcards a slight change is needed where % is the wildcard.

    strFilter = "@SQL=urn:schemas:httpmail:subject like '%xs'"

%xs returns anything ending in xs whereas

    strFilter = "@SQL=urn:schemas:httpmail:subject like 'xs%'"

xs% returns anything starting xs and of course they can be combined.



Hi Chris
Many thanks for the answers - both the intial code and the filter enhancement work beautifully.
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Glad to help, and thanks for the feedback on the enhancement - it's useful to know it worked across the applications hence my appreciation.


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial