Powershell Outlook Time Reporting by Category

June 11, 2013

Table of Contents

This code does the task of an Outlook Time Reporting sheet with all the categories and times of the day and the summary for a week. Hope it will help someone out there to import their time into SAP. This is all made easy with PowerShell here is the code:


# OutlookTimeReport.ps1

# v1.2



param ( [DateTime] $rangeStart = (get-date -hour 0 -minute 0 -second 0).AddDays(-(get-date).DayOfWeek.value__),

 [DateTime] $rangeEnd = (get-date -hour 23 -minute 59 -second 59).AddDays(7-(get-date).DayOfWeek.value__),

 [String] $categories,

 [String] $calendar,

 [switch] $csv,

 [switch] $pickdates

function pick-date()


 $objForm = New-Object Windows.Forms.Form


$objForm.Text = “Select a Date”

 $objForm.Size = New-Object Drawing.Size @(190,190)

 $objForm.StartPosition = “CenterScreen”

$objForm.KeyPreview = $True


 if ($_.KeyCode -eq “Enter”)







 if ($_.KeyCode -eq “Escape”)





$objCalendar = New-Object System.Windows.Forms.MonthCalendar

 $objCalendar.ShowTodayCircle = $False

 $objCalendar.MaxSelectionCount = 1


$objForm.Topmost = $True


 [void] $objForm.ShowDialog()

if ($dtmDate)


 return $dtmDate



if ($pickdates)


 [void] [System.Reflection.Assembly]::LoadWithPartialName(“System.Windows.Forms”)

 [void] [System.Reflection.Assembly]::LoadWithPartialName(“System.Drawing”)

 [DateTime] $pickedStart = pick-date

 [DateTime] $pickedEnd = pick-date

 If ($pickedStart -and $pickedEnd)


 $rangeStart = $pickedStart

 $rangeEnd = $pickedEnd



Add-Type -AssemblyName Microsoft.Office.Interop.Outlook

$class = @”

using Microsoft.Office.Interop.Outlook;public class MyOL


 public MAPIFolder GetCalendar(string userName)


 Application oOutlook = new Application();

 NameSpace oNs = oOutlook.GetNamespace(“MAPI”);

 Recipient oRep = oNs.CreateRecipient(userName);

 MAPIFolder calendar = oNs.GetSharedDefaultFolder(oRep, OlDefaultFolders.olFolderCalendar);

 return calendar;




Add-Type $class -ReferencedAssemblies Microsoft.Office.Interop.Outlook

$outlook = new-object MyOL

$restriction = “[End] >= ‘{0}’ AND [Start] <= ‘{1}'” -f $rangeStart.ToString(“g”), $rangeEnd.ToString(“g”)

$seArray = @()

$categoryMatch = [string]$categories -replace ‘ ‘,’|’

$SECalendar = $outlook.GetCalendar($calendar)

$appointments = $SECalendar.items


$appointments.IncludeRecurrences = $true

$SEappts = $appointments.Restrict($restriction) | where {$_.categories -match $categoryMatch}

# Group the appointments by category

$grouped = $SEappts | Group-Object categories

# This is our table array

$table = @()

# Days of the week. Are these language sensitive?

$dow = @(“Monday”,”Tuesday”,”Wednesday”,”Thursday”,”Friday”)

# Change hash values to local language if required.

$dowHash = @{“Monday”=”Monday”; “Tuesday”=”Tuesday”; “Wednesday”=”Wednesday”; “Thursday”=”Thursday”; “Friday”=”Friday”}

# For each category…

foreach ($group in $grouped)


 # Start a new row…

 $row = New-Object PSObject

 # Add the category name to the start of our table…

 Add-Member -MemberType NoteProperty -Name “Category” -value $group.name -InputObject $row

# For each work day

 foreach ($day in $dow)


 # Sum up the hours for that category.

 $hours = ($group.group | where {$_.start.dayofweek -eq $day} | Measure-Object -Sum -Property duration).sum /60

# Then add it to our table row.

 Add-Member -MemberType NoteProperty -Name $dowHash.($day) -Value $hours -InputObject $row


 $table += $row


# Summary at the end

$summary = New-Object PSObject

Add-Member -MemberType NoteProperty -Name “Category” -value “SUMMARY” -InputObject $summary

foreach ($day in $dow)


 # Sum up the hours for that category.

 $hours = ($SEappts | where {$_.start.dayofweek -eq $day} | Measure-Object -Sum -Property duration).sum /60

# Then add it.

 Add-Member -MemberType NoteProperty -Name $dowHash.($day) -Value $hours -InputObject $summary


$table += $summary

# Get TOTAL hours for final HTML report

$totalHours = ($SEappts | Measure-Object -Sum -Property duration).sum /60

if ($csv)


 $table | Export-Csv -NoTypeInformation -Path “$HOMEdesktopOutlookTimeReport.csv”

 Invoke-Item “$HOMEdesktopOutlookTimeReport.csv”




 $totalpost = “<table><tr><td><b>TOTAL HOURS:</b></td><td><b>$($totalHours)</b></td></tr><table>”

 $seHTML = $table | ConvertTo-Html -PostContent $totalpost | Out-File “$HOMEdesktopOutlookTimeReport.html”

 Invoke-Item “$HOMEdesktopOutlookTimeReport.html”



Share on
More posts

Dedicated Servers Quick Guide

What is a Dedicated Servers? Why bother using a dedicated server over a VPS or Shared Hosting?A dedicated server is a server 100% dedicated to your website/project or business needs.

PowerShell your world cup

PowerShell This is something that is very exciting and if you are interested you should try it. I found the post and tried it. Here is a function and how

Get 90% Discount

First 3 People gets the Bonus!
Don't Miss Out Our Big Sale

Get 0-90% On All
247Rack Services


The Sale Is Until The End Of March