How to compare the "To:" field in the Outlook mail with a predefined Mail id using VBA?

Question

This code runs on my friend's Excel.

On mine it throws an error on the line Range("C" & i).Value = olMail.To.

This is the code.

Sub Trial()

    Dim olApp As Outlook.Application
    Dim olNS As Namespace
    Dim Fldr As MAPIFolder
    Dim olMail As Object
    Dim i As Integer, j As Integer
    
    Set olApp = New Outlook.Application
    Set olNS = olApp.GetNamespace("MAPI")
    
    Set Fldr = olNS.GetDefaultFolder(olFolderInbox)
   
    Sheets("Sheet1").Select
    i = [Counta(Sheet1!A:A)]
    
    Dim lastMail As String
    If i = 1 Then
        lastMail = 0
    Else
        lastMail = Range("B" & i).Value
    End If
    
    For Each olMail In Fldr.Items
    
        If olMail.UnRead = True Then
        
            i = i + 1
            Range("C" & i).Value = olMail.To

            If Range("C" & i).Value Like "*MailID comes here*" Then
                
                Range("A" & i).Value = olMail.Subject
                Range("B" & i).Value = olMail.SentOn
                Range("D" & i).Value = olMail.CC
                Range("E" & i).Value = olMail.Body
           
                olMail.UnRead = False
                Call Macro1(i)
            Else
                Range("C" & i).Select
                Selection.ClearContents
                i = i - 1
            End If
       
        End If
    Next olMail
    
    ActiveWorkbook.Save
  
    Set olApp = Nothing
    Set olNS = Nothing
    Set Fldr = Nothing

End Sub

Fully qualify the ranges. Though, without more details it's hard to tell — Ricardo Diaz, Mar 02 '21 at 04:33
Not everything on your Inbox is necessarily a mail item or something which has a `To` property. — Tim Williams, Mar 02 '21 at 04:52
Add a check like `If TypeOf olMail Is MailItem Then` to make sure you're dealing with a mail item and not something else. — Tim Williams, Mar 02 '21 at 05:13

score 0 · Answer 1 · answered Mar 02 '21 at 06:24

i think you miss handle to workbook, if wasnt active return error. try this code I add handle to workbook and worksheet

Sub Trial()

    Dim olApp As Outlook.Application
    Dim olNS As Namespace
    Dim Fldr As MAPIFolder
    Dim olMail As Object
    Dim i As Integer, j As Integer
    dim wb as workbook
    dim ws as worksheet

    Set olApp = New Outlook.Application
    Set olNS = olApp.GetNamespace("MAPI")
    
    Set Fldr = olNS.GetDefaultFolder(olFolderInbox)
    
    set wb= thisworkbook
    set ws = wb.worksheets("Sheet1")
    
    i = ws.cells(rows.count,1).end(xlup).row
    
    Dim lastMail As String
    If i = 1 Then
        lastMail = 0
    Else
        lastMail = ws.Range("B" & i).Value
    End If
    
    For Each olMail In Fldr.Items
    
    If olMail.UnRead = True Then
        
            i = i + 1
            ws.Range("C" & i).Value = olMail.To

                If ws.Range("C" & i).Value Like "*MailID comes here*" Then
                
                    ws.Range("A" & i).Value = olMail.Subject
                    ws.Range("B" & i).Value = olMail.SentOn
                    ws.Range("D" & i).Value = olMail.CC
                    ws.Range("E" & i).Value = olMail.Body
           
                    olMail.UnRead = False
                    Call Macro1(i)
                Else
                    ws.Range("C" & i).ClearContents
                    i = i - 1
                End If
       
    End If
    Next olMail
    
    wb.Save
  
    Set olApp = Nothing
    Set olNS = Nothing
    Set Fldr = Nothing
    set wb = nothing
    set ws = nothing
End Sub

There is an error when returning mailitem properties from non-mailitems. This occurs frequently. `Dim olMail As Object` is better as `Dim olItem As Object` after which there would be a test to verify the item is a mailitem then `Set olMail = olItem` https://stackoverflow.com/a/18702525/1571407 — niton, Mar 08 '21 at 17:52

How to compare the "To:" field in the Outlook mail with a predefined Mail id using VBA?

1 Answers1