<

Go Premium for a chance to win a PS4. Enter to Win

x

Properly open Outlook as an Application object in VBA

Published on
14,649 Points
4,949 Views
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.
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
1 Comment
 
LVL 66

Expert Comment

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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Join & Write a Comment

This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
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…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month