Powershell Outlook Time Reporting by Category

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”



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.

Software Defined Single Sign On Database

Software Defined Single Sign On Database. One difficult task since the release of vSphere 5.1 has been the creation of SSO (Single Sign On) databases.  Creating an SSO database is

Free eBooks from Microsoft Press

It is clear that Microsoft have available a books, which are free to download. These books provide an introduction and detailed information on some of their enterprise nad development products. They

cPanel 2-Factor Auth Bypass

2-Factor Authentication Bypass hole in WHM cPanel security has been Reported effected WHM resellers. cPanel, a software provider to manage web hosting/websites, has recently patched a security vulnerability that allowed