Outlook macroで予定一覧を取得する

効率化

2020.05.272021.04.22

目的
1. ユースケース
code
appendix

目的

Outlookの予定を拾いたいときに、かなりの頻度で出てくるケースを関数にしました。

ユースケース

今日の予定一覧を引っ張る
明日の予定一覧を引っ張る

（2021/04/22追記）
任意のフォルダ/機関のスケジュールを取得するマクロ関数も作成しました。

参考

code

'予定一覧引っ張るDebug

Sub debugGetSchedule()

    Dim myStart As Date
    Dim duration As Integer
    Dim oItemsInDateRange As Outlook.Items
    
    
    myStart = Date
    Call GetSchedule(oItemsInDateRange, myStart, 15)
    
    For Each oAppt In oItemsInDateRange
         Debug.Print oAppt.Start, oAppt.Subject
    Next
   
End Sub

' 予定一覧を引っ張る
Public Function GetSchedule(oItemsInDateRange As Outlook.Items, ByRef myStart As Date, ByRef duration As Integer)

    
    Dim objApItem As Outlook.AppointmentItem
    Dim myEnd As Date
    Dim oCalendar As Outlook.Folder
    Dim oItems As Outlook.Items
    Dim oFinalItems As Outlook.Items
    Dim oAppt As Outlook.AppointmentItem
    Dim strRestriction As String
    Dim lunchTime As Boolean

    myEnd = DateAdd("d", duration, myStart)

    Set objApItem = Application.CreateItem(olAppointmentItem)
'        Debug.Print "Start:", myStart
'        Debug.Print "End:", myEnd
          
    'Construct filter for the next duration-day date range
    strRestriction = "[Start] >= '" & _
    Format$(myStart, "mm/dd/yyyy hh:mm AMPM") _
    & "' AND [End] <= '" & _
    Format$(myEnd, "mm/dd/yyyy hh:mm AMPM") & "'"
    'Check the restriction string
    'Debug.Print strRestriction
    
    Set oCalendar = Application.Session.GetDefaultFolder(olFolderCalendar)
    Set oItems = oCalendar.Items
    oItems.IncludeRecurrences = True
    oItems.Sort "[Start]"
    Set oItemsInDateRange = oItems.Restrict(strRestriction)
End Function