Excel VBA Outlook16 cannot read MailItem .Body property, though Outlook14 can

Question

I have taken on a spreadsheet that has a VBA routine to read outlook emails

It works fine for me on Excel2010 (using the Outlook Office14.0 Object library) but now doesnt work for my colleague who's on Excel2016 (he's referenced the Outlook Office16.0 Object library in the VBA references), here's the key bits of code:

Dim olItms As Outlook.Items, Dim olMail As Variant,

For Each olMail In olItms
     mailContents() = Split(olMail.Body, Chr(13))

I can add a Watch and see all of the emails in the chosen folder are in the olItms array I can view the properties for each olMail object, eg sender & time received, all look fine. In my Excel2010 I can read the .Body property and write it to Excel etc

In his Excel2016 I can similarly add a Watch and see all of the emails I can similarly view the properties for each olMail object However I cannot read the .Body property, it shows as <> instead of the text and nothing is read In his Excel2016 session I can use the VBA to open/activate the email I can also write to the .Body property in the VBA, eg olMail.Body = "test text" works, replacing the body of text in the open/activate email with "test text" However I still can't read the body text.
The other similar fields (HTMLBody, RTFBody) similarly show as <> with no text read
I can't see anything in his Outlook properties that could be restricting it The emails definitely have body text in them, as they get read ok in my Excel2010
The Outlook16 object libary must be working ok as the other email properties are reading ok (unless it could be partly working ?)

Here's a copy of all the code up to the error point (with some names changed)

Sub GetIncomeUpdatesFromInbox()
     
    Dim olApp As Outlook.Application
    Dim olNs As Outlook.Namespace
    Dim olFldr As Outlook.MAPIFolder
    Dim olMailbox As Outlook.MAPIFolder
    Dim olItms As Outlook.Items
    Dim olMail As Variant, vRow As Variant
    Dim i As Long
    Dim FolderAddress As String, arrFolders() As String, mailContents() As String
    
    Dim EarliestDate As Date
     
    Set olApp = New Outlook.Application
    Set olNs = olApp.GetNamespace("MAPI")
    On Error Resume Next
  
    Set olMailbox = olNs.Folders("mailbox@company.com").Folders("Inbox")
    
        'Produces the relevant folder as a string
        If Range("FolderAddress") = "Update" Or Range("FolderAddress") = "Create" Then
            FolderAddress = "\\mailbox@company.com\*Folders\Data\xxx\"
        Else
            FolderAddress = "\\mailbox@company.com\*Folders\Data\xxx\Update\"
        End If
        
        FolderAddress = FolderAddress + Range("FolderAddress")
    
        'changes Folder address into an array
        
        arrFolders() = Split(FolderAddress, "\")
        
        'Enters first part of fodler address
        
        Set olFldr = olNs.Folders.Item(arrFolders(2))

    
    'Navigates to relevant folder
    
    If Not olFldr Is Nothing Then
        For i = 3 To UBound(arrFolders)
            Set colFolders = olFldr.Folders
            Set olFldr = Nothing
            Set olFldr = colFolders.Item(arrFolders(i))
            If olFldr Is Nothing Then
                Exit For
            End If
        Next
    End If

    Application.DisplayStatusBar = True
    
    
    Set olItms = olFldr.Items
     
     'Sorts emails by date received
     
    olItms.Sort “Received”
     
    i = 1
    
    UserForm1.TextBox1 = Format(CDate(Evaluate("WORKDAY(TODAY(),-1)")), "dd/mm/yyyy")
    UserForm1.TextBox2 = Format(CDate(Evaluate("WORKDAY(TODAY(),-0)")), "dd/mm/yyyy")
    UserForm1.Show
    
    EarliestDate = UserForm1.TextBox1
    LatestDate = UserForm1.TextBox2
         
     'moves through mails one by one for all emails received after specified earliest date"
     
    iColumn = 3
    
    For Each olMail In olItms
        If LatestDate > CDate(olMail.ReceivedTime) Then
            If CDate(olMail.ReceivedTime) > EarliestDate Then
            
                'Splits content of the mail into an array with each element of the array one line in the original email
                mailContents() = Split(olMail.Body, Chr(13))

Answer 1

Try to use the GetInspector or Display method before getting the message body.

Another point is a security trigger in the Outlook object model. Outlook may restrict access to secure properties when you automate the host from another process. You may try to run the same code from a COM add-in where you deal with a safe Application instance which doesn't trigger a security issue. There are several ways for suppressing such issues when dealing with OOM:

1. Use a third-party components for suppressing Outlook security warnings/issues. See Security Manager for Microsoft Outlook for more information.
2. Use a low-level API instead of OOM. Or any other third-party wrappers around that API, for example, Redemption.
3. Develop a COM add-in which has access to the trusted Application object. And then communicate from a standalone application with an add-in using standard .Net tools (Remoting).
4. Use group policy objects for setting up machines.
5. Install any AV software with the latest databases (up to date).

---

There are other aspects in the code listed above. Let's cover them in depth.

1. Instead of using the following code:

   Set olMailbox = olNs.Folders("mailbox@company.com").Folders("Inbox")
   

   You need to use the GetDefaultFolder method of the Namespace or Store class which is similar to the GetDefaultFolder method of the NameSpace object. The difference is that this method gets the default folder on the delivery store that is associated with the account, whereas NameSpace.GetDefaultFolder returns the default folder on the default store for the current profile.

2. Iterating over all items in the folder is not really a good idea:

   For Each olMail In olItms
        If LatestDate > CDate(olMail.ReceivedTime) Then
            If CDate(olMail.ReceivedTime) > EarliestDate Then
   

   Use the Find/FindNext or Restrict methods of the Items class instead. Read more about these methods in the following articles:

   • How To: Retrieve Outlook calendar items using Find and FindNext methods
   • How To: Use Restrict method in Outlook to get calendar items