Microsoft Excel
Back to jkp-ads.com

Ron de Bruin
Excel Automation

Microsoft MVP Program

Test if Outlook is open and open Outlook with VBA

Important message to visitors of this page

Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.

Luckily, Ron was kind enough to allow me to publish all of his Excel content here.

Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.

Kind regards

Jan Karel Pieterse


If you want to test if Outlook is open with VBA code it is not so difficult as the example below shows:

Sub TestOutlookIsOpen()
    Dim oOutlook As Object

    On Error Resume Next
    Set oOutlook = GetObject(, "Outlook.Application")
    On Error GoTo 0

    If oOutlook Is Nothing Then
        MsgBox "Outlook is not open, open Outlook and try again"
    Else
        'Call NameOfYourMailMacro
    End If
End Sub

But if you want to open it when Outlook is closed with VBA it is not so difficult if you use Early Binding but not as easy if you use Late Binding in order for your code to run on different versions without issues. For example, this code below will work to open Word if it is closed and make it visible using Late binding.

Sub TestMe()
Dim word As Object
Dim doc As Object
On Error Resume Next
Set word = GetObject(, "word.application") 'gives error 429 if Word is not open
If Err.Number = 429 Then
  Err.Clear
  Set word = CreateObject("Word.Application")
End If
If Not word Is Nothing Then
   word.Visible = True
   'Set doc = word.Documents.Add
Else
   MsgBox "Unable to retrieve Word."
End If
End Sub
But you can’t use similar code with Outlook due to how Outlook opens which is quite different from other Office products. Sometimes it is important to know that Outlook is open, for example to be sure that your mails created with VBA code are sent and not waiting in your outbox.

The code below is created by MVP Ben Clothier and can either retrieve an open instance of Outlook or open Outlook if it is closed. This uses a ‘self-healing object’ approach for returning an instance of Outlook. If you want to know more about self-healing objects, read this article :

http://accessexperts.com/blog/2011/07/26/self-healing-objects/

 

Test the code

Copy the code below into a new module of your workbook. To test the code, run the macro named MyMacroThatUseOutlook below the two functions. If everything works correctly, the Outlook will open minimized. If you prefer to open Outlook maximized, change the parameter WindowState’s default from olMinimized to olMaximized like this Set OutApp = OutlookApp(olMaximized)

#Const LateBind = True

Const olMinimized As Long = 1
Const olMaximized As Long = 2
Const olFolderInbox As Long = 6

#If LateBind Then

Public Function OutlookApp( _
    Optional WindowState As Long = olMinimized, _
    Optional ReleaseIt As Boolean = False _
    ) As Object
    Static o As Object
#Else
Public Function OutlookApp( _
    Optional WindowState As Outlook.OlWindowState = olMinimized, _
    Optional ReleaseIt As Boolean _
) As Outlook.Application
    Static o As Outlook.Application
#End If
On Error GoTo ErrHandler
 
    Select Case True
        Case o Is Nothing, Len(o.Name) = 0
            Set o = GetObject(, "Outlook.Application")
            If o.Explorers.Count = 0 Then
InitOutlook:
                'Open inbox to prevent errors with security prompts
                o.Session.GetDefaultFolder(olFolderInbox).Display
                o.ActiveExplorer.WindowState = WindowState
            End If
        Case ReleaseIt
            Set o = Nothing
    End Select
    Set OutlookApp = o
 
ExitProc:
    Exit Function
ErrHandler:
    Select Case Err.Number
        Case -2147352567
            'User cancelled setup, silently exit
            Set o = Nothing
        Case 429, 462
            Set o = GetOutlookApp()
            If o Is Nothing Then
                Err.Raise 429, "OutlookApp", "Outlook Application does not appear to be installed."
            Else
                Resume InitOutlook
            End If
        Case Else
            MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Unexpected error"
    End Select
    Resume ExitProc
    Resume
End Function

#If LateBind Then
Private Function GetOutlookApp() As Object
#Else
Private Function GetOutlookApp() As Outlook.Application
#End If
On Error GoTo ErrHandler
    
    Set GetOutlookApp = CreateObject("Outlook.Application")
    
ExitProc:
    Exit Function
ErrHandler:
    Select Case Err.Number
        Case Else
            'Do not raise any errors
            Set GetOutlookApp = Nothing
    End Select
    Resume ExitProc
    Resume
End Function

Sub MyMacroThatUseOutlook()
    Dim OutApp  As Object
    Set OutApp = OutlookApp()
    'Automate OutApp as desired
End Sub

 

When you want to automate Outlook in your macros and you don’t want to worry about whether Outlook is open already, you can use code like in the macro MyMacroThatUseOutlook above:

For more Outlook mail examples where you can use the code from this page visit my site : https://jkp-ads.com/rdb/win/s1/outlook/mail.htm