[AccessD] Open Outlook from Access - Then Set Foreground back to Access

Folks,  

I wanted to share something I figured out (partly with help from this group)
that works pretty well.  My database sends email frequently, so a user
should have Outlook open whenever the db is open.  The following will check
to see if Outlook is open - if not then Outlook will be opened.  The next
step is to set the foreground window back to the db which opened Outlook.
It's all pretty smooth in operation, and avoids potential errors when email
needs to be sent.  Perhaps someone else can use this . . .

Dan Waters


THIS SUB IS CALLED FROM THE STARTUP FORM (WHICH IS NOT VISIBLE AND CLOSES
WHEN DONE)
Private Sub OpenOutlook()
On Error GoTo EH

    Dim objOutlook As Object
    Dim stgApplication As String
    Dim lngHandle As Long
    
    '-- Path to Outlook
    stgApplication = "C:\Program Files\Microsoft
Office\Office10\Office10\OUTLOOK.EXE"
    
    '-- check to see if Outlook is open.  If not then error 429 occurs
    Set objOutlook = GetObject(, "Outlook.Application")
    MblnOutlookAvailable = True 
    
    Exit Sub
    
EH:
    Application.Echo True
    GlngErrNumber = Err.Number
    GstgErrDescription = Err.Description
    Select Case GlngErrNumber
        Case 429
            Call Shell(stgApplication, 1)
            MblnOutlookAvailable = True
        Case Else
            Call GlobalErrors("", GlngErrNumber, GstgErrDescription,
CurrentObjectName, "OpenOutlook")
    End Select
    
End Sub


THESE WINDOWS API'S ARE NEEDED.  PUT THESE IN A STANDARD MODULE.
Public Declare Function SetForegroundWindow Lib "user32" (ByVal hwnd As
Long) As Long
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)


THIS IS THE OPEN EVENT FOR THE MAIN FORM OF THE DATABASE.
Private Sub Form_Open(Cancel As Integer)
On Error GoTo EH

    Dim lngMeHandle As Long
    
    '-- This will ensure that Access is in the foreground if Outlook had to
be opened. _
        A Sleep time is needed to let it work.  1/2 second seems OK.
    lngMeHandle = Me.hwnd
    Sleep 500
    lngMeHandle = SetForegroundWindow(lngMeHandle)

    Exit Sub

EH:
    Application.Echo True
    Call GlobalErrors("", Err.Number, Err.Description, CurrentObjectName,
"Form_Open")

End Sub