none
Outlook Automation Problem RRS feed

  • Question

  • Hi

    Using office 2003.

    I am automating Outlook through Access to send emails and update access tables when the email is sent.

    I have a problem that an instance of Access remains open if i run the code in outlook to update the Access tables. I have narrowed it down by checking task manager and can confirm that at any point up until the following code runs Access behaves itself and closes.

    I can not see that i have not set any objects to nothing (even put them where i probably should not just to see).

    Updated.........

    I posted this in an Access forum and they told me to use quit in various guises (did not help) and to remove the Access form i used to store variables, also to remove dlookups etc. which i did by passing the variables to the routine, but still Access hangs around. The code where i stripped out everything that could be causing the problem is at the end. I included it all to see if anyone can spot anything obvious!

    Oops almost forgot also took out the NZ function.

    thanks

    Neil
    -------------------------

    CODE

    Private Sub updateAccess(lngFileId As Long, lngContactID As Long)
    Dim db As dao.Database

    Dim ws As Workspace
    Dim rst As dao.Recordset
    Dim dbEng As dao.DBEngine
    Dim strCriteria As String
    Dim StrDBName As String
    Dim strSQL As String
    Dim lngEmployeeId As Long
    'Dim lngContactID As Long
    Dim EnquiryID As Integer
    Dim strFileName As String
    Dim strDocType As String
    Dim lngIDCheck As Long
    Dim varCheck As Variant
    Dim blnQuoted As Boolean

    If Nz(lngContactID) = 0 Then 'do not update td_docsent if no contact is available.
    Exit Sub
    End If
        Set dbEng = Application.CreateObject("DAO.DBEngine.36")

        DBEngine.SystemDB = "\\OurServer\Southall\DATABASE\System1.mda"
         Set ws = DBEngine.CreateWorkspace("tasakoWS", "word", "word", dbUseJet)

       'strDBName = "C:\DATABASE\Dauntless-tasako2003-1.mdb  '/wrkgrp F:\database\system1.mda"
        StrDBName = "\\OurServer\AccessBE$\tasako3.mdb"
            Set db = ws.OpenDatabase(StrDBName)

       lngEmployeeId = CLng(Forms!frmUserLog!txtEmployeeID)

        ' Perhaps look up contactId to save in the td_docsent table

        'If (Forms!frmUserLog!txtContactID) <> "" Then 'read contact id for later use
        'lngContactID = CLng(Forms!frmUserLog!txtContactID)
        'Else
        'GoTo JumpOut
        'End If

        Set rst = db.OpenRecordset("filelist", dbOpenDynaset)
    If rst.RecordCount = 0 Then ' if no record exists exit
        rst.Close
        Set rst = Nothing
        Call ClearUserTempLog
        db.Close
        Set db = Nothing
        ws.Close
        Set ws = Nothing

        Set dbEng = Nothing


       Exit Sub   ' exit if no records
    End If
       strCriteria = "bwk_Filelist =" & lngFileId

    rst.FindFirst strCriteria

    If Not rst.NoMatch Then 'If there is an existing record copy it's id field

        lngFileId = rst.Fields("bwk_Filelist")
        rst.Close
        Set rst = db.OpenRecordset("td_DocSent", dbOpenDynaset)
       If rst.RecordCount = 0 Then ' if no record exists in td_DocSent then exit
        rst.Close
        Set rst = Nothing
        Call ClearUserTempLog
        db.Close
        Set db = Nothing
        ws.Close
        Set ws = Nothing
        Set dbEng = Nothing

        Exit Sub  ' exit if no records
       End If
       'strCriteria = "bwk_Filelist =" & intFileId

        ' it is a new record add the details
        rst.AddNew
        rst!bwk_SentDate = Now()
        rst!bwk_Filelist = lngFileId
        rst!bwk_Contact = lngContactID
        rst!bwk_Employee = lngEmployeeId
        rst.Update
    End If
            strFileName = DLookup("Filename", "filelist", "bwk_Filelist = " & lngFileId)

    If Left(strFileName, 1) = "Q" Then ' it's a quote so show it as quoted
        EnquiryID = DLookup("bwk_Enquiry", "filelist", "bwk_Filelist = " & lngFileId)
        strSQL = "SELECT * FROM td_QuoteSituation WHERE bwk_Enquiry = " & EnquiryID
        Set rst = db.OpenRecordset(strSQL)

            'strSQL = "UPDATE td_QuoteSituation SET td_QuoteSituation!ft_QtStarted = Now() " & _
                 "WHERE (td_QuoteSituation!bwk_Enquiry) = " & EnquiryID
            strSQL = "UPDATE td_QuoteSituation SET td_QuoteSituation!ft_QtEmailed = Now() " & _
            "WHERE (td_QuoteSituation!bwk_Enquiry) = " & EnquiryID
            db.Execute strSQL, dbFailOnError
            strSQL = "UPDATE td_QuoteSituation SET td_QuoteSituation!ft_QtComplete = Now() " & _
            "WHERE (td_QuoteSituation!bwk_Enquiry) = " & EnquiryID
            db.Execute strSQL, dbFailOnError
            strSQL = "SELECT * FROM td_Enquiry WHERE bwk_Enquiry = " & EnquiryID
            Set rst = db.OpenRecordset(strSQL)
            blnQuoted = DLookup("Quoted", "td_Enquiry", "bwk_Enquiry = " & EnquiryID) '"bwk_Filelist = " & lngFileId)
         If blnQuoted = False Then
             If MsgBox("Show this enquiry as Quoted?", vbYesNo, "Set as Quoted on Enquiry") = vbYes Then ''****************

             strSQL = "UPDATE td_Enquiry SET td_Enquiry!Quoted = true " & _
             "WHERE (td_Enquiry!bwk_Enquiry) = " & EnquiryID
             db.Execute strSQL, dbFailOnError
             End If
         End If


    End If


    rst.Close
    Set rst = Nothing

    Call ClearUserTempLog ' this just sets some form fields in Access to "" eg Forms!frmUserLog!txtContactID = ""
    db.Close
    Set db = Nothing

    ws.Close
    Set ws = Nothing

    Set dbEng = Nothing

    End Sub

     =========================

    Portion of code with dlookups removed.
    --------------------
    If Not rst.NoMatch Then 'If there is an existing record copy it's id field

    lngFileID = rst.Fields("bwk_Filelist")
    strFileName = rst.Fields("Filename")
    EnquiryID = rst.Fields("bwk_Enquiry")
    rst.Close
    Set rst = db.OpenRecordset("td_DocSent", dbOpenDynaset)
    If rst.RecordCount = 0 Then ' if no record exists in td_DocSent then exit
    rst.Close
    Set rst = Nothing
    ' Call ClearUserTempLog
    db.Close
    Set db = Nothing
    ws.Close
    Set ws = Nothing
    Set dbEng = Nothing

    Exit Sub
    End If ' exit if no records
    'strCriteria = "bwk_Filelist =" & intFileId

    ' it is a new record add the details
    rst.AddNew
    rst!bwk_SentDate = Now()
    rst!bwk_Filelist = lngFileID
    rst!bwk_Contact = lngContactID
    rst!bwk_Employee = lngEmployeeId
    rst.Update
    End If
    'strFileName = DLookup("Filename", "filelist", "bwk_Filelist = " & lngFileID)

    If Left(strFileName, 1) = "Q" Then ' it's a quote so show it as quoted
    'EnquiryID = DLookup("bwk_Enquiry", "filelist", "bwk_Filelist = " & lngFileID)
    strSQL = "SELECT * FROM td_QuoteSituation WHERE bwk_Enquiry = " & EnquiryID
    Set rst = db.OpenRecordset(strSQL)

    'strSQL = "UPDATE td_QuoteSituation SET td_QuoteSituation!ft_QtStarted = Now() " & _
    "WHERE (td_QuoteSituation!bwk_Enquiry) = " & EnquiryID
    strSQL = "UPDATE td_QuoteSituation SET td_QuoteSituation!ft_QtEmailed = Now() " & _
    "WHERE (td_QuoteSituation!bwk_Enquiry) = " & EnquiryID
    db.Execute strSQL, dbFailOnError
    strSQL = "UPDATE td_QuoteSituation SET td_QuoteSituation!ft_QtComplete = Now() " & _
    "WHERE (td_QuoteSituation!bwk_Enquiry) = " & EnquiryID
    db.Execute strSQL, dbFailOnError
    strSQL = "SELECT * FROM td_Enquiry WHERE bwk_Enquiry = " & EnquiryID
    Set rst = db.OpenRecordset(strSQL)
    'blnQuoted = DLookup("Quoted", "td_Enquiry", "bwk_Enquiry = " & EnquiryID) '"bwk_Filelist = " & lngFileId)
    If rst.Fields("quoted") = 0 Then
    ------------

                                                                                     
    Tuesday, August 20, 2013 3:48 PM

