'*** code from Outlook VBA help ***
'NewMail Event Example
'This example displays the Inbox folder when new mail arrives. The sample code must be placed in a class module, and the Initialize_handler routine must be called before the event procedure can be called by Microsoft Outlook.
Dim WithEvents myOlApp As Outlook.Application
Sub Initialize_handler()
Set myOlApp = CreateObject("Outlook.application")
End Sub
Private Sub myOlApp_NewMail()
Dim myExplorers As Outlook.Explorers
Dim myFolder As Outlook.MAPIFolder
Set myExplorers = myOlApp.Explorers
Set myFolder = myOlApp.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
If myExplorers.Count <> 0 Then
For x = 1 To myExplorers.Count
On Error GoTo skipif
If myExplorers.Item(x).CurrentFolder.Name = "Inbox" Then
myExplorers.Item(x).Display
myExplorers.Item(x).Activate
'**** start of my code ****
'should loop thru default inbox until
'finds trigger email (eg Subject = "sales batches update")
'then triggers database
Set fld = ActiveExplorer.CurrentFolder
Set itms = fld.Items
Set triggerText = "sales batches update"
ItemCount = itms.Count
If ItemCount = 0 Then
MsgBox "Nothing in Inbox"
Exit Sub
End If
For i = 1 To ItemCount
Set itm = itms(i)
'check if this item is a mail message
If itm.MessageClass = "IPM.Note" Then
'check for trigger text
If itm.Subject = triggerText Then openAccessDB()
End If
Next
'**** end of my code ****
Exit Sub
End If
skipif:
Next x
End If
On Error GoTo 0
myFolder.Display
End Sub
'**** start of my code ****
Sub openAccessDB()
'set database to open
'and table or query to open
'CHANGE FOLLOWING TWO LINES TO OPEN CORRECT DATABASE
'AND QUERY/TABLE
strAccesstblName = "tblMTDInvoice"
strDBName = "X:\Invoices\Invoices.mdb"
'Reference Access Database
Set dao = Application.CreateObject("DAO.DBEngine.35")
Set wks = dao.Workspaces(0)
Set db = wks.OpenDatabase(strDBName)
'Open Access table containing mail data
Set rst = db.OpenRecordset(strAccesstblName)
'THIS BIT MIGHT NEED SOME WORK!
'insert any code here to perform whatever action needed
'could try opening query similar to below?
'Set rst = db.OpenQuery ("Sales Totals Query", , acReadOnly)
rst.Close
MsgBox "Reconciliation done!"
End Sub
'**** end of my code ****