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