Properly open Outlook as an Application object in VBA

Nick67
CERTIFIED EXPERT
Published:
Updated:
You can go ahead and Google this
 
Set objOutlook = CreateObject("Outlook.Application")

Open in new window

It looks SO simple to fire up Outlook to do something via VBA automation.
Throw in a reference to the Microsoft Outook XX.0 library and get busy
  • Create contacts from a spreasheet or database
  • Save attachments and log what's been done.
The list is endless for WHY you'd want to automate Outlook. And so you do it, just like it looks up there in the code snippet.

And then, the first time you run that code with Outlook open, you'll get a nice Error 429
Run-time error '429': ActiveX component can't create object
 

'Well, crud!'


You'll look around on the Internet and discover that error occurs because, really, only ONE instance of Outlook is meant to be open at a time.  And so you'll remember to manually close Outlook before you run your code -- until you get sick of that. And you'll poke around a little harder at the internet and you'll find
 
Set objOutlook = GetObject(, "Outlook.Application")

Open in new window

This gets the open instance of Outlook for your use.
Excellent.
Right up until you didn't have Outlook open again.  And then you're back to 429 again
 

'Double crud!'


So, you'll growl and curse and do something like
 
On Error Resume Next
                      Dim objOutlook As Outlook.Application
                      Set objOutlook = GetObject(, "Outlook.Application")
                      
                      If Err.Number = 429 Then
                          Err.Clear
                          Set objOutlook = CreateObject("Outlook.Application")
                      End If

Open in new window

And you'll pat yourself on the back for a job well done...
Until the day you come to recall exactly WHY
On Error Resume Next
is such a bad idea.

That day when some other error besides our friend 429 happens and cascades down your code causing you grey and lost hair as you try to find the real cause instead of the point where your code couldn't ignore its own errors anymore. And on that day, when you decide to get rid of that pesky On Error Resume Next statement, you'll realize that you are stuck.

Because, with Outlook, that 429 error is a way of life.

So what to do?
Minimize the damage that On Error Resume Next can do!

VBA is perfectly capable of creating functions that return objects like Outlook.Application objects
Like this function:
 
Option Explicit
                      Public WasOpen As Boolean
                      Private Function FireOutlook() As Outlook.Application
                      On Error Resume Next
                      Dim objOutlook As Outlook.Application
                      
                      Set objOutlook = GetObject(, "Outlook.Application")
                      'MsgBox Err.Number & " " & Err.Description
                      If Err.Number = 429 Then
                          Err.Clear
                          WasOpen = False
                          ' Create the Outlook session.
                          Set objOutlook = CreateObject("Outlook.Application")
                      Else
                          WasOpen = True
                      End If
                      Set FireOutlook = objOutlook
                      End Function

Open in new window


Which you can then use in your big, ugly important sub that you certainly don't want On Error Resume Next in!
And nice and simply, too
 
Dim objOutlook As Outlook.Application
                      Set objOutlook = FireOutlook()

Open in new window

You may have noticed WasOpen

If Outlook was open, then you have an Outlook UI (mail, calendars, contacts, etc) in existence already.
Great!
But if it wasn't, then you'll need to get the Outlook UI up and going.
For mail items, that's where WasOpen comes into play
 
Dim ns As Outlook.Namespace
                      Dim Folder As Outlook.MAPIFolder
                      Set ns = objOutlook.GetNamespace("MAPI")
                      Set Folder = ns.GetDefaultFolder(olFolderInbox)
                      Set objOutlookExplorers = objOutlook.Explorers
                         
                      If WasOpen = False Then
                          objOutlook.Explorers.Add Folder
                          Folder.Display
                          'done opening
                      End If

Open in new window


So to recap, automating the opening of Outlook for use in VBA code is going to lead to unavoidable 429 errors, but On Error Resume Next plays havoc with complex code. The solution is to isolate the opening of Outlook into its own function to contain the error, and return the Outlook object to the procedure that needed it. Whether you calling VBA code comes from Word, Excel, Access or some other source -- even VBScript -- the code demonstrated is THE correct way to open Outlook.

And while I suppose that there are some folks who would prefer a code sample with Late Binding, if you are sophisticated enough to know what that is and want it, then you are sophisticated enough to change the Dim statements to Objects and suss out the numeric equivalent of the Outlook constants in use.  Me -- I like Intellisense!
 
2
15,459 Views
Nick67
CERTIFIED EXPERT

Comments (3)

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Very well laid out, and relates to a current business need.  Voting yes.
I did not follow everything for I am still learning. Can I get a copy of all code needed to open Outlook?  I do want to studt it but need to make sure I have it working.  Thank you  john@my-planet-earth.com
Alternatively, the following code handles Error 429 in particular, and stops on other errors.

On Error GoTo Err_Handler
...
...

Err_Handler:
    If Err.Number = 429 Then
        Err.Clear
        Set oOutlookApp = GetObject(, "Outlook.Application")
        Resume Next
    Else
        MsgBox Err.Number & " " & Err.Description
        Resume Exit_Handler
    End If

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.