VBA筛选今天的全天重复Outlook约会

huangapple go评论56阅读模式
英文:

VBA Filter All-Day Outlook Reoccurring Appointments for Today

问题

如何使用VBA的restrict函数来筛选今天的全天重复的Outlook日历约会?我有一个每两周重复一次的全天约会。

我的尝试:

Dim olApp As Outlook.Application
Set olApp = Outlook.Application
Set calendarItems = olApp.Session.GetDefaultFolder(olFolderCalendar).Items

'文档说首先对集合进行排序,然后设置包括重复项
'https://learn.microsoft.com/en-us/office/vba/api/outlook.items.includerecurrences
calendarItems.Sort "[Start]"
calendarItems.IncludeRecurrences = True

'用于restrict的筛选条件:[Start] < "明天" 并且 [Start] >= "今天"
criteria = "[Start] < " & Chr(34) & VBA.Format(Now + 1, "Short Date") & Chr(34) & " and [Start] >= " & Chr(34) & VBA.Format(Now, "Short Date") & Chr(34)

Set todayAppointments = calendarItems.Restrict(criteria)

'用于调试的打印信息
For Each Item In todayAppointments
    Debug.Print (Item.Subject & " " & Item.ConversationTopic & " " & Item.Organizer & " " & Item.Start & " " & Item.End)
Next

这会显示出今天发生的普通约会,但不包括重复的约会。

如果我扩展筛选条件以包括昨天一天,即VBA.Format(Now - 1, "Short Date"),那么它会包括今天的重复约会。但是,那样也会包括前一天的其他约会,这不是我想要的。

英文:

How can I use the VBA restrict function to filter for all day reoccurring Outlook calendar appointments that are today? I have a reoccurring appointment every other week and it's set as an all-day appointment.

My attempt:

Dim olApp As Outlook.Application
Set olApp = Outlook.Application
Set calendarItems = olApp.Session.GetDefaultFolder(olFolderCalendar).Items

&#39;Documentation says sort the collection first, then set include recurrences
&#39;https://learn.microsoft.com/en-us/office/vba/api/outlook.items.includerecurrences
calendarItems.Sort &quot;[Start]&quot;
calendarItems.IncludeRecurrences = True

&#39;filter for restrict: [Start] &lt; &quot;&lt;tomorrow&gt;&quot; and [Start] &gt;= &quot;&lt;today&gt;&quot;
criteria = &quot;[Start] &lt; &quot; &amp; Chr(34) &amp; VBA.Format(Now + 1, &quot;Short Date&quot;) &amp; Chr(34) &amp; &quot; and [Start] &gt;= &quot; &amp; Chr(34) &amp; VBA.Format(Now, &quot;Short Date&quot;) &amp; Chr(34)

Set todayAppointments = calendarItems.Restrict(criteria)

&#39;Print info for debugging
For Each Item In todayAppointments
    Debug.Print (Item.Subject &amp; &quot; &quot; &amp; Item.ConversationTopic &amp; &quot; &quot; &amp; Item.Organizer &amp; &quot; &quot; &amp; Item.Start &amp; &quot; &quot; &amp; Item.End)
Next

This will bring up the regular appointments that occur today, but not the reoccurring appointment.

I can get the reoccurring appointment if I expand my filter to be one day before today, i.e. VBA.Format(Now - 1, &quot;Short Date&quot;), then it brings in my reoccurring appointment for today. However, then I also get other appointments for the previous day which is not what I wanted.

答案1

得分: 1

添加一个时间组件。

Option Explicit ' 将此视为强制性的
' 工具 | 选项 | 编辑器选项卡
' 要求变量声明
' 如果非常需要,请声明为Variant

Private Sub ApptToday()

Dim calendarItems As Items
Dim todayAppointments As Items

Dim item As Object

Set calendarItems = Session.GetDefaultFolder(olFolderCalendar).Items

' 文档建议首先对集合进行排序,然后设置包括重复项
' https://learn.microsoft.com/en-us/office/vba/api/outlook.items.includerecurrences
calendarItems.Sort "[Start]"
calendarItems.IncludeRecurrences = True

