Outlook VBA - if no mail found

[lemonbarley]

This code runs perfect in finding and retrieving emails from my local sync shared mailbox - except when it cannot find anything, it does nothing; whereas I want it to tell me nothing is found (rather than have blanks where data should have been).

Note: some text have been edited/converted to comments here to remove private/non-essential data from the code

Sub InvDet()
      
    Dim ol As Outlook.Application: Set ol = New Outlook.Application
    Dim ns As Outlook.Namespace: Set ns = ol.GetNamespace("MAPI")
    Dim olshared As Outlook.Recipient: Set olshared = ns.CreateRecipient(emailaddress)
    Dim fol As Outlook.folder: Set fol = ns.GetSharedDefaultFolder(olshared, olFolderInbox)
   
    Dim i As Object
    Dim mi As Outlook.MailItem
    Dim n As Long
    Dim FilterText As String
   
    For n = 3 To 'variable
        FilterText = 'SQL filtercriteria using subject and from
        For Each i In fol.Items.Restrict(FilterText)
            If i Is Nothing Then
                Cells(n, 2) = "No mail found"
            End If

            If i.Class <> olMail Then
                Cells(n, 2) = "Item is not a mail"
            End If

            If i.Class = olMail Then
                Set mi = i
                Cells(n, 2) = 'string using inStr(mi.Body)
            End If
        Next i
    Next n
   
End Sub

What's not working is If i is Nothing Then Cells(n, 2) = "No mail found"
I just get a blank in Cells(n, 2)
I've also tried this to no avail:

           If i.Class = olMail Then
               Set mi = i
               Cells(n, 2) = string using inStr(mi.Body)
               Else: Cells(n, 2) = "No mail found" 'tried this but nothing gets populated in Cells(n, 2)
           End If

I don't know if the If i.Class <> olMail part works because my FilterText is stringent enough that I wouldn't find any other item that isn't a mail (I copied the code off a tutorial).

To be clear, If i.Class = olMail part works perfect - if it finds an email, it populates the cells with data I want.

 

[GWteB]

Your issue is due to the logic of your code. In cases you are iterating through a collection filled with one or more elements, obviously each element exists.
That said, within the inner loop variable n will never increase so the code is giving you only the last found email item.
Imo it should look like something like the code below, although I didn't test it.

    n = 3
    FilterText = 'SQL filtercriteria using subject and from
    
    If fol.Items.Restrict(FilterText).Count > 0 Then
        
        For Each i In fol.Items.Restrict(FilterText)
            If i.Class = olMail Then
                Set mi = i
                Cells(n, 2) = 'string using inStr(mi.Body)
            Else
                Cells(n, 2) = "Item is not a mail"
            End If
            n = n + 1
                ' === optional ===
                If n = MaxCount Then    ' <<<< change MaxCount to a number that suits your purpose
                    Exit For
                End If
                ' ================
        Next i
    
    Else
        Cells(n, 2) = "No mail found"
    End If

 

[lemonbarley]

I get what you're saying! Your solution works.
Thank you so much!

 

[GWteB]

You are welcome and thanks for the feedback