Access_MS
asked on
Automation - Access and Outlook - forward and e-mail based on subject text using VBA
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
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
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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.GetDef aultFolder (Outlook.O lDefaultFo lders.olFo lderSentMa il)
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
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("
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.GetDef
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
ASKER
Thanks for the help - I have posted a new question regarding filtering the records returned to just the ones I need.