Dim criteria1 As String
criteria1 = "[Start] < " & Chr(34) & VBA.Format(Now + 1, "Short Date") & Chr(34) & " AND [Start] >= " & Chr(34) & VBA.Format(Now, "Short Date") & Chr(34)
Debug.Print
Debug.Print criteria1
Set todayAppointments = calendarItems.Restrict(criteria1)

' 用于调试的打印信息
For Each item In todayAppointments
    If item.Class = olAppointment Then
        Debug.Print (item.subject & " " & item.ConversationTopic & " " & item.Organizer & " " & item.Start & " " & item.End)
    End If
Next

Dim criteria2 As String
criteria2 = "[Start] < " & Chr(34) & VBA.Format(Now + 1, "Short Date") & " 00:00" & Chr(34) & " AND [Start] >= " & Chr(34) & VBA.Format(Now, "Short Date") & " 00:00" & Chr(34)
Debug.Print
Debug.Print criteria2
Set todayAppointments = calendarItems.Restrict(criteria2)

' 用于调试的打印信息
For Each item In todayAppointments
    If item.Class = olAppointment Then
        Debug.Print (item.subject & " " & item.ConversationTopic & " " & item.Organizer & " " & item.Start & " " & item.End)
    End If
Next

Dim criteria3 As String
criteria3 = "[Start] < " & Chr(34) & VBA.Format(Date + 1, "yyyy-mm-dd hh:nn") & Chr(34) & " AND [Start] >= " & Chr(34) & VBA.Format(Date, "yyyy-mm-dd hh:nn") & Chr(34)
Debug.Print
Debug.Print criteria3
Set todayAppointments = calendarItems.Restrict(criteria3)

' 用于调试的打印信息
For Each item In todayAppointments
    If item.Class = olAppointment Then
        Debug.Print (item.subject & " " & item.ConversationTopic & " " & item.Organizer & " " & item.Start & " " & item.End)
    End If
Next

End Sub
英文:

Add a time component.

Option Explicit &#39; Consider this mandatory
&#39; Tools | Options | Editor tab
&#39; Require Variable Declaration
&#39; If desperate declare as Variant


Private Sub ApptToday()

Dim calendarItems As Items
Dim todayAppointments As Items

Dim item As Object

Set calendarItems = Session.GetDefaultFolder(olFolderCalendar).Items

&#39;Documentation says sort the collection first, then set include recurrences
&#39;https://learn.microsoft.com/en-us/office/vba/api/outlook.items.includerecurrences
calendarItems.Sort &quot;[Start]&quot;
calendarItems.IncludeRecurrences = True

Dim criteria1 As String
criteria1 = &quot;[Start] &lt; &quot; &amp; Chr(34) &amp; VBA.Format(Now + 1, &quot;Short Date&quot;) &amp; Chr(34) &amp; &quot; AND [Start] &gt;= &quot; &amp; Chr(34) &amp; VBA.Format(Now, &quot;Short Date&quot;) &amp; Chr(34)
Debug.Print
Debug.Print criteria1
Set todayAppointments = calendarItems.Restrict(criteria1)

&#39;Print info for debugging
For Each item In todayAppointments
    If item.Class = olAppointment Then
        Debug.Print (item.subject &amp; &quot; &quot; &amp; item.ConversationTopic &amp; &quot; &quot; &amp; item.Organizer &amp; &quot; &quot; &amp; item.Start &amp; &quot; &quot; &amp; item.End)
    End If
Next


Dim criteria2 As String
criteria2 = &quot;[Start] &lt; &quot; &amp; Chr(34) &amp; VBA.Format(Now + 1, &quot;Short Date&quot;) &amp; &quot; 00:00&quot; &amp; Chr(34) &amp; &quot; AND [Start] &gt;= &quot; &amp; Chr(34) &amp; VBA.Format(Now, &quot;Short Date&quot;) &amp; &quot; 00:00&quot; &amp; Chr(34)
Debug.Print
Debug.Print criteria2
Set todayAppointments = calendarItems.Restrict(criteria2)

&#39;Print info for debugging
For Each item In todayAppointments
    If item.Class = olAppointment Then
        Debug.Print (item.subject &amp; &quot; &quot; &amp; item.ConversationTopic &amp; &quot; &quot; &amp; item.Organizer &amp; &quot; &quot; &amp; item.Start &amp; &quot; &quot; &amp; item.End)
    End If
Next

Dim criteria3 As String
criteria3 = &quot;[Start] &lt; &quot; &amp; Chr(34) &amp; VBA.Format(Date + 1, &quot;yyyy-mm-dd hh:nn&quot;) &amp; Chr(34) &amp; &quot; AND [Start] &gt;= &quot; &amp; Chr(34) &amp; VBA.Format(Date, &quot;yyyy-mm-dd hh:nn&quot;) &amp; Chr(34)
Debug.Print
Debug.Print criteria3
Set todayAppointments = calendarItems.Restrict(criteria3)

&#39;Print info for debugging
For Each item In todayAppointments
    If item.Class = olAppointment Then
        Debug.Print (item.subject &amp; &quot; &quot; &amp; item.ConversationTopic &amp; &quot; &quot; &amp; item.Organizer &amp; &quot; &quot; &amp; item.Start &amp; &quot; &quot; &amp; item.End)
    End If
Next

End Sub

答案2

得分: 0

需要以相反的顺序设置属性。不要首先对集合进行排序,而是首先包括重复项:

calendarItems.Sort "[Start]"```

尽管日期和时间通常以日期格式存储,但使用 Jet 和 DAV Searching and Locating (DASL) 语法的筛选器要求将日期时间值转换为字符串表示。在 Jet 语法中,日期时间比较字符串应该用双引号或单引号括起来。在 DASL 语法中,日期时间比较字符串应该用单引号括起来。

为确保日期时间比较字符串格式与 Microsoft Outlook 期望的格式一致,使用 Visual Basic for Applications 的 `Format` 函数。以下示例创建了一个 Jet 筛选器,以查找在2022年6月12日下午3:30之前修改的所有项目,本地时间。

```criteria = "[LastModificationTime] < '&#" & Format("6/12/2022 3:30PM","General Date") & "#'"```

在我为技术博客写的文章中了解更多信息:

- [如何在 Outlook 中使用 Restrict 方法获取日历项][1]
- [如何使用 Find 和 FindNext 方法检索 Outlook 日历项][2]

  [1]: https://www.add-in-express.com/creating-addins-blog/2011/11/15/outlook-restrict-calendar-items/
  [2]: https://www.add-in-express.com/creating-addins-blog/2011/11/11/outlook-retrieve-calendar-items/

<details>
<summary>英文:</summary>

You need to set properties in the reverse order. Instead of sorting the collection first you need to include recurrences first of all:

calendarItems.IncludeRecurrences = True
calendarItems.Sort "[Start]"


Although dates and times are typically stored with a date format, filters using the Jet and DAV Searching and Locating (DASL) syntax require that the date-time value to be converted to a string representation. In Jet syntax, the date-time comparison string should be enclosed in either double quotes or single quotes. In DASL syntax, the date-time comparison string should be enclosed in single quotes.

To make sure that the date-time comparison string is formatted as Microsoft Outlook expects, use the Visual Basic for Applications `Format` function. The following example creates a Jet filter to find all items that have been modified before June 12, 2022 at 3:30 P.M local time.

criteria = "[LastModificationTime] < '" _
& Format("6/12/2022 3:30PM","General Date") & "'"


Read more about that in the articles that I wrote for the technical blog:

 - [How To: Use Restrict method in Outlook to get calendar items][1]
 - [How To: Retrieve Outlook calendar items using Find and FindNext methods][2]


  [1]: https://www.add-in-express.com/creating-addins-blog/2011/11/15/outlook-restrict-calendar-items/
  [2]: https://www.add-in-express.com/creating-addins-blog/2011/11/11/outlook-retrieve-calendar-items/

</details>



huangapple
  • 本文由 发表于 2023年3月21日 03:03:22
  • 转载请务必保留本文链接:https://go.coder-hub.com/75794303.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定