使用多个日期条件限制方法过滤器 [英] Restrict method filter with multiple date condition

问题描述

我正在尝试使用Excel VBA筛选出我的Outlook邮件收件箱，然后在条件满足时最终发送电子邮件.

I'm trying to use Excel VBA to filter out my outlook mail inbox and then eventually send email if condition meets.

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

The full condition is: If the Outlook Inbox contains Subject within the date range(past 7 days) and from Sender(dynamic sender email).

我已经完成了sub sendEmails()，现在正忙于过滤邮件.

I had completed the sub sendEmails(), now struggling with filtering mails.

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

The code I have now successfully filter out the subject I'm looking at. However, after trying to include in the date range into the filter, It screwd up.

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

First Problem: The filter is giving me

运行时错误13:键入不匹配.

Run-Time Error 13: Type Mismatch.

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

I knew this happens because the filter contains String and Date values, so I tried to change type to variant but still ran into the error.

另一个问题是，我按照这篇文章尝试添加日期条件.并且此帖子为日期应用过滤器. 如果有经验的人可以纠正我的错误，那么应该多加赞赏. (还没有跑到那里，但是只有 strong 的感觉会出错)

Another issue is that I followed this post for attempting to add the date conditions. And this post to apply filter for date. Properly many errors so appreciated if somebody with experience can correct my mistakes. (Haven't run to there yet, but just having strong feeling will hit errors)

这是我第一次使用它，所以请放轻松.

This is my first time working with this so please go easy on me.

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

推荐答案

假设您在添加日期归档程序时仅遇到问题，则可以找到一些有用的

Assuming you're only having problem in adding date filers you can find something useful here.
Now applying it to your code, you will have to enclose your dates with single quotes (').

尽管日期和时间通常以日期格式存储，但是查找和限制"方法要求将日期和时间转换为字符串表示形式.

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中的保留字.}

_{Note: I used eFilter instead of Filter as variable because it is a reserved word in VBA.}

这将为您提供:

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

@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')

这篇关于使用多个日期条件限制方法过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！