CODEWITHSUNDEEP
excelvbaemailoutlook
MaraBiz
MaraBiz

Reputation: 1

Returning mailitem property of item in inbox: Error 438 object doesn't support this property or method

I am trying to run the macro below in Excel.

I get

error 438 object doesn't support this property or method

on the line If OutlookMail.ReceivedTime >= Range("From_date").Value Then

Option Explicit

Sub getDataFromOutlook()

    Dim OutlookApp As Outlook.Application
    Dim OutlookNamespace As Namespace
    Dim Folder As MAPIFolder
    Dim OutlookMail As Variant
    Dim objOwner As Outlook.Recipient
    Dim i As Integer

    Set OutlookApp = New Outlook.Application

    Set OutlookNamespace = OutlookApp.GetNamespace("MAPI")

    Set objOwner = OutlookNamespace.CreateRecipient("[email protected]")
objOwner.Resolve

    If objOwner.Resolved Then
        Set Folder = OutlookNamespace.GetSharedDefaultFolder(objOwner, olFolderInbox)
    End If

    i = 1

    For Each OutlookMail In Folder.Items
        If OutlookMail.ReceivedTime >= Range("From_date").Value Then
            Range("eMail_sender").Offset(i, 0).Value = OutlookMail.SenderName
            Range("eMail_subject").Offset(i, 0).Value = OutlookMail.Subject
            Range("eMail_date").Offset(i, 0).Value = OutlookMail.ReceivedTime
            Range("eMail_categories").Offset(i, 0).Value = OutlookMail.Categories
            Range("eMail_flag_status").Offset(i, 0) = OutlookMail.FlagStatus

            i = i + 1
        End If
    Next OutlookMail

    Set Folder = Nothing
    Set OutlookNamespace = Nothing
    Set OutlookApp = Nothing

End Sub

Upvotes: 0

Views: 973

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71177

Error 438 means you're trying to invoke a member that does not exist - that can only ever happen at run-time, against late-bound code.

Late bound? VBA can't know what the actual type of a Variant or Object is going to be until the code is actually running - when members are bound at run-time, it's called late binding. When members are bound at compile-time, it's called early binding. You want as much of your code to be early-bound, so that you pick up such errors at compile-time rather than at run-time.

But you're referencing the Outlook library - you have no valid reason to late-bind anything here.

Folder.Items contains many object types: the OutlookMail variable has a very misleading name:

For Each OutlookMail In Folder.Items

A better name could be folderItem, or unknownItem - because we know it's something that's in a folder, but we don't know what type of object that is. All we know of it, is that it's an Object:

Dim unknownItem As Object
For Each unknownItem In Folder.Items

Now if the type of unknkownItem is Outlook.MailItem, we can cast it to that interface:

    Dim emailItem As Outlook.MailItem
    If TypeOf unknownItem Is Outlook.MailItem Then
        Set emailItem = unknownItem

And now every single member call we make against emailItem will be validated at compile-time instead of run-time, because we know that inside this conditional block, we're looking at a MailItem object.

        Sheet1.Range("eMail_sender").Offset(i, 0).Value = emailItem.SenderName

Note that SenderName will be in the names list when you type the . dot: that's how you know you're making an early-bound member call.

Avoid implicit late binding wherever you can - that means working with object variables that have an explicit, declared type, and avoiding member calls against Variant and Object.

Upvotes: 1

Eugene Astafiev
Eugene Astafiev

Reputation: 49395

An obvious fact is that Outlook folders may contain a different kind of items, so not every type provides the ReceivedTime property:

For Each OutlookMail In Folder.Items
    If OutlookMail.ReceivedTime >= Range("From_date").Value Then

You must check the item type first before accessing any property or method to make sure such a member exists for the item:

For Each OutlookMail In Folder.Items
   If TypeOf OutlookMail Is MailItem Then
      If OutlookMail.ReceivedTime >= Range("From_date").Value Then

Upvotes: 3

Related Questions