Results 1 to 3 of 3
  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    OL Automation (2002 SP-2)

    I'm experiencing a problem with Outlook being "released" once the following code is run (On Timer event) from Access. It doesn't seem to matter if there is no new e-mail, but once a new e-mail is received (and processed by Access) it "freezes" OL (as if the OL window has lost focus). I have to re-start OL to get back to normal.
    <pre>Option Compare Database

    Dim objOLApp As Outlook.Application
    Sub ProcessMails()
    Dim objOLNameSpace As Outlook.NameSpace
    Dim objOLFolder As Outlook.MAPIFolder
    Dim objOLItem As Outlook.MailItem
    Dim intStartOutlook As Integer
    Dim strSubject As String
    Dim intPos As Integer
    Dim strProNo As String
    Dim strSQL As String
    Dim strReceived As String
    Dim lngNumItems As Long
    Dim i As Long

    ' Create Outlook object variable
    intStartOutlook = StartOutlook
    If intStartOutlook = 0 Then
    MsgBox "Outlook not available.", vbInformation
    Exit Sub
    End If

    On Error GoTo ErrHandler

    ' Set up Outlook variables
    Set objOLNameSpace = objOLApp.GetNamespace("MAPI")
    Set objOLFolder = objOLNameSpace.GetDefaultFolder(olFolderInbox).Par ent.Folders("Load Confirmations")
    lngNumItems = objOLFolder.Items.Count

    ' Loop through mail items
    For i = lngNumItems To 1 Step -1
    Set objOLItem = objOLFolder.Items(i)
    strSubject = objOLItem.Subject
    ' Check if subject contains PRO#
    intPos = InStr(strSubject, "PRO# ")
    If intPos > 0 Then
    ' Assemble SQL string
    strProNo = Val(Mid(strSubject, intPos + 5))
    strReceived = "#" & Format(objOLItem.ReceivedTime, "mm/dd/yyyy hh:mm AM/PM") & "#"
    strSQL = "UPDATE tblMaster SET blnCarrierConfirm=True, datReceived=" & _
    strReceived & " WHERE ProNo=" & strProNo & _
    " And (datReceived Is Null Or datReceived<" & strReceived & ")"
    ' Update record
    CurrentDb.Execute strSQL
    End If
    Next i

    ExitHandler:
    ' Clean up
    On Error Resume Next
    Set objOLItem = Nothing
    Set objOLFolder = Nothing
    Set objOLNameSpace = Nothing
    If intStartOutlook = 2 Then
    objOLApp.Quit
    End If
    Set objOLApp = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    Function StartOutlook() As Integer
    ' 0 = failed
    ' 1 = Outlook was already active
    ' 2 = Outlook started

    On Error Resume Next

    ' Default return value
    StartOutlook = 1

    Set objOLApp = Nothing
    ' Check if Outlook is active
    Set objOLApp = GetObject(, "Outlook.Application")
    If objOLApp Is Nothing Then
    ' No, so start Outlook
    Err.Clear
    Set objOLApp = CreateObject("Outlook.Application")
    StartOutlook = 2
    End If

    ' If still Nothing, we failed
    If objOLApp Is Nothing Then
    StartOutlook = 0
    End If
    End Function</pre>

    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: OL Automation (2002 SP-2)

    When Outlook seems to freeze, hold down Alt and press Tab a few times to see if there is an Outlook dialog that is waiting for user input.

  3. #3
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: OL Automation (2002 SP-2)

    Hans:

    Sorry for the delay. First, I had to spend some time slapping myself around a bit for having posted the wrong Module (brilliant!). Second, Alt + Tab revealed four "objects", OL "New Message of Interest", OL, Access, and my app. The "New message of Interest" comes from the rules for that folder, but has never displayed! So, I removed that rule and everything seems to be working fine right now. I did encounter a rather strange thing when closing OL after the rule change; Access was displaying "Syntax error in UPDATE statement". I closed Access and re-started OL and Access, sent a new message to myself and everything worked just fine. I believe the "New Message of Interest" was left over from before having the Access query the OL folder. Why it wasn't displayed is beyond me. The syntax error is even further beyond. Just to be clear, I am attaching the correct module.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •