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