Outlook VBA - if no mail found

lemonbarley

New Member
Joined
Jun 1, 2021
Messages
6
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

VBA 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:
VBA Code:
           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.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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.

VBA Code:
    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
 
Upvote 0
Solution
You are welcome and thanks for the feedback (y)
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top