Office 2007 CreateItem(olMailItem) failing

  • I used resources from here, MSDN and others to create a VBA macro in Excel to send an e-mail using data from the worksheets. It's been working fine in Office/Outlook 2003 for about 1 year. I was out of the office and, upon my return, was told it's no longer working. I did check the code against my backup and nothing has changed. So I'm guessing that a security patch may have been pushed out which is causing it to fail and/or we are now using Office 2007 and that is the culprit. Either way, I can not determine why it has stopped and would appreciate any help you can provide.

    Details: I'm using late binding to the Outlook object so there would be no issues with other users not having the proper references installed/available.

    The following are global definitions:

    Code
    Public olApp As Object
    Public olNameSpace As Object
    Public olItems As Object
    Public olMail As Object



    The basic parts of getting the Outlook instance. This works. If Outlook is already open, it simply returns with a reference to it. If it's not open, it opens it and then returns the reference:



    The macro is executed via a worksheet change function:



    The olMail object is what is not being created but I am unable to determine why.

    Code
    Set olMail = olApp.CreateItem(olItemType.olMailItem)


    When I look at olMail stepping through the code, it is returning a null. Therefore, subsequent code to actually populate the message with recipients and the body fail.

    I have seen numerous threads elsewhere no the net indicating the same situation but none of those seem to have been resolved. They all involved the exact circumstances: Office 2007 and CreateItem(olMailItem) failing to return a valid object.

    Thanks much!!

  • Re: Office 2007 CreateItem(olMailItem) failing


    Still trying to resolve this issue - been working around by running on Office 2003 version workstation. I've seen some other e-mail related VBA and instead of using .Recipients.Add, they just use the .To= to set the recipients e-mail addresses. Have not been able to test yet, but, wanted to see if anyone else had any other ideas.

  • Re: Office 2007 CreateItem(olMailItem) failing


    Can't test what you have as you didn't include the code for Main.Get_ApplicationObject. However, it did work using a plain vanilla

    Code
    GetObject(  ,"Outlook.application")

    and changing the

    Code
    '.Recipients.Add' -- to -- '.To'

    As a matter of interest, the '.Send' will display the default Outlook security prompt forcing the users to have to wait 5 seconds before the mail can be sent (manually!). Much better in my opinion to use '.Display' and let the user click Send....

  • Re: Office 2007 CreateItem(olMailItem) failing


    Sorry for omitting that code. It actually does what you did, just gets the object. Included here for completeness:


    Thanks for confirming the change of .Recipients to .To. Any ideas why though?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!