Properly open Outlook as an Application object in VBA

Published on
20,823 Points
2 Endorsements
Last Modified:
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.
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
    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
    WasOpen = False
    ' Create the Outlook session.
    Set objOutlook = CreateObject("Outlook.Application")
    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.
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
    '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!
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free