Answers

  • Why not just copy the code in the event handler to a Public Sub with no arguments and call that from the button? If you have an open item the macro code can use Application.ActiveInspector.CurrentItem to get a mail item from the open item. You can then use any properties of the item you want. Just make sure that the item has been saved.

    For example, if there is one item open, a mail item, something like this:

    Public Sub MyMacro()

    Dim oMail As Outlook.MailItem

    Set oMail = Application.ActiveInspector.CurrentItem

    ' whatever else


    Ken Slovak MVP - Outlook

    Friday, August 23, 2013 6:07 PM
    Moderator

All replies

  • That's a lot of Access code to try to figure out, especially in an Outlook forum. Try reducing the code until it just causes the problem and then post that code for people to look at.

    Ken Slovak MVP - Outlook

    Tuesday, August 20, 2013 6:39 PM
    Moderator
  • Hi Ken

    I put it all in as in a previous forum (Access oriented) they asked to see it all.

    Basically there is nothing wrong with the code running it works. All it really does is run some sql toupdate an access recordset. But something is causing Access to hang around once it has run. If i close and restart Outlook it goes away. So I first looked to see if i was releasing the Access objects as i do when programming in Access as the front end. For example i have lots of code in Access which automates Word. If you do the necessary regarding references and closing/setting to nothing it all behaves well.

    In this instance however i am stumped.

    So i suspect somewhere in these bits of code i am missing something or it is an unusual Outlook problem.

    Someone suggested that if i remove all references to Access then it would work. That seems a bit extreme and i am not sure i could do what i need to do without it, i also have other code in Outlook modules that needs those references. I suppose if nothing strikes anyone as obvious i will strip out all the modules with the code that uses Access references and try and see how to do an update without them.

    If anyone has advice on that (should it come to it)  i would appreciate it.

    Regards

    Neil

    Private Sub updateAccess(lngFileId As Long, lngContactID As Long)
    Dim db As dao.Database
    Dim ws As Workspace
    Dim rst As dao.Recordset
    Dim dbEng As dao.DBEngine

    Set dbEng = Application.CreateObject("DAO.DBEngine.36")

         DBEngine.SystemDB = "\\OurServer\Southall\DATABASE\System1.mda"
         Set ws = DBEngine.CreateWorkspace("tasakoWS", "word", "word", dbUseJet)
         StrDBName = "\\OurServer\AccessBE$\tasako3.mdb"
         Set db = ws.OpenDatabase(StrDBName)

    Then the bits of code that update the db.

    Then closing code.

    rst.Close
    Set rst = Nothing


    db.Close
    Set db = Nothing

    ws.Close
    Set ws = Nothing

    Set dbEng = Nothing

    End Sub


    Thursday, August 22, 2013 11:47 AM
  • There is nothing that should keep Access open when using Access code from Outlook. I've done it many times. Setting my Access objects to Nothing when done with them has always been sufficient for me to correctly release Access.

    I don't see anything in the code that should be keeping Access open, but there's a lot more expertise with that in Access forums than here. There is no Outlook code at all in what you've shown, so I can't see how Outlook is even related to this. Even if there is Outlook code somewhere else in your project I can't see how that relates to Access staying open.

    I'm sorry, I can't help you with this.


    Ken Slovak MVP - Outlook

    Thursday, August 22, 2013 2:40 PM
    Moderator
  • Ken

    Code is running in outlook and is actioned on hitting send in outlook (this outlook session). It only causes Access to stay open if i run this bit of code to update Access. Other code i have runs fine. A real pain as it is a really usefull bit of automation and i was hoping to expand it.

    Hey ho.

    Thanks for looking at it.

    Neil

    Thursday, August 22, 2013 2:47 PM
  • Is that an item.Send() handler or an Application_ItemSend() handler? Does it work if the code is called from outside the send handler?

    Have you tried running in the debugger? Set a breakpoint in the code and make sure all the code you expect to run actually does run. Check in the debugger to see that all the objects that you expect to have been released are actually released.

    That's about all I can think of, sorry.


    Ken Slovak MVP - Outlook

    Thursday, August 22, 2013 3:36 PM
    Moderator
  • Ken

    It is Application_ItemSend() handler.

    I just spent 3 hours setting breakpoints and running the system to see what did and did not cause the problem.

    It boils down to this. Opening and closing the recordset! If i exclude this and open and close all the objects it behaves. As soon as i open and close a recordset it causes access to stay around?

    >Does it work if the code is called from outside the send handler?< Not sure how i can do this can you give me an idea? Do i need to attach some code to a button?

    Just had a thought. If the email is sitting in the outbox for 3 mins (which it does without me setting it as a priority) perhaps this causes the problem. But i am sure i have waited until it is sent. Will chek and revert.

    Thanks

    Neil

    Friday, August 23, 2013 1:26 PM
  • Ken

    The email being sent immediately or not made no difference.

    Neil

    Friday, August 23, 2013 1:33 PM
  • I have no idea why just opening and closing a recordset would cause Access to not close, that just sounds weird if the recordset is closed and the object is disposed of.

    What I meant by testing outside of the event handler could be done by calling the code from a button. It's worth trying just to eliminate anything caused by the event handler.

    I'd be curious to see if you actually set up an Access.Application object and either use that and Access code to work with the database, or just use the existing code and call Quit() on it when done, if that gets rid of the problem.


    Ken Slovak MVP - Outlook

    Friday, August 23, 2013 2:47 PM
    Moderator
  • Ken

    Trying to get the item send code on to a button seems not possible as it will not let me set

    Sub custom_Send(ByVal Item As Object, Cancel As Boolean)

    As a macro.

    Regards

    Neil

    Friday, August 23, 2013 3:46 PM
  • Ah it seems i can not have arguments?

    Not sure how to work round that.

    Friday, August 23, 2013 4:01 PM
  • Why not just copy the code in the event handler to a Public Sub with no arguments and call that from the button? If you have an open item the macro code can use Application.ActiveInspector.CurrentItem to get a mail item from the open item. You can then use any properties of the item you want. Just make sure that the item has been saved.

    For example, if there is one item open, a mail item, something like this:

    Public Sub MyMacro()

    Dim oMail As Outlook.MailItem

    Set oMail = Application.ActiveInspector.CurrentItem

    ' whatever else


    Ken Slovak MVP - Outlook

    Friday, August 23, 2013 6:07 PM
    Moderator