Manufacturing Information Solutions Forum Index Manufacturing Information Solutions
Your Place for Support and Discussions
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Reading Outlook E-Mails from Access

 
Post new topic   Reply to topic    Manufacturing Information Solutions Forum Index -> Microsoft Access
View previous topic :: View next topic  
Author Message
BIGmiralli
Frequent Poster


Joined: 17 Apr 2007
Posts: 38
Location: Boston, Massachusetts

PostPosted: Fri May 23, 2008 1:01 pm    Post subject: Reading Outlook E-Mails from Access Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    Manufacturing Information Solutions Forum Index -> Microsoft Access All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group