Office 2007 CreateItem(olMailItem) failing

ShosMeister · Dec 8th 2010

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:

Code

Enum olDefaultFolders
  olFolderCalendar = 9
  olFolderContacts = 10
  olFolderDeletedItems = 3
  olFolderDrafts = 16
  olFolderInbox = 6
  olFolderJournal = 11
  olFolderJunk = 23
  olFolderNotes = 12
  olFolderOutbox = 4
  olFolderSentMail = 5
  olFolderTasks = 13
  olPublicFoldersAllPublicFolders = 18
  olFolderConflicts = 19
  olFolderLocalFailures = 21
  olFolderServerFailures = 22
  olFolderSyncIssues = 20
End Enum
 
Enum olItemType
  olAppointmentItem = 1
  olContactItem = 2
  olDistributionListItem = 7
  olJournalItem = 4
  olMailItem = 0
  olNoteItem = 5
  olPostItem = 6
  olTaskItem = 3
End Enum
 
Private Sub Get_OutlookApp()
  Dim tmpResult As Integer
 
  Set olApp = Main.Get_ApplicationObject("Outlook.Application", True)
  If olApp Is Nothing Then
    tmpResult = Application.Run("Display_MsgBox", _
                                "Outlook Error", _
                                "Error creating Outlook instance. Can not continue.", _
                                vbCritical)
    End
  Else
    Set olNameSpace = olApp.getnamespace("MAPI")
  End If
 
End Sub
 
Private Sub Close_OutlookApp()
  Set olApp = Nothing
  Set olNameSpace = Nothing
  Set olItems = Nothing
  Set olMail = Nothing
End Sub

Display More

The macro is executed via a worksheet change function:

Code

'+------------------------------------------------------------------------------------------------+
'| Worksheet_Change()                                                                             |
'| This is fired any time a cell on the Master worksheet changes. Its function is to send out an  |
'| e-mail notification to the selected assessor.                                                  |
'|                                                                                                |
'| Assessor is contained in column E (which is column 5 numerically). If that is the column of    |
'| the target, then the data is collected and an e-mail sent. If not, then the entire function is |
'| skipped.                                                                                       |
'|                                                                                                |
'| MailTo is the address to send the e-mail to. It is contained in a defined name range on the    |
'| worksheet using the target cell's value as the lookup.                                         |
'|                                                                                                |
'| The DueDate is contained in column F of the target row and is formatted as mm/dd/yyyy for the  |
'| e-mail as the internal date format for Excel is a simple numeric value.                        |
'|                                                                                                |
'| BodyData is formatted as defined in the code to include the relavent data from the worksheet.  |
'|   Document Title - Column B                                                                    |
'|    Document Type - Column C                                                                    |
'|        Control # - Column A                                                                    |
'|     Comments Due - The above mentioned DueDate                                                 |
'|   Document Stage - Column D                                                                    |
'|                                                                                                |
'| Prior to starting an instance of Outlook is created. If that fails, the code will halt. Once   |
'| created, the e-mail object is created and the approriate data, as collected above, will be     |
'| formatted into the message. Once all formatting is complete, the e-mail will be sent directly  |
'| from the user's machine and Outlook account. Once completed, the instance of Outlook will be   |
'| closed.                                                                                        |
'+------------------------------------------------------------------------------------------------+
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 5 Then
    Dim BodyData As String
    Dim MailTo As String
    Dim DueDate As String
 
 
    ' Check to make sure the target contains a valid value. Do not send e-mail to N/A or blank
    ' If good, verify that all the relavent data to be included in the e-mail is present on the
    ' worksheet. If not, then don't send a partial e-mail.
    If (Target <> "N/A") And (Target <> "") Then
      If (Range("A" & Target.Row) <> "" And _
         Range("B" & Target.Row) <> "" And _
         Range("C" & Target.Row) <> "" And _
         Range("D" & Target.Row) <> "" And _
         Range("F" & Target.Row) <> "") Then
        Application.Run "Get_OutlookApp"
        Set olMail = olApp.CreateItem(olItemType.olMailItem)
        MailTo = Application.WorksheetFunction.VLookup(Target, Range("eMailLookup"), 2)
        DueDate = Format(Range("F" & Target.Row), "mm/dd/yyyy")
        BodyData = "The below referenced document has been assigned to you. " & _
                   "Please complete the review process by the suspense date." & Chr(13) & _
                   " ------------------------------------------------------" & Chr(13) & _
                   "Document Title: " & Range("B" & Target.Row) & Chr(13) & _
                   " Document Type: " & Range("C" & Target.Row) & Chr(13) & _
                   "    Control  #: " & Range("A" & Target.Row) & Chr(13) & _
                   "  Comments Due: " & DueDate & Chr(13) & _
                   "Document Stage: " & Range("D" & Target.Row) & Chr(13) & Chr(13)
        With olMail
         .Recipients.Add MailTo
         .Subject = "GE333 Assessment: " & DueDate
         .body = BodyData
         .send
        End With
      End If
      Application.Run "Close_OutlookApp"
    End If
  End If
End Sub

Display More

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!!

ShosMeister · Jul 8th 2011

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.

cytop · Jul 8th 2011

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....

ShosMeister · Jul 11th 2011

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:

Code

'+------------------------------------------------------------------------------------------------+
'| This will be used to determine if an instance of an application is running. For this specific  |
'| workbook (as of Aug 2010), the only application we are concerned with is Outlook.              |
'|                                                                                                |
'| strApp is a string containing the application to search for (i.e. "Outlook.Application")       |
'| bCreate is a boolean used to determine if the object should be created if not found:           |
'|    True - create the object and return it or null if creation failed                           |
'|    False - don't create the object. Return null if an instance does not exist.


Function Get_ApplicationObject(strApp As String, bCreate As Boolean) As Object
  Dim tmpApp As Object
  
  On Error Resume Next
  Set tmpApp = GetObject(, strApp)
  If tmpApp Is Nothing Then
    If bCreate = True Then
      Set tmpApp = CreateObject(strApp)
      If tmpApp Is Nothing Then
        tmpApp = Null
      End If
    Else
      tmpApp = Null
    End If
  End If
  On Error GoTo 0
  Set Get_ApplicationObject = tmpApp
End Function

Display More

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

Office 2007 CreateItem(olMailItem) failing

Participate now!

Share