Todd Shillam said:
Anybody know if there's a method to extract Outlook message text into
Microsoft Access as a variable? I'd like to be able to extract text in the
body of a message; however, I'd go for the subject line as well.
Here's some code that was posted several years ago, source unknown. I have
not personally tested it so I cannot vouch for it, but it does look like it
should work.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
You have formatted E-mails responses which you want to be automatically
inserted into a database or moved to another folder or just processes 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 datbase 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("tblTemp") 'Open table tblTemp
'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 "New mails have been checked. Please check the tblTemp 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.
