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

Access_MS
Access_MS used Ask the Experts™
on
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
            mItem.Forward
           'show for testing only
            mItem.Display
           
            'add in recipients...do other stuff
            mItem.To = "M@abc.xy"
            mItem.Body = "Hello" & vbCrLf & mItem.Body
           
            End If
       
    Next i
End Sub
Comment
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.

Author

Commented:
Hi
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

Marion

   


Software Quality Lead Engineer
Top Expert 2011
Commented:
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
     'findObj.Item(maiCount)
next

Can then be used for just the specific items

Chris
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

Commented:
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.

Chris

Author

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

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

Chris

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