Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Outlook Automation from Access (2003 SP2)

    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!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Outlook Automation from Access (2003 SP2)

    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)

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook Automation from Access (2003 SP2)

    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 <!post=488173,488173>488173<!/post>.)

    Thanks.
    Attached Images Attached Images

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Outlook Automation from Access (2003 SP2)

    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.

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook Automation from Access (2003 SP2)

    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.

  6. #6
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook Automation from Access (2003 SP2)

    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("Glob al Address List").AddressEntries(<Outlook E-Mail Name>)) 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.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Outlook Automation from Access (2003 SP2)

    Thanks for sharing this!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •