excel - 限制具有多个日期条件的方法过滤器

Question

我正在尝试使用 Excel VBA 过滤掉我的 Outlook 邮件收件箱，然后在满足条件时最终发送电子邮件。

完整的条件是:如果 Outlook 收件箱包含日期范围内的主题(过去 7 天)和发件人(动态发件人电子邮件)。

我已经完成了 sub sendEmails()，现在正在努力过滤邮件。

我现在已经成功过滤掉我正在查看的主题的代码。但是，在尝试将日期范围包含在过滤器中之后，它搞砸了。

第一个问题:过滤器给了我

Run-Time Error 13: Type Mismatch.

我知道会发生这种情况，因为过滤器包含 String 和 Date 值，所以我尝试将类型更改为 variant 但仍然遇到了错误。

另一个问题是我关注了this post用于尝试添加日期条件。和 this post为日期应用过滤器。如果有经验的人可以纠正我的错误，那么非常感谢正确的许多错误。(还没跑到那里，只是有strong的感觉会出错)

这是我第一次使用这个，所以请放轻松。

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://msdn.microsoft.com/en-us/library/aa579702(v=exchg.80).aspx
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

Answer 1

假设您只是在添加日期文件管理器时遇到问题，您可以找到一些有用的东西 here .
现在将它应用到您的代码中，您必须用单引号 (') 将您的日期括起来。

Although dates and times are typically stored with a Date format, the Find and Restrict methods require that the date and time be converted to a string representation.

Dim tdyDate As String, checkDate As String

tdyDate = "'" & Format(Date, "Short Date") & "'"
checkDate = "'" & Format(Date - 7, "Short Date") & "'"

或者你可以试试:

tdyDate = Format(Date, "\'ddddd\'") '/* until todays date */
checkDate = Format(Date - 7, "\'ddddd\'") '/* I suppose you are filtering 7 days ago? */

然后构建您的过滤器:

eFilter = "@SQL= (urn:schemas:httpmail:subject Like 'Reminder on Subject'" & _
          " And urn:schemas:httpmail:datereceived >= " & checkDate & _
          " And urn:schemas:httpmail:datereceived <= " & tdyDate & ")"

_{注意:我使用 eFilter 而不是 Filter 作为变量，因为它是 VBA 中的保留字。}

这会给你:

@SQL= (urn:schemas:httpmail:subject Like 'Reminder on Subject' And urn:schemas:httpmail:datereceived >= '1/2/2018' And urn:schemas:httpmail:datereceived <= '1/9/2018')