Script to monitor emails and send info to access database

  • Thread starter Thread starter melmac
  • Start date Start date
M

melmac

Hi everyone,

I hope someone can help me with my problem. I was wondering if it's possible
to write a macro script that will monitor incoming emails and send info to an
Access database? To make it more clearer, here's the scenario: I need to
monitor emails which have the word "Handovers" on the subject line and i'd
like the macro script to put a "Yes" on one of the fields in a table within
an Access
database. Is this even possible or do i have to ask someone who's more an
expert on Access? Please, i hope someone out there can help me.

Thanks,
 
You might use the ItemAdd event of the inbox for that. Please see the VBA
help for an example. And here's a sample for how to connect to an Access
database:

http://www.vboffice.net/sample.html?mnu=2&pub=6&lang=en&smp=67&cmd=showitem

Maybe you'd just need the connection object, which supports an Execute
method. The help file should also have an example for Connection.Execute.

--
Best regards
Michael Bauer - MVP Outlook

: VBOffice Reporter for Data Analysis & Reporting
: Outlook Categories? Category Manager Is Your Tool
: <http://www.vboffice.net/product.html?pub=6&lang=en>


Am Mon, 25 Aug 2008 13:06:21 -0700 schrieb melmac:
 
Place this script in "ThisOutlookSession" in the VB editor in Outlook (make
sure that you have macros enabled in macro security)

'##################################################
Option Explicit
' the next line sets incoming emails as an event to start
TargetFolderItems_ItemAdd
Dim WithEvents TargetFolderItems As Items

Const MBOX_NAME As String = "xxxx" ' change xxxx to match your mailbox name

'##################################################
' this routine will run each time Outllook first starts
Private Sub Application_Startup()
Dim ns As Outlook.NameSpace
Set ns = Application.GetNamespace("MAPI")
Set TargetFolderItems =
ns.Folders.Item(MBOX_NAME).Folders.Item("Inbox").Items
End Sub

'##################################################
' this is the event driven routine
Sub TargetFolderItems_ItemAdd(ByVal Item As Object)
Dim olAtt As Attachment
Dim BODY_NAME As String
Dim i As Integer

If InStr(1, Item, "Handovers") Then
Item.Display ' displays subject title in a msgbox when a mail
arrives with "Handovers" in the subject title
Put_Yes_In_Access
End If
Set olAtt = Nothing
End Sub

'##################################################
Sub Put_Yes_In_Access()
' now you need code here to put Yes in a table in an MDB
End Sub
'##################################################
' this is the Application_Quit event code in ThisOutlookSession module
Private Sub Application_Quit()
Dim ns As Outlook.NameSpace
Set TargetFolderItems = Nothing
Set ns = Nothing
End Sub
 
note that some of the comment lines have over-spilled into two lines !!!!

e.g.
' the next line sets incoming emails as an event to start
TargetFolderItems_ItemAdd

make sure this is on one line !!!!!
 
Back
Top