Microsoft Access Office VB VBA Help and Examples

Reading Outlook E-Mails from Access

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. This code works with Outlook 97, Outlook 98 and Outlook 2000. 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.

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!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
            Rst!datesent = OlMail.ReceivedTime
            Rst!status = "Failed"
            OlMail.Move OlFailed
        End If
    End If
    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.

The code and application content of this site is copyright of Smiley I.T. and as such reproduction in any form which is for commercial use requires the permission of the Webmaster. Any use of this code for non-commercial use only requires a link or comment back to the original page you took the code from.