On his blog Le Café Central de DeVa, blogger Deva Gnanam has more sample code for automating Outlook using VBA and the OOM (Outlook Object Model). In his latest post, Using Restrict method to get contacts based on specific categories, he shows us how to use the Restrict Method to filter a collection of ContactItems in order to look at only a subset of the original collection.
In The Wrong Way to Declare Application Objects, I wrote
When you are writing native VBA code for an Office Application (i.e. Outlook VBA code to be used inside Outlook), never use the New keyword with the Application Object.
You should, whenever possible, derive your Application Object from the native Outlook.Application Object when writing VBA code inside Outlook. It's just the simplest way to do it and avoids some of the Object Model Guard security prompts at the same time. Also, avoid using CreateObject and GetObject, they're unnecessary as well.
Well, it looks like Deva is at it again — here's the code from his post:
Dim oOutlook As Outlook.Application
Dim oNameSpace As Outlook.NameSpace
Dim oFolder As Outlook.MAPIFolder
Dim oContacts As Outlook.Items
Dim oContact As Outlook.ContactItem
Dim oItems As Outlook.Items
Dim oItem As Outlook.ContactItem
Dim i As Integer
' Connect with Outlook & get the Namespace
Set oOutlook = New Outlook.Application
Set oNameSpace = oOutlook.GetNamespace("MAPI")
' Retrieve the default Contacts Folder
Set oFolder = oNameSpace.GetDefaultFolder(olFolderContacts)
' Retrieve the Items collection
Set oContacts = oFolder.Items
' Create a Restriction on the Items
Set oItems = oContacts.Restrict("[Categories] = 'Friend'")
' Display the number of items that match the restriction
MsgBox "Found: " & oItems.Count
' Process each of the Restricted Items
For Each oItem In oItems
Debug.Print "Full Name : " & oItem.FullName & " Categories: " & _
oItem.Categories & "File as:" & oItem.FileAs
Next
' Clean it up
Set oItems = Nothing
Set oOutlook = Nothing
Notice the Set oOutlook = New Outlook.Application in the code above? There's that New keyword again!
In fact, all we need to do to "fix" this code is remove the New keyword. We could also remove the two unused declarations (the variables oContact and i). I won't even mention the inconsistent "clean up" section at the end of the code, where two objects are dereferenced even though five were created in the body of the code.
So my lesson is, for any given VBA-enabled program, whether it be Excel, Publisher, PowerPoint or Word, derive your Application-level Object from the intrinsic Application object native to each application. Don't use New, CreateObject or GetObject. Unless there really is a reason to?
About JP
7 Comments:
It's conceivable that you might want to open a new Excel instance when programming in Excel (or Word from Word), but I've done it perhaps twice in all of my programming experience. It's more likely that you'd want to open a fresh Word instance when programming from Excel.
If Outlook is running, you can't open a new instance anyway. Outlook and PowerPoint both only allow one instance at a time. When I looked at the sample code above, it looked like it was supposed to run in an app other than Outlook (though I wouldn't use New, I would use GetObject or CreateObject to use an existing instance, or if it isn't running, start a new instance of Outlook).
I doubt it, since it's an Outlook blog and most of the other topics revolve around Outlook. I would open a new instance of all apps except OL and PPT, to avoid disturbing the user if they already have an instance running.
Unless of course they clicked on a button that for example exports something from Excel to Word. Then I put it in the active document where the cursor is. If there's no active document I create one. And of course, if Word isn't running yet, I start it and open a document.
What about if someone is working on something separately in Word, then runs your Excel procedure. Wouldn't that export right into their current Word document?
Sure does, because the tooltip says "Export to cursor position in active Word document." Presumably they are working on the Word report and the Excel workbook with the supporting graphics. Write some text, locate the cursor, switch to Excel, select the chart, click the button. No complaints so far.
That's different, the user should be expecting it.
That's how it was designed. Other functions that work differently are designed differently. I was just bringing this up to show that it's not a hard and fast rule to use a different instance.