<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Properly open Outlook as an Application object in VBA

Published on
18,352 Points
8,652 Views
2 Endorsements
Last Modified:
Approved
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
Comment
Author:Nick67
3 Comments
LVL 67

Expert Comment

by:Jim Horn
Very well laid out, and relates to a current business need.  Voting yes.
0

Expert Comment

by:John Davy
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
0

Expert Comment

by:Abraham Gumba
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
0

Featured Post

Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Join & Write a Comment

This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Learn the basics of Skype For Business in office 365

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month