69 lines
2.4 KiB
PowerShell
69 lines
2.4 KiB
PowerShell
#Declare data
|
|
Add-Type -Assembly "Microsoft.Office.Interop.Outlook"
|
|
$Data = Import-Excel -WorkSheetName "New_Outline" -NoHeader -DataOnly "\\thalesgroup.local\shares\Public\_TogD\03_Release_Management\Planning with I&O\2023\Weekly planning W08-W10-2023_MoM.xlsx"
|
|
#$Data = Import-Excel -WorkSheetName "New_Outline" -NoHeader -DataOnly "C:\Users\a.warme\Downloads\AlexScriptData2.xlsx"
|
|
$CsvData= Import-CSV -Path "C:\Users\a.warme\Documents\csvcheckfile\Outfile.csv"
|
|
|
|
#Declare Outlook information
|
|
$Ol = New-Object -ComObject Outlook.Application
|
|
|
|
#emails
|
|
$AllanEmail="allan.schwedler@urbanandmainlines.com"
|
|
$MartinEmail="martin.nielsen@urbanandmainlines.com"
|
|
$AlexanderEmail="alexander.warme@urbanandmainlines.com"
|
|
|
|
#P2 - Tasks
|
|
#P3 - Owner
|
|
#P4 - Platform
|
|
#P5 - DateTime
|
|
#P9 - Task Category
|
|
|
|
#foreach loop to create an appointment for each row in excel
|
|
foreach ($Row in $Data) {
|
|
if ($Row.P1 -eq "New"){continue}
|
|
|
|
if ($CsvData | Where-Object -Property Subject -eq $Row.P2 -WarningAction SilentlyContinue){continue}
|
|
|
|
|
|
# Import the Outlook interop assembly
|
|
$Appointment = $Ol.CreateItem(1)
|
|
$SubjectTitle = $Row.P2 + " " + $Row.P4 + " " + $Row.P9
|
|
$SubjectTitle
|
|
$Appointment.Subject = "$SubjectTitle"
|
|
$Appointment.Body = $Row.P2 + " " + $Row.P3 + " " + $Row.P4 + " " + $Row.P9
|
|
$Appointment.Location = $Row.P4
|
|
$Appointment.ReminderSet = $true
|
|
$Appointment.Importance = 1
|
|
$Appointment.MeetingStatus = [Microsoft.Office.Interop.Outlook.OlMeetingStatus]::olMeeting
|
|
|
|
#assign email to owner on appointment
|
|
|
|
switch($Row.P3) {
|
|
Allan {$Appointment.Recipients.Add($AllanEmail)}
|
|
|
|
Martin {$Appointment.Recipients.Add($MartinEmail)}
|
|
|
|
Alexander {$Appointment.Recipients.Add($AlexanderEmail)}
|
|
|
|
default {$Appointment.Recipients.Add($AlexanderEmail)}
|
|
}
|
|
|
|
$Appointment.ReminderMinutesBeforeStart = 15
|
|
$Appointment.Start = $Row.P5.AddHours(8)
|
|
$Appointment.Duration = 480
|
|
$Appointment.Send()
|
|
|
|
#consistency
|
|
|
|
$ArrayQ = @([PSCustomObject]@{Subject = $Row.P2 ; Id = 1 })
|
|
#Export - Append the data to the existing file ("ExportFile.csv") or create a new file if it does not exist
|
|
$ArrayQ | Export-CSV "C:\Users\a.warme\Documents\csvcheckfile\Outfile.csv" -Append -NoTypeInformation
|
|
|
|
}
|
|
|
|
#to destingush weeks and tasks
|
|
#regex this : Weekly planning ([A-Za-z0-9]+(-[A-Za-z0-9]+)+)_
|
|
# with file name to link previous tasks and weeks.
|
|
|
|
|
|
|