BIGmiralli Frequent Poster
Joined: 17 Apr 2007 Posts: 38 Location: Boston, Massachusetts
|
Posted: Fri May 23, 2008 1:01 pm Post subject: Reading Outlook E-Mails from Access |
|
|
This code works with Outlook 97, Outlook 98 and Outlook 2000.
You have formatted E-mails responses which you want to be automatically inserted into a database or moved to another folder or just processed to see who responded. The following bit of code is an example of how to do just that.
Remember to add the MS Outlook reference to your database or project before running this code. This particular bit of code looks for the word accept or decline in the subject line and adds a record to a database accordingly, then moves the emails to a folder underneath the inbox folder accordingly.
Code: | Public Sub ImportOutlookItems()
Dim Olapp As Outlook.Application
Dim Olmapi As Outlook.NameSpace
Dim Olfolder As Outlook.MAPIFolder
Dim OlAccept As Outlook.MAPIFolder
Dim OlDecline As Outlook.MAPIFolder
Dim OlFailed As Outlook.MAPIFolder
Dim OlMail As Object 'Have to late bind as appointments e.t.c screw it up
Dim OlItems As Outlook.Items
Dim OlRecips As Outlook.Recipients
Dim OlRecip As Outlook.Recipient
Dim Rst As Recordset
Set Rst = CurrentDb.OpenRecordset("tbl_Temp") 'Open table tbl_temp
'Create a connection to outlook
Set Olapp = CreateObject("Outlook.Application")
Set Olmapi = Olapp.GetNamespace("MAPI")
'Open the inbox
Set Olfolder = Olmapi.GetDefaultFolder(olFolderInbox)
Set OlItems = Olfolder.Items
'Set up the folders the mails are going to be deposited in
Set OlAccept = Olfolder.folders("Accept")
Set OlDecline = OLfolder.Folders("Decline")
Set OlFailed = Olfolder.Folders("Failed")
'Set up a loop to run till the inbox is empty (otherwise it skips some)
Do Until OlItems.Count = 0
'Reset the olitems object otherwise new incoming mails and moving mails get missed
Set OlItems = OLfolder.Items
For Each OlMail In OlItems
'For each mail in the collection check the subject line and process accordingly
If OlMail.UnRead = True Then
OlMail.UnRead = False 'Mark mail as read
Rst.AddNew
Rst!Name = OlMail.SenderName
If InStr(1, OlMail.Subject, "Accept") > 0 Then
Rst!status = "Attending"
Rst!datesent = OlMail.ReceivedTime
OlMail.Move OlAccept
ElseIf InStr(1, OlMail.Subject, "Decline") > 0 Then
Rst!datesent = OlMail.ReceivedTime
Rst!status = "Decline"
OlMail.Move OlDecline
Else
Rst!datesent = OlMail.ReceivedTime
Rst!status = "Failed"
OlMail.Move OlFailed
End If
Rst.Update
End If
Next
Loop
MsgBox "Your wish is my command. New mails have been checked. Please check the tbl_temp for details", vbOKOnly
End Sub |
Please note if you don't want to use the inbox you don't have too. You can instead set up a new folder and use that instead and set up a rule wizard in outlook to move the relevant mails when they hit the inbox. Alternatively you could get access to only process certain mails.
________
MYWEBCAMSHOOKUP |
|