How to run Access database automatically based on Outlook e-mail

[kupz]

I am trying to automate a daily process. Basically, accounting does a manual release of a batch of invoices in the order-entry system which ultimately updates the SQL server tables. The manual release is done at different times by accounting. An email is sent out to an analyst by the DBA once the the sales batches have been updated in SQL Server. The analyst will, in turn, run a front-end database(Access) in order to reconcile the invoices on a MTD basis.

Since the batch update is done at different times. I thought of automatically running the database once the e-mail is received from the DBA based on either the e-mail subject line or sender's name. I do not have any idea how I would be able to do this in VBA. Can someone help?

Thank you.

 

[drewby1]

hi kupz
try looking at MS Knowledge base article Q292063 (how to create a custom rule using VBA)

some or all of the following code should point you in the right direction
(haven't tested so don't know how useful - might even get flamed for posting duff code!!)

I think this code needs putting in a class and the Sub Initialize_handler() needs to be run before the code (which fires up whennew mail hits Outlook

Of course you'll need Outlook running when the email is received from the DBA oitherwise the code won't fire

Let me know how you get on. Hopefully if the code doesn't work it'll give you a few pointers in the right direction

'*** 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 = &quot;Inbox&quot; Then
    myExplorers.Item(x).Display
    myExplorers.Item(x).Activate

    '**** start of my code ****
    'should loop thru default inbox until
    'finds trigger email (eg Subject = &quot;sales batches update&quot;)
    'then triggers database
    Set fld = ActiveExplorer.CurrentFolder
    Set itms = fld.Items
    
    Set triggerText = &quot;sales batches update&quot;
    
    ItemCount = itms.Count
    If ItemCount = 0 Then
        MsgBox &quot;Nothing in Inbox&quot;
        Exit Sub
    End If
    For i = 1 To ItemCount
        Set itm = itms(i)
        'check if this item is a mail message
        If itm.MessageClass = &quot;IPM.Note&quot; 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 = &quot;tblMTDInvoice&quot;
    strDBName = &quot;X:\Invoices\Invoices.mdb&quot;

    'Reference Access Database
    Set dao = Application.CreateObject(&quot;DAO.DBEngine.35&quot;)
    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 (&quot;Sales Totals Query&quot;, , acReadOnly)

    rst.Close
    MsgBox &quot;Reconciliation done!&quot;
End Sub
'**** end of my code ****

 

[kupz]

drewby1,

I think this will work out just fine. I'll just need to do some modifications. I really do appreciate the help. Will let you know how this works out and thanks again.

kupz