Outlook Automation from Access (2003 SP2)

Topic

New Reply

I use VBA automation in Access to send e-mail messages via Outlook. I first look for an existing instance of Outlook (using GetObject) and use it if found. If not found, then I create an instance (using CreateObject) — in this case I attempt to remove the instance when I’m done (using appOutlook.Quit and Set appOutlook = Nothing). The problem is that when I send multiple e-mails, I end up with multiple instances of Outlook (as viewed in Windows Task Manager). So, it seems, either GetObject is not detecting the existing instance of Outlook and/or the appOutlook.Quit command is not removing the instance that I created. Is there some bit of “object housekeeping” I’m missing that’s causing this “instance bloat”?

Thanks (in advance) for any help!

Reply | Quote

Replies

Check your code very carefully for unqualified references to Outlook objects/methods. For example, the instruction

Set objNS = GetNamespace(“MAPI”)

uses GetNameSpace without specifying that it belongs to appOutlook, so it may create a new instance. It should be

Set objNS = appOutlook.GetNamespace(“MAPI”)

Similarly, the instruction

Set objMail = CreateItem(olMailItem)

may create a new instance of Outlook, it should be

Set objMail = appOutlook.CreateItem(olMailItem)

Reply | Quote

Hans,

This is getting “interesting”… I checked the code carefully and did not find any unqualified references. Investigating further, I opened up Task Manager and watched it as I stepped through my “Send Mail” routine. If Outlook was already open, things work fine… the existing instance is detected and used and left open at the end. However, if Outlook is not initially open, my routine generates the instance, but when it executes to the appOutlook.Quit command, the instance remains in Task Manager. When I run the routine again, GetObject does not see this residual instance, so CreateObject generates another one. appOutlook.Quit doesn’t close this one either. This continues as more e-mails are sent…

Here’s where it gets interesting… I Googled “multiple instances of Outlook” and found this article which describes an apparent bug in SpamBayes (which I have running as an add-on to my Outlook) causing multiple instances of Outlook when “mailto” links are used. They claimed that if SpamBayes is disabled in Outlook (Tools…Otpions…Other…Advanced Options…COM Add-Ins…), the problem goes away. On a whim, I tried this and, lo and behold, my multiple instance problem went away! (Curiously, the created instance is not removed from Task Manager immediately after executing the appOutlook.Quit command but is removed once the subsequent End Sub command is executed.)

That’s the good news… The bad news is that now that SpamBayes is disabled, I get that annoying “A program is trying to access e-mail addresses you have stored in Outlook. Do you want to allow this? … ” message… (attached) for each e-mail generated. (SpamBayes apparently circumvented this message.) Is there any way yet to disable this message at the end-user level? (I’m checking with our Microsoft Exchange Server administrator to see if they can do something about this at their level; e.g. one of the solutions in your post 488173.)

Thanks.

Reply | Quote

I must be lucky. I use SpamBayes, but neither Automation code nor using mailto: links cause multiple instances of Outlook.
Oh wait, I’m using Outlook 2002 – apparently the problem occurs in Outlook 2003 only.
Unfortunately, development of SpamBayes seems to have come to a standstill, so I doubt the bug you experience will be eradicated.

ClickYes (mentioned in the same post you refer to) is the easiest way to suppress the “A program is trying to…” warnings.

Reply | Quote

I’d like to use ClickYes, but I fear deploying it in our rather controlled network environment may be an uphill battle. One consolation is that if Outlook is open to begin with, I don’t get the “A program is trying to…” warnings and, as noted in my previous post, the multiple instances are also avoided even if SpamBayes is running. This might be the lesser of the various evils. (The Network Bosses have not yet said “No” to configuring Exchange Server to bypass this annoyance, but I don’t expect them to deliver anything real soon.

Thanks again for your help.

Reply | Quote

Hans,

Progress…

No word yet from the network guys, but I’ve found an acceptable workaround, which I think may be helpful for others faced with this annoyance.

As described, I get the annoying alert when Outlook is not already open (and SpamBayes is disabled). However, one of my applications did not generate the alert when sending e-mail. I compared the code side-by-side and discovered a difference. If I add recipients to the mail message object using their Outlook name only (the Object Browser indicates the Add method for the Recipients collection needs just a string argument), the subsequent .Send method generates the alert. However, if I extract an AddressEntry object out of the namespace object (e.g., Set objAddressEntry = appOutlook.GetNamespace(“MAPI”).AddressLists(“Global Address List”).AddressEntries()) and then use this as the argument for the .Add method (e.g., Set objRecipient = objMailItem.Recipients.Add(objAddressEntry)), then the subsequent .Send method does NOT generate the alert.

Reply | Quote

Thanks for sharing this!

Reply | Quote