I am trying to use Excel VBA to filter my Outlook inbox, and then send an email if necessary.
Full condition: if the Outlook inbox contains a subject in the date range (for the last 7 days) and from the sender (dynamic sender letter).
I ended up sub sendEmails()
now struggling with email filtering.
The code that I am now successfully filtering out the topic I'm looking at. However, after trying to include a date range in the filter, it is screwed.
First problem: the filter gives me
Runtime Error 13: Type Mismatch.
I knew this was happening because the filter contains values String
and Date
therefore I tried to change the type to variant
, but still ran into an error.
Another issue is that I followed this post for trying to add date conditions. And this post apply a filter to the date. Correctly, many errors are so appreciated if someone with experience can correct my errors. (Do not run there yet, but only a strong feeling will lead to errors)
This is my first time working with this, so please calm down.
Sub Search_Inbox()
Dim myOlApp As New Outlook.Application
Dim objNamespace As Outlook.Namespace
Dim objFolder As Outlook.MAPIFolder
Dim filteredItems As Outlook.Items
Dim itm As Object
Dim Found As Boolean
Dim Filter As Variant
Dim tdyDate As String
Dim checkDate As Date
tdyDate = Format(Now(), "Short Date")
checkDate = DateAdd("d", -7, tdyDate) ' DateAdd(interval,number,date)
Set objNamespace = myOlApp.GetNamespace("MAPI")
Set objFolder = objNamespace.GetDefaultFolder(olFolderInbox)
'https:
Filter = "@SQL=" & Chr(34) & "(urn:schemas:httpmail:subject" & Chr(34) & " like 'Reminder on Subject' &" _
And Chr(34) & "urn:schemas:httpmail:datereceived >= & checkDate & " _
And Chr(34) & "urn:schemas:httpmail:datereceived >= & tdyDate &"
Set filteredItems = objFolder.Items.Restrict(Filter)
If filteredItems.Count = 0 Then
Debug.Print "No emails found"
Found = False
Else
Found = True
' this loop is optional, it displays the list of emails by subject.
For Each itm In filteredItems
Debug.Print itm.Subject
Next
End If
'If the subject isn't found:
If Not Found Then
'NoResults.Show
Else
Debug.Print "Found " & filteredItems.Count & " items."
End If
Set myOlApp = Nothing
End Sub
source
share