Error Handling Loop...Stuck Inside!

[Soule]

I'm not sure why this code is hanging up. When my user clicks a form button, I'm simply trying to check to see if Outlook is open, and if it isn't, display a message box reminding them to open Outlook. If it is already open, I (obviously) want the code to skip the error code and continue.

My environment is:
- Access 2007
- .accdb file
- Am in datasheet view when stepping through
- Early binding
- Libraries:
VB for Apps
MS Access 12.0 Obj. Lib.
MS OFC 12.0 Access db engine Obj. Lib.
MS VB for Apps Extensibility 5.3
MS VBScript Regular Expressions 5.5
MS Outlook 12.0 Obj. Lib.
MS ActiveX Data Objects 2.8 Lib.
OLE Automation

The code is hanging up on the "Exit Sub" line.

When Outlook is closed: Hangs up on the "Exit Sub" when it should be going to SECT1_ERR.

When Outlook is open: Does the same thing, just runs faster.

Here it is, along with the section that comes after it:

 
Option Explicit
Option Compare Database
 
Public Sub A1S1_Form_Re_send_Welcome_E_Mail_Only_Button_Click()
 
On Error GoTo SECT1_ERR:
Dim OlApp As Object
 
Set OlApp = GetObject("", "Outlook.Application")
 
' Debug.Print "See If Outlook Is Closed", Err.Number, Err.Description
 
Exit Sub
 
SECT1_ERR:
MsgBox "MS Outlook not open." & vbCrLf & "Please open Outlook, then hit automation button again." & vbCrLf & "Error #: " & Err.Number & " - " & Err.Description, vbExclamation + vbOKOnly, "Open Outlook"
 
Resume Next
 
' Template is now retrieved to base e-mail on...
 
On Error GoTo SECT2_ERR
 
Dim objOutlookMsg As Outlook.MailItem
 
Set objOutlookMsg = OlApp.CreateItemFromTemplate("J:\Database Work\A1 Tracking DB & Related\A1 Form Button Automation Email Templates\Employee A1 Welcome Outlook Template.oft")
 
' Debug.Print "Open template object", Err.Number, Err.Description
 
SECT2_EXIT:
 
Exit Sub
 
SECT2_ERR:
MsgBox "Error opening Outlook template object." & vbCrLf & "Error #: " & Err.Number & " - " & Err.Description, vbExclamation + vbOKOnly, "Open Outlook Template"
 
Resume SECT2_EXIT

When I take out the "Exit Sub", the error IS caught and the MsgBox pops, but then I get stuck in the loop of Resume Next-->GetObject-->MsgBox.

If Outlook is already opened or opened by the user after a first message instance, I don't see why my code won't continue from the "GetObject" statement to "Exit Sub" and on to the next section (SECT2) of my code....? No versions I'm trying work.


Note: When I add a "SECT1_EXIT" before the error handling and a "Resume SECT1_EXIT" after it, code hangs up in the same place - on "SECT1_EXIT", when Outlook is open or not.

Note: I previously tried the CreateObject method to create an instance of Outlook if it was closed, but to no avail (got error 438-object doesn't support this property/method).

Thank you for looking at this problem. It is appreciated. Any advice will further be!

Frank



This post is also here: http://bytes.com/topic/visual-basic/...de#post3703866 and here http://www.access-programmers.co.uk/...09#post1134109

[ssanfu]

This line should not have the quotes

Set OlApp = GetObject("", "Outlook.Application")

I couldn't completely test this code, but it should be close:

Public Sub A1S1_Form_Re_send_Welcome_E_Mail_Only_Button_Click()

   Dim OlApp As Object
   Dim objOutlookMsg As Outlook.MailItem

   Set OlApp = GetObject(, "Outlook.Application")
   ' Debug.Print "See If Outlook Is Closed", Err.Number, Err.Description

   If Err.Number <> 0 Then
      MsgBox "MS Outlook not open." & vbCrLf & "Please open Outlook, then hit automation button again." & vbCrLf & "Error #: " & Err.Number & " - " & Err.Description, vbExclamation + vbOKOnly, "Open Outlook"
      Exit Sub
   End If

   Err.Clear

   ' Template is now retrieved to base e-mail on...
   Set objOutlookMsg = OlApp.CreateItemFromTemplate("J:\Database Work\A1 Tracking DB & Related\A1 Form Button Automation Email Templates\Employee A1 Welcome Outlook Template.oft")

   ' Debug.Print "Open template object", Err.Number, Err.Description
   If Err.Number <> 0 Then
      MsgBox "Error opening Outlook template object." & vbCrLf & "Error #: " & Err.Number & " - " & Err.Description, vbExclamation + vbOKOnly, "Open Outlook Template"
   End If


End Sub

[Soule]

Thanks, Ssanfu, for taking the time to not only look at the code, but examine and re-write it! I've tried to do a procedure that checks for an instance of Outlook and opens one if non-existant over 10 different ways, but I can't get it to work in this corporate environment. I give up. I don't want to think what would happen if I re-registered Outlook through regserver at the prompt. The users will just have to pass the word around that they have to open Outlook first before hitting any automation buttons. Thank you.

Frank

[ssanfu]

I made another run at it. I put this in a standard module and ran tests.
Try this code:

Public Sub TestForOutlook()
   On Error GoTo HandleError

   Dim OlApp As Object
   Dim objOutlookMsg As Outlook.MailItem

   Dim ThisError As Long
   Dim ThisErrDesc As String

   Set OlApp = GetObject(, "Outlook.Application")

   ' Template is now retrieved to base e-mail on...
   Set objOutlookMsg = OlApp.CreateItemFromTemplate("J:\Database Work\A1 Tracking DB & Related\A1 Form Button Automation Email Templates\Employee A1 Welcome Outlook Template.oft")


   Exit Sub

HandleError:
   'save error info
   ThisError = Err.Number
   ThisErrDesc = Err.Description
   
   Select Case ThisError
      Case 429
         MsgBox "MS Outlook not open." & vbCrLf & vbCrLf & "Please open Outlook, then hit automation button again." & vbCrLf & vbCrLf & "Error #: " & ThisError & " - " & ThisErrDesc, vbExclamation + vbOKOnly, "Open Outlook"
      Case -2147287037    '  change this to the error number when the template can't be opened.
         MsgBox "Error opening Outlook template object." & vbCrLf & vbCrLf & "Error #: " & ThisError & " - " & ThisErrDesc, vbExclamation + vbOKOnly, "Open Outlook Template"
   End Select

   Err.Clear
End Sub

When Outlook in not running I get the error.
When Outlook is running, I get no error.
When Outlook is running and try to open the template, I get an error

[Soule]

Thanks, again, Ssanfu! I realized I was being TOO conservative with my error handling, and decided to go with one direction label at the beginning of my Sub and the error handler at the end of it...necessitating only one Exit Sub statement before the handler and End Sub statement. It was overly complicated before.

I couldn't get a CreateObject statement to work for me with a form in this environment, so I chucked it for now. I'll try it again later. The users may just have to use old school word of mouth that their automated messages won't actually be sent until they open their copy of Outlook.

Thank you for your time!

Frank