Grab Outlook calendar with Python

Question

I have a VBA script that grabs MS Outlook items (specifically outlook appointments) and spits out results within an excel table. It is just grabbing the next 7 days of meetings.

I'm wondering if there is a way to get this to either a) into an equivalent python script OR b) call out to run this vba script and output the results into a python list

What would be the path of least resistance?

Sub FindAppts()

Application.EnableEvents = False
    Dim myStart As Date
    Dim myEnd As Date
    Dim ws1 As Worksheet
    Dim CalRawOutput As Range
    Dim o As Outlook.Application
    Dim ons As Outlook.Namespace
    Dim oCalendar As Outlook.Folder
    Dim oItems As Outlook.Items
    Dim oItemsInDateRange As Outlook.Items
    Dim oFinalItems As Outlook.Items
    Dim oAppt As Outlook.AppointmentItem
    Dim strRestriction As String
    Dim lastrownum As Integer
    Dim OutputRange As Range

Set ws1 = ActiveWorkbook.Sheets("Calendar")
lastrownum = ws1.Cells(ws1.Rows.Count, "B").End(xlUp).row
Set OutputRange = ws1.Range(Cells(53, 2).Address(), Cells(lastrownum, 5).Address())
OutputRange.ClearContents


    myStart = Date
    myEnd = DateAdd("d", 7, myStart)

    'Construct filter for the next 7-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
    Set o = New Outlook.Application
    Set ons = o.GetNamespace("MAPI")
    Set oCalendar = ons.GetDefaultFolder(olFolderCalendar)
    Set oItems = oCalendar.Items
    oItems.IncludeRecurrences = True
    oItems.Sort "[Start]"
    'Restrict the Items collection for the 7-day date range
    Set oItemsInDateRange = oItems.Restrict(strRestriction)
        & "0x0037001E" & Chr(34) & " like '%team%'"
    oItemsInDateRange.Sort "[Start]"
r = 1
Set CalRawOutput = ws1.Range("B53:E100")
    For Each oAppt In oItemsInDateRange


            CalRawOutput.Cells(r, 1).Value = oAppt.Subject
            CalRawOutput.Cells(r, 2).Value = oAppt.Start
            CalRawOutput.Cells(r, 3).Value = oAppt.End
            CalRawOutput.Cells(r, 4).Value = oAppt.Location
            r = r + 1
    Next
Application.EnableEvents = True
End Sub

score 0 · Answer 1 · answered May 18 '20 at 21:28

The Application.Run method allows running a macro or calls a function. This can be used to run a macro written in Visual Basic or the Microsoft Excel macro language, or to run a function in a DLL or XLL. The first parameter can be either a string with the macro name, a Range object indicating where the function is, or a register ID for a registered DLL (XLL) function. If a string is used, the string will be evaluated in the context of the active sheet.

Application.Run "FindAppts"

If the VBA sub is located in a particular file you need to use the following construction:

Application.Run "'My Work Book.xls'!FindAppts"

So, basically you just need to automate Excel from Python and use the Run method to get your code running.

But the Excel object model is common for all kind of applications or programming languages. You can port your code to Python, see Automation Excel from Python for more information. For example, take a look at the Python Win32 extensions and openpyxl.

Grab Outlook calendar with Python

1 Answers1