Automation - Access and Outlook - forward and e-mail based on subject text using VBA

Access_MS
Access_MS used Ask the Experts™
on
Hi Experts
I need some help with automation of Outlook 2003 from Access 2003

From Access I need to be able to go through the Sent Items in Outlook, find a particular e-mail based on the content of the subject line, and then forward this e-mail to a new recipient.

All help greatly appreciated.

Regards
Marion
 
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
the best way to do it is to setup a rule in outlook client, while certain email arrive, just forward to a new recipient. If you have to do it in Access, you can use outlook component in VBA, check email folder all the time, while new comes, process it.

Author

Commented:
Hi Richard
sorry - I need to do this as a response to another series of actions which take place in the Access database and there is a time lag between the e-mail coming in and the forward taking place,  so I need to use VBA to do it.
thanks for the reply

Regards
Marion
if you have to do it using VBA in Access. what's your question then?
I created the following code in VB (not VBA) before to check inbox folder. Hope you can find it helpful.

Dim mOutLookApp As Outlook.Application
Dim mNameSpace As Outlook.NameSpace
Dim inBox As Outlook.MAPIFolder
Dim mItem 'As Outlook.MailItem

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

    Set inBox = mOutLookApp.Session.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderInbox)
   
    sHTMLBody = ""
    For i = 1 To inBox.Items.Count
        Set mItem = inBox.Items(i)
        'then you can process each item
    next i

Author

Commented:
Hi Richard
My question... sorry...was obviously not clear from what you have said, so I'll rephrase it...  :-)

When I wrote my question I didnt know how to write the VBA code 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

I have used your code and changed it to work on the Sent Items, which works,  Brrrrilliant - Many thanks :-)

Unfortunately it is possible that there will be 1000's of e-mails in the sent folder so - If possible - I would like to restrict the items I iterate through in the Sent Items to the ones containing my phrase

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

''My changed version of your code...


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
            mItem.To = "M@abc.xy"
            mItem.Body = "Hello" & vbCrLf & mItem.Body
           
            End If
       
    Next i
End Sub

Author

Commented:
Thanks for the help - I have posted a new question regarding filtering the records returned to just the ones I need.

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