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

$objForm.Add_KeyDown({

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

 {

 $dtmDate=$objCalendar.SelectionStart

 $objForm.Close()

 }

 })

$objForm.Add_KeyDown({

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

 {

 $objForm.Close()

 }

 })

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

 $objCalendar.ShowTodayCircle = $False

 $objCalendar.MaxSelectionCount = 1

 $objForm.Controls.Add($objCalendar)

$objForm.Topmost = $True

$objForm.Add_Shown({$objForm.Activate()})

 [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.Sort(“[Start]”)

$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”

}

else

{

 $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”

}

Home

Share on
Facebook
Twitter
LinkedIn
Pinterest
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.

Get 90% Discount

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

Get 0-90% On All
247Rack Services

247Rack

The Sale Is Until The End Of March