#######################################################
# GLOBAL Script
# *** ReCertification ***
#
# CMD :
# C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
# https://gallery.technet.microsoft.com/scriptcenter/Outlook-Automation-using-d7584688
# -File P:\Test\Powershell\Script\Recertification\ManagingEmails
###################################
Write-Host "/// --- \\\" -ForegroundColor Cyan
Write-Host "Start scanning Emails on Inbox 'ISEC-ISS'..." -ForegroundColor Cyan
# Warning Email
$Outlook = New-Object -ComObject Outlook.Application
$Mail = $Outlook.CreateItem(0)
$Mail.To = "ISEC-ISS-Controls@ca-cib.com" #"ISEC-ISS-Controls@ca-cib.com"
$Mail.Subject = "Recertification: Processing Emails started..."
$Mail.Body = "~~~~~~~ \\\\ START //// ~~~~~~~"
$Mail.Send()
# Date
$date = get-date -format d
$date = $date.ToString().Replace(“/”, “-”)
$time = get-date -format t
# $time = $time.ToString().Replace(":", "-")
$time = $time.ToString().Replace(" ", "")
Write-Host "Date:" $date $time -ForegroundColor Cyan
# Input by User 1 :
Write-Host "How old are emails (received time)? #0=Today; 1=From yesterday; ..." -BackgroundColor DarkMagenta
$NumberOfDays = Read-Host "Days"
# Input by User 2 :
Write-Host "What is the subject of emails are we looking for" -BackgroundColor DarkMagenta
Write-Host "RE: [POUR ACTION]" -ForegroundColor Cyan
Write-Host "RE: [RAPPEL] [POUR ACTION]" -ForegroundColor Cyan
Write-Host "RE: [ACTION REQUIRED]" -ForegroundColor Cyan
Write-Host "RE: [REMINDER] [ACTION REQUIRED]" -ForegroundColor Cyan
Write-Host "RE: [LAST REMINDER] [ACTION REQUIRED]" -ForegroundColor Cyan
Write-Host "RE: [DERNIER RAPPEL] [POUR ACTION]" -ForegroundColor Cyan
$Subject1 = Read-Host "Subject 1?"
$Subject2 = Read-Host "Subject 2?"
$Subject3 = Read-Host "Subject 3?"
$Subject4 = Read-Host "Subject 4?"
# # # # #
# Parameter Emails "received time:"
$Date = [DateTime]::Now.AddDays(-$NumberOfDays)
$ReceivedDays = $Date.tostring("MM/dd/yyyy")
# Parameter Subject:
# $subjectComparisonExpression = "Message de sécurité pour les utilisateurs du Système d’Information de CACIB / Security message for all CACIB Information System users"
# Connecting to Outlook SharedMailBox
$ol = New-Object -ComObject Outlook.Application
$ns = $ol.GetNamespace('mapi')
$mb = $ns.Stores['baptiste.george-prestataire@ca-cib.com'].GetRootFolder()
$inbox = $mb.Folders['Inbox']
$recipient = $ns.CreateRecipient('ISEC-ISS-Controls@ca-cib.com')
if($recipient.Resolve()){
# Write-Host 'OK'
$shared = $ns.GetSharedDefaultFolder($recipient, [Microsoft.Office.Interop.Outlook.OlDefaultFolders]::olFolderInbox)
}else{
Write-Host 'Unable to resolve. Can not connect to sharedMailBox ISEC-ISS!'
}
Write-Host "Query is running" -ForegroundColor Cyan
################################### Filter Emails #######################################
# $messages = $inbox.items
# $messages = $shared.items | Where-Object -FilterScript {$_.senton -Ge "$ReceivedDays" -and $_.Subject -eq $Subject1}
$messages = $shared.items | Where-Object -FilterScript {
$_.senton -Ge "$ReceivedDays" -and ( ($_.Subject.SubString(0,[math]::min($Subject1.length,$_.Subject.length) ) -eq $Subject1) -or
($_.Subject.SubString(0,[math]::min($Subject2.length,$_.Subject.length) ) -eq $Subject2) -or
($_.Subject.SubString(0,[math]::min($Subject3.length,$_.Subject.length) ) -eq $Subject3) -or
($_.Subject.SubString(0,[math]::min($Subject4.length,$_.Subject.length) ) -eq $Subject4)
)}
# Query by User result :
# Count Emails into Inbox :
write-host "Query Result:" $messages.count "Emails were found in Inbox 'ISEC-ISS'" -ForegroundColor Green
$messcount = $messages.count
# Init:
$countprocessed = 0
$countprocessed_PJ = 0
# for ($c = $mailboxFolders.Items.count; $c -ge 1;$c--) {
################################### Starting #######################################
foreach($message in $messages){
# $From = $message.SenderEmailAddress
$msubject = $message.subject
Write-Host "Email found:" -BackgroundColor Blue
Write-Host $msubject -BackgroundColor Blue
$mBody = $message.body
# Write-Host $mBody
################################### Save Attchement #######################################
$filepath = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Fichiers Retour\"
$Check_PJ = 0
$message.attachments | foreach {
$attr = $_.filename
# add-content $log1 "Attachment: $attr"
$a = $_.filename
If ($a.Contains(".xlsx")) {
Write-Host $_.filename " --> The file has been saved on folder!"-BackgroundColor Green
$_.saveasfile((Join-Path $filepath $a))
$Check_PJ ++
$countprocessed_PJ ++
}
}
# In case you want to resend the file, into a new mail
$attachment = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Fichiers Retour\" + $a
### C'était ici
# Moving Emails to Questions as emails doesn't contain file.xlxs
If ($Check_PJ -eq 0) {Write-Host "Warning: No file found" -BackgroundColor Yellow
$MoveTarget = $shared.Folders.item("23 - Recertification").Folders.item("Temp_Question")
[void]$message.Move($MoveTarget)
}
else {
################################### Moving ALL EMAILS to Temp Folder #######################################
$MoveTarget1 = $shared.Folders.item("23 - Recertification").Folders.item("Processed")
[void]$message.Move($MoveTarget1)
}
$countprocessed++
} # End
#Synthèse
Write-Host "Result :" -ForegroundColor Cyan
If ( $countprocessed - $countprocessed_PJ -ge 1) {
$count_Empty_emails = $countprocessed - $countprocessed_PJ
Write-Host "-->" $count_Empty_emails "Email(s) do not contain any file!" -ForegroundColor Yellow
}
Write-Host "-->" $countprocessed_PJ "Files have been successfully saved on folder!" -ForegroundColor Green
write-host "-->" $messages.count "Emails have been moved to specific folders!" -ForegroundColor Green
Write-Host "End processing Emails from Inbox" -ForegroundColor Cyan
Write-Host "\\\ --- ///" -ForegroundColor Cyan
# Playing wav sound
$musicPath = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Sounds\Powershell Notification.wav"
$Song = New-Object System.Media.SoundPlayer
$Song.SoundLocation = $musicPath
$Song.Play()
####################################################### PROMPT : DO YOU WANT TO PERFORM NEXT STEP ?
$Title = "Recertification Process"
$Info = "Do you want to perform next step?"
$options = [System.Management.Automation.Host.ChoiceDescription[]] @("&Yes", "&No")
[int]$defaultchoice = 0
$opt = $host.UI.PromptForChoice($Title , $Info , $Options,$defaultchoice)
switch($opt)
{
# Option Yes
0 {
Write-Host "/// --- \\\" -ForegroundColor Cyan
Write-Host "Start scanning emails from 'Temp' Folder..." -ForegroundColor Cyan
$ScriptRoute = "P:\Test\Powershell\Script\Recertification\"
$ScriptName = "ScanningEmailsTempFolder.ps1"
# The script to call
$CallingScript = "$ScriptRoute"+"$ScriptName"
Write-Host $CallingScript
# Execute script at location:
&"$CallingScript"
}
# option No
1 { Write-Host "Ending process" -ForegroundColor Green
Break
}
}
####################################################### END PROMPT
#######################################################
# Script ReCertification
# *** Save Attachements ***
#
# CMD :
# C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
# https://gallery.technet.microsoft.com/scriptcenter/Outlook-Automation-using-d7584688
# -File P:\Test\Powershell\Script\Recertification\ScanningEmailsTempFolder
###################################
# Connecting to Outlook SharedMailBox
$ol = New-Object -ComObject Outlook.Application
$ns = $ol.GetNamespace('mapi')
$mb = $ns.Stores['baptiste.george-prestataire@ca-cib.com'].GetRootFolder()
$inbox = $mb.Folders['Inbox']
$recipient = $ns.CreateRecipient('ISEC-ISS-Controls@ca-cib.com')
if($recipient.Resolve()){
# Write-Host 'OK'
$shared = $ns.GetSharedDefaultFolder($recipient, [Microsoft.Office.Interop.Outlook.OlDefaultFolders]::olFolderInbox)
}else{
Write-Host 'Unable to resolve. Can not connect to sharedMailBox ISEC-ISS!'
}
# Write-Host "Query is running" -ForegroundColor Cyan
################################### Filter Emails #######################################
# $messages = $inbox.items
# $messages = $shared.items | Where-Object -FilterScript {$_.senton -Ge "$ReceivedDays" -and $_.Subject -eq $Subject1}
$messages = $shared.Folders.item("23 - Recertification").Folders.item("Temp").items
# Count Emails into Inbox :
$messcount = $messages.count
write-host "Result :" -ForegroundColor Cyan
write-host "-->" $messcount "Emails were found in 'Temp' Folder" -ForegroundColor Green
# Init:
$countprocessed = 0
$countprocessed_PJ = 0
################################### Starting #######################################
foreach($message in $messages){
# $From = $message.SenderEmailAddress
$msubject = $message.subject
Write-Host "Email found:" -BackgroundColor Blue
Write-Host $msubject -BackgroundColor Blue
$mBody = $message.body
# Write-Host $mBody
################################### Save Attchement #######################################
$filepath = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Fichiers Retour\"
$Check_PJ = 0
$message.attachments | foreach {
$attr = $_.filename
# add-content $log1 "Attachment: $attr"
$a = $_.filename
If ($a.Contains(".xlsx")) {
Write-Host $_.filename " --> The file has been saved on folder!"-BackgroundColor Green
$_.saveasfile((Join-Path $filepath $a))
$Check_PJ ++
$countprocessed_PJ ++
}
}
$countprocessed++
} # End
#Synthèse
Write-Host "Result" -ForegroundColor Cyan
If ( $countprocessed - $countprocessed_PJ -ge 1) {
$count_Empty_emails = $countprocessed - $countprocessed_PJ
Write-Host $count_Empty_emails "Email(s) do not contain any file!" -BackgroundColor Yellow
}
Write-Host "-->" $countprocessed_PJ "Files have been successfully saved on folder!" -ForegroundColor Green
# Il faut faire un backward Loop pour ne pas itérer sur la collection pour réussir à .Move les emails dans un autre folder.
# Alors que quand on fait .Move dans Foreach $message depuis le Inbox... le problème ne se pose pas! : (
# En tout cas ca marche.
# ==> En fait il faut fair un backward quand on travaille à partir d'un folder... Mais Il faut le faire en loop croissant quand on travaille à partir de l'inbox!
$messages = $shared.Folders.item("23 - Recertification").Folders.item("Temp").items
# Count Emails into Inbox :
$messcount = $messages.count
$moveTarget = $shared.Folders.item("23 - Recertification").Folders.item("Processed")
for ($c = $messcount; $c -ge 1;$c--) {
[void]$messages[$c].Move($moveTarget) # L'Index
}
Write-Host "-->" $messcount "Emails moved to 'Processed' Folder" -ForegroundColor Green
Write-Host "End processing Emails from 'Temp' Folder" -ForegroundColor Cyan
Write-Host "\\\ --- ///" -ForegroundColor Cyan
Write-Host "/// --- \\\" -ForegroundColor Cyan
Write-Host "Start scanning files saved on Folder..." -ForegroundColor Cyan
$Pathfiles = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Fichiers Retour\"
$PJSaved = (Get-ChildItem -File $Pathfiles | Measure-Object).Count
Write-Host "Result : " -ForegroundColor Cyan
Write-Host $PJSaved "Files found on folder" -ForegroundColor Green
Write-Host "\\\ --- ///" -ForegroundColor Cyan
# Playing wav sound
$musicPath = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Sounds\Powershell Notification.wav"
$Song = New-Object System.Media.SoundPlayer
$Song.SoundLocation = $musicPath
$Song.Play()
####################################################### PROMPT : DO YOU WANT TO PERFORM NEXT STEP ?
$Title = "Recertification Process"
$Info = "Do you want to perform next step?"
$options = [System.Management.Automation.Host.ChoiceDescription[]] @("&Yes", "&No")
[int]$defaultchoice = 0
$opt = $host.UI.PromptForChoice($Title , $Info , $Options,$defaultchoice)
switch($opt)
{
# Option Yes
0 {
Write-Host "/// --- \\\" -ForegroundColor Cyan
Write-Host "Step 2 is running..." -ForegroundColor Green
Write-Host "Let's check files name..." -ForegroundColor Green
$ScriptRoute = "P:\Test\Powershell\Script\Recertification\"
$ScriptName = "RenameFilesWithDataFromSheet.ps1"
# The script to call
$CallingScript = "$ScriptRoute"+"$ScriptName"
Write-Host $CallingScript
# Execute script at location:
&"$CallingScript"
}
# option No
1 { Write-Host "Ending process : Shutdown by User!" -ForegroundColor Green
Break
}
}
####################################################### END PROMPT
#######################################################
# Script ReCertification
# Stepx RenameFiesWithDataFromSheet
# Renommer un fichier avec le "prénom.nom - Rows.xlsx" _> "baptiste.george - 4.xlsx"
#
#
# CMD :
# C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
# -File P:\Test\Powershell\Script\Recertification\RenameFilesWithDataFromSheet.ps1
#######################################################
$Pathfiles = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Fichiers Retour\"
$folderDestination = "$Pathfiles" + "_Rejected\"
$Excel = New-Object -ComObject Excel.Application
Write-Host "/// --- \\\" -ForegroundColor Cyan
Write-Host "Start processing files..." -ForegroundColor Cyan
$Files = Get-ChildItem $Pathfiles"*.xlsx" | Select -Expand FullName
$counter = 1
ForEach($File in $Files){
Write-Progress -Activity "Checking: $file" -Status "File $counter of $($files.count)" -PercentComplete ($counter*100/$files.count)
$Workbook = $Excel.Workbooks.Open($File)
$WorkSheets = $WorkBook.WorkSheets | where {$_.name -eq "ISS"}
# On traite les fichiers seulement si les fichiers contiennent la feuille ISS
if($WorkSheets) {
$WorkSheet = $WorkBook.Sheets.Item("ISS")
$Rows = ($WorkSheet.UsedRange.Rows).count-1
$val1 = $Workbook.Sheets.Item("ISS").Cells.item(2,7).Value2
# Write-Host $val1
$Position = $val1.indexof("@")
# Write-host $Position
$len = $val1.Length
# Write-host $len
if ($val1 -eq "-") { Write-Host "Error : " -BackgroundColor Magenta } else {
$val = $val1.substring(0,$val1.length -($len-$Position)) }
# Write-host $val
$Workbook.close($false)
$NewNameFile = "$val" + " - " + "$Rows"
$Path_NewNameFile = "$Pathfiles"+"$NewNameFile"+".xlsx"
# Write-host $File
if ($val1 -eq "-") { Write-Host "Manager is missing : "$File -BackgroundColor Magenta } Else {
if ($File -eq "$Path_NewNameFile"){Write-Host $NewNameFile'.xlsx --> No need to rename this file' -BackgroundColor Blue }
Else{
# Vérification Si le fichier n'existe pas déjà
If (Test-Path $Path_NewNameFile){
Write-Host "Error : " -BackgroundColor Yellow
Write-Host $File -BackgroundColor Yellow
Write-Host $NewNameFile'.xlsx already exists' -BackgroundColor Yellow
}
Else{ # Sinon on peut renommer le file
Rename-Item -Path $File -NewName $NewNameFile'.xlsx'
Write-Host "Processing File : " -BackgroundColor Green
Write-Host $File -BackgroundColor Green
Write-Host $NewNameFile'.xlsx --> This file has been renamed successfully' -BackgroundColor Green
}
}
}
# End ISS Worksheet
} Else { Write-Host "Warning :" -BackgroundColor Red
Write-Host $File' --> This file has been rejected!' -BackgroundColor Red
# Déplacer les fichiers :
$Workbook.close($false)
Move-Item -Path $File -Destination $folderDestination -Force}
$counter++
}
get-process *excel* | stop-process -force
# Playing wav sound
$musicPath = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Sounds\Powershell Notification.wav"
$Song = New-Object System.Media.SoundPlayer
$Song.SoundLocation = $musicPath
$Song.Play()
Write-Host "End process" -ForegroundColor Cyan
Write-Host "\\\ --- ///" -ForegroundColor Cyan
####################################################### PROMPT : DO YOU WANT TO PERFORM NEXT STEP ?
$Title = "Recertification Process"
$Info = "Do you want to perform next step?"
$options = [System.Management.Automation.Host.ChoiceDescription[]] @("&Yes", "&No")
[int]$defaultchoice = 0
$opt = $host.UI.PromptForChoice($Title , $Info , $Options,$defaultchoice)
switch($opt)
{
# Option Yes
0 {
Write-Host "/// --- \\\" -ForegroundColor Cyan
Write-Host "Step 3 is running :" -ForegroundColor Green
Write-Host "Let's check if files are completed..." -ForegroundColor Green
$ScriptRoute = "P:\Test\Powershell\Script\Recertification\"
$ScriptName = "Step5_Recertif_Files_ToBeRemoved.ps1"
# The script to call
$CallingScript = "$ScriptRoute"+"$ScriptName"
Write-Host $CallingScript
# Execute script at location:
&"$CallingScript"
}
# option No
1 { Write-Host "Ending process" -ForegroundColor Green
Break
}
}
####################################################### END PROMPT
#######################################################
# Script ReCertification
# Step5_Recertif_Files_Incompleted
# Recertification Déplacer des fichiers incomplets:
#
#
# CMD :
# C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
# -File P:\Test\Powershell\Script\Recertification\Step5_Recertif_Files_ToBeRemoved.ps1
#######################################################
Write-Host "/// --- \\\" -ForegroundColor Cyan
Write-Host "Start processing files..." -ForegroundColor Cyan
# Déplace les fichiers .xlsx qui ont un text = "removed" dans la colonne E de l'onglet 2
$Pathfiles = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Fichiers Retour\"
$Destination = "$Pathfiles" + "_Not Completed\"
$SearchText = '' # 'removed'
$Excel = New-Object -ComObject Excel.Application
$Files = Get-ChildItem $Pathfiles"*.xlsx" | Select -Expand FullName
$counter = 1
$counterFileMoved = 0
ForEach($File in $Files){
Write-Progress -Activity "Checking: $file" -Status "File $counter of $($files.count)" -PercentComplete ($counter*100/$files.count)
$Workbook = $Excel.Workbooks.Open($File)
$WorkSheet = $WorkBook.Sheets.Item("ISS")
$Rows = ($WorkSheet.UsedRange.Rows).count
$Range = "E1:E" + "$Rows"
# Write-Host $Rows
# If($Workbook.Sheets.Item(1).Range("E:E").Find($SearchText)){
If($Workbook.Sheets.Item("ISS").Range($Range).Find($SearchText)){
$counterFileMoved++
# Pas de text trouvé, on ferme le fichier
$Workbook.Close($false)
# Si le texte recherché est trouvé alors on déplace les fichiers dans un nouveau dossier : )
# Moved $file to $destination"
Move-Item -Path $File -Destination $Destination -Force
Write-Host $counterFileMoved "Files has been moved to 'Not_Completed' Folder" -BackgroundColor Yellow
# break
}
else {
$workbook.close($false)
}
$counter++
}
# Synthèse du traitement
If( $counterFileMoved -eq 0 ){ Write-Host "All files are completed !!!" -BackgroundColor Green} else
{
$Resultat = $counter-$counterFileMoved-1
Write-Host "$Resultat" "files are completed !!!" -BackgroundColor Green
}
Write-Host "End process" -ForegroundColor Cyan
Write-Host "\\\ --- ///" -ForegroundColor Cyan
#######################################################
# *** On .txt -> List all the files into folder ***
# -File P:\Test\Powershell\Script\Recertification\GLOBAL_PROCESS_Recertification.ps1
#######################################################
Write-Host "/// --- \\\" -ForegroundColor Cyan
Write-Host "Start creating txt file..." -ForegroundColor Cyan
$Folder = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Fichiers Retour\" #__TEST\"
$FolderResultat = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Fichiers Retour\"
$OutFile = "__ListeFichiersEnRetour.txt"
$FolderOutFile = "$FolderResultat" + "$OutFile"
# Avant : On vérifie si le fichier existe, si oui... on supprime.
If (Test-Path $FolderOutFile){
Remove-Item $FolderOutFile
}
# Create txt file
Get-ChildItem -Path $Folder*.xlsx | %{ADD-content -path $FolderOutFile -value $_.FullName}
# Playing wav sound
$musicPath = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Sounds\Powershell Notification.wav"
$Song = New-Object System.Media.SoundPlayer
$Song.SoundLocation = $musicPath
$Song.Play()
Write-Host "End process" -ForegroundColor Cyan
Write-Host "\\\ --- ///" -ForegroundColor Cyan
####################################################### PROMPT : DO YOU WANT TO PERFORM NEXT STEP ?
$Title = "Recertification Process"
$Info = "Do you want to perform next step?"
$options = [System.Management.Automation.Host.ChoiceDescription[]] @("&Yes", "&No")
[int]$defaultchoice = 0
$opt = $host.UI.PromptForChoice($Title , $Info , $Options,$defaultchoice)
switch($opt)
{
# Option Yes
0 {
Write-Host "/// --- \\\" -ForegroundColor Cyan
Write-Host "Step 4 is running..." -ForegroundColor Green
Write-Host "Let's merge files..." -ForegroundColor Green
$ScriptRoute = "P:\Test\Powershell\Script\Recertification\"
$ScriptName = "MergeCompletedFiles.ps1"
# The script to call
$CallingScript = "$ScriptRoute"+"$ScriptName"
Write-Host $CallingScript
# Execute script at location:
&"$CallingScript"
}
# option No
1 { Write-Host "Ending process" -ForegroundColor Green
Break
}
}
####################################################### END PROMPT
#######################################################
# Script - ReCertification - Merging Files
# *** Merging Files ***
#
# CMD :
# C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
# -File P:\Test\Powershell\Script\Recertification\MergeCompletedFiles.ps1
#######################################################
Write-Host "/// --- \\\" -ForegroundColor Cyan
Write-Host "Starting Merging Files" -ForegroundColor Cyan
# Write-Host "Minimize Windows and fill the prompt" -ForegroundColor Yellow
# Step 01 : Call Excel File
$excel= new-object -comobject excel.application
$excel.Visible = $false
$excel.DisplayAlerts = $false
$classeur=$excel.workbooks.open("\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\_Lot_Concat\_Fusion_Fichier_Excel.xlsm")
# Test pouir lancer une macro
$excel.Run('Fusion_classeur_Excel')
# get-process *excel* | stop-process -force
# Playing wav sound
$musicPath = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Sounds\Powershell Notification.wav"
$Song = New-Object System.Media.SoundPlayer
$Song.SoundLocation = $musicPath
$Song.Play()
Write-Host "All files have been merged successfully" -ForegroundColor Green
Write-Host "End process" -ForegroundColor Cyan
Write-Host "\\\ --- ///" -ForegroundColor Cyan
####################################################### PROMPT : DO YOU WANT TO PERFORM NEXT STEP ?
$Title = "Recertification Process : List Email Address"
$Info = "Do you want to perform next step?"
$options = [System.Management.Automation.Host.ChoiceDescription[]] @("&Yes", "&No")
[int]$defaultchoice = 0
$opt = $host.UI.PromptForChoice($Title , $Info , $Options,$defaultchoice)
switch($opt)
{
# Option Yes
0 {
Write-Host "/// --- \\\" -ForegroundColor Cyan
Write-Host "Step 5 is running..." -ForegroundColor Green
Write-Host "Let's list Email Address..." -ForegroundColor Green
$ScriptRoute = "P:\Test\Powershell\Script\Recertification\"
$ScriptName = "ListEmailAddress.ps1"
# The script to call
$CallingScript = "$ScriptRoute"+"$ScriptName"
Write-Host $CallingScript
# Execute script at location:
&"$CallingScript"
}
# option No
1 { Write-Host "Ending process" -ForegroundColor Green
Break
}
}
####################################################### END PROMPT
#######################################################
# Script ReCertification
# *** Step7_Recertif List Email Address ***
#
# CMD :
# C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
# -File P:\Test\Powershell\Script\Recertification\ListEmailAddress.ps1
#######################################################
# Write-Host "/// --- \\\" -ForegroundColor Cyan
# Write-Host "Start listing Email address..." -ForegroundColor Cyan
$ol = New-Object -ComObject Outlook.Application
$ns = $ol.GetNamespace('mapi')
$mb = $ns.Stores['baptiste.george-prestataire@ca-cib.com'].GetRootFolder()
$inbox = $mb.Folders['Inbox']
$recipient = $ns.CreateRecipient('ISEC-ISS-Controls@ca-cib.com')
if($recipient.Resolve()){
# Write-Host 'OK'
$shared = $ns.GetSharedDefaultFolder($recipient, [Microsoft.Office.Interop.Outlook.OlDefaultFolders]::olFolderInbox)
}else{
Write-Host 'Unable to resolve. Can not connect to sharedMailBox ISEC-ISS!'
}
Write-Host "Start creating txt file..." -ForegroundColor Cyan
# Input by User 1 :
# Write-Host "What is the Subfolder?" -BackgroundColor DarkMagenta
# $Subfolder = Read-Host "Name"
# Input by User 2 :
# Write-Host "What is the Lot?" -BackgroundColor DarkMagenta
# $Lot = Read-Host "Number"
# Go to Subfolder :
$targetfolder1 = $shared.Folders | where-object { $_.name -eq "23 - Recertification" }
$targetfolder2 = $targetfolder1.Folders | where-object { $_.name -eq "Processed" }
# $shared = $ns.GetSharedDefaultFolder($recipient, [Microsoft.Office.Interop.Outlook.OlDefaultFolders]::olFolderInbox)
# Txt File
$FolderOutFile ="\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Emails Retour\_EmailAddress.txt"
# Delete file :
If (Test-Path $FolderOutFile){Remove-Item $FolderOutFile}
#checks 15 newest messages
# $shared.items | select -first 15 | foreach { # Inbox of SharedMailBox
$targetfolder2.items | select -first 1000 | foreach {
$Position = $_.SenderEmailAddress.indexof("@") #If = -1 --> Introuvable
# Write-Host = $Position
$date = Get-Date -Format "yyyy-MM-dd"
# Write-Host = $date
If ($Position -eq -1) {$Email_SenderName = $_.Sender.GetExchangeUser().PrimarySmtpAddress} else {$Email_SenderName = $_.SenderEmailAddress}
#"$($_.Sender.GetExchangeUser().PrimarySmtpAddress);1;$Lot;$date;$($_.Subject)" | out-file $FolderOutFile -Append else #
"$($Email_SenderName);$($_.Subject)" | out-file $FolderOutFile -Append
}
Write-Host "End process" -ForegroundColor Cyan
Write-Host "\\\ --- ///" -ForegroundColor Cyan
# Happy End
# Playing wav sound
$musicPath = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Sounds\Powershell Notification.wav"
$Song = New-Object System.Media.SoundPlayer
$Song.SoundLocation = $musicPath
$Song.Play()
####################################################### PROMPT : DO YOU WANT TO PERFORM NEXT STEP ?
$Title = "Recertification Process"
$Info = "Do you want to perform next step?"
$options = [System.Management.Automation.Host.ChoiceDescription[]] @("&Yes", "&No")
[int]$defaultchoice = 0
$opt = $host.UI.PromptForChoice($Title , $Info , $Options,$defaultchoice)
switch($opt)
{
# Option Yes
0 {
Write-Host "/// --- \\\" -ForegroundColor Cyan
Write-Host "Start scanning emails from 'Processed' Folder..." -ForegroundColor Cyan
$ScriptRoute = "P:\Test\Powershell\Script\Recertification\"
$ScriptName = "ListSenderEmails.ps1"
# The script to call
$CallingScript = "$ScriptRoute"+"$ScriptName"
Write-Host $CallingScript
# Execute script at location:
&"$CallingScript"
}
# option No
1 { Write-Host "Ending process" -ForegroundColor Green
Break
}
}
####################################################### END PROMPT
#######################################################
# Script ReCertification
# *** Scanning Emails to extract data ***
# Saving Emails as .msg -> But can't open it! ; (
#
# CMD :
# C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
# https://gallery.technet.microsoft.com/scriptcenter/Outlook-Automation-using-d7584688
# -File P:\Test\Powershell\Script\Recertification\ListSenderEmails.ps1
###################################
Write-Host "/// --- \\\" -ForegroundColor Cyan
write-host "Scanning Emails to extract data :" -ForegroundColor Cyan
# Input by User 1 :
# Write-Host "Lot1 (traité)" -ForegroundColor Cyan
# Write-Host "What folder on Outlook?" -BackgroundColor DarkMagenta
# $OutlookFolder = Read-Host "Folder"
$OutlookFolder = "Processed"
# Folder to save .msg
# $FolderDest = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\_Lot1\Test\"
# Txt File
$FolderOutFile ="\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Emails Retour\_InfoFromEmails_"+"$OutlookFolder"+".txt"
# Delete file :
If (Test-Path $FolderOutFile){Remove-Item $FolderOutFile}
# Connecting to Outlook SharedMailBox
$ol = New-Object -ComObject Outlook.Application
$ns = $ol.GetNamespace('mapi')
$mb = $ns.Stores['baptiste.george-prestataire@ca-cib.com'].GetRootFolder()
$inbox = $mb.Folders['Inbox']
$recipient = $ns.CreateRecipient('ISEC-ISS-Controls@ca-cib.com')
if($recipient.Resolve()){
# Write-Host 'OK'
$shared = $ns.GetSharedDefaultFolder($recipient, [Microsoft.Office.Interop.Outlook.OlDefaultFolders]::olFolderInbox)
}else{
Write-Host 'Unable to resolve. Can not connect to sharedMailBox ISEC-ISS!'
}
################################### Filter Emails #######################################
# $messages = $inbox.items
# $messages = $shared.items | Where-Object -FilterScript {$_.senton -Ge "$ReceivedDays" -and $_.Subject -eq $Subject1}
$messages = $shared.Folders.item("23 - Recertification").Folders.item($OutlookFolder).items # | select -First 5
# Count Emails into Inbox :
$messcount = $messages.count
write-host "Result :" -ForegroundColor Cyan
write-host "-->" $messcount "Emails were found in 'Processed' Folder" -ForegroundColor Green
# txt FILE :
# Create Header ;
$("Sender;Nbre;Lot;Sent On;Subject;File1.xlsx;File2.xlsx;Msg1.xlsx;Msg2.xlsx") | out-file $FolderOutFile -Append
################################### Extraction Emails (sender, subject, body) #######################################
foreach($message in $messages){
# $From = $message.SenderEmailAddress
$msubject = $message.subject
Write-Host "Email found : Extracting data!" -BackgroundColor DarkGreen
Write-Host "Subject : " $msubject -BackgroundColor Green
$mBody = $message.body
# Write-Host $mBody
$mBody = $message.body
$mSentOn = $message.senton.ToString().Replace(“/”, “-”)
# Write-Host $mSentOn
################################### Get Description of Attchements xlsx & msg #######################################
# init
$Check_PJ = 0
$FileName1 =""
$FileName2 =""
$Check_Msg = 0
$FileNameMsg1 =""
$FileNameMsg2 =""
$message.attachments | foreach {
$attr = $_.filename
# add-content $log1 "Attachment: $attr"
$a = $_.filename
If ($a.Contains(".xlsx")) {
# Write-Host $_.filename -BackgroundColor Green
$Check_PJ ++
If ( $Check_PJ -eq 1 ) { $FileName1 = $_.filename
Write-Host " - " $FileName1 -BackgroundColor Blue}
If ( $Check_PJ -eq 2 ) { $FileName2 = $_.filename
Write-Host " - " $FileName2 -BackgroundColor Blue}
}
If ($a.Contains(".msg")) {
# Write-Host $_.filename -BackgroundColor Green
$Check_Msg ++
If ( $Check_msg -eq 1 ) { $FileNameMsg1 = $_.filename
Write-Host " - " $FileNameMsg1 -BackgroundColor Cyan}
If ( $Check_msg -eq 2 ) { $FileNameMsg2 = $_.filename
Write-Host " - " $FileNameMsg2 -BackgroundColor Cyan}
}
}
$Position = $message.SenderEmailAddress.indexof("@") #If = -1 --> Introuvable
# Write-Host = $Position
If ($Position -eq -1) {$Email_SenderName = $message.Sender.GetExchangeUser().PrimarySmtpAddress} else {$Email_SenderName = $message.SenderEmailAddress}
# Write into txt File :
"$($Email_SenderName);1;Lot9;$($mSentOn);$($msubject);$FileName1;$FileName2;$FileNameMsg1;$FileNameMsg2" | out-file $FolderOutFile -Append
################################### Save Emails as .msg #######################################
# Test :
# $PathFileName = "$FolderDest" + "$Email_SenderName" +".msg"
# $message.SaveAs($PathFileName, $olSaveType::olMSG)
# La savaugarde fonctionne... mais impossible d'ouvrir le fichier .msg dans le folder : (
}
# Waring Email
$Outlook = New-Object -ComObject Outlook.Application
$Mail = $Outlook.CreateItem(0)
$Mail.To = "ISEC-ISS-Controls@ca-cib.com" #"ISEC-ISS-Controls@ca-cib.com"
$Mail.Subject = "Recertification: Processing Emails has run successfully..."
$Mail.Body = "~~~~~~~ //// END \\\\ ~~~~~~~"
$Mail.Send()
# Playing wav sound
$musicPath = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Sounds\Powershell Notification.wav"
$Song = New-Object System.Media.SoundPlayer
$Song.SoundLocation = $musicPath
$Song.Play()
Write-Host "End process" -ForegroundColor Cyan
Write-Host "\\\ --- ///" -ForegroundColor Cyan
# END
Write-Host ""
Write-Host "Thank you for watching... All jobs have been performed successfully" -ForegroundColor Green
#######################################################
# Script ReCertification
# *** Recertif Copy Files to Folder ***
#
# CMD :
# C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
# -File P:\Test\Powershell\Script\Recertification\CopyFilesToFolder.ps1
#######################################################
##### FR CAMPAIGN :
# Param
$PathFiles = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\05 - Mails envoyés\01_FR\"
$FolderDest = $PathFiles+"_01 - EY\"
Write-Host "/// --- \\\" -ForegroundColor Cyan
Write-Host "Step 1 is running :" -ForegroundColor Green
$NbrEmails = (Get-ChildItem -File $Pathfiles | Measure-Object).Count
Write-Host "Start scanning" $NbrEmails "Emails from FR campaign" -ForegroundColor Green
# Param List to check :
$Current = Get-Content '\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\05 - Mails envoyés\FR_ListEmail.txt'
# Processing Files :
$Count = 0
foreach ($currentVal in $Current){
$File = $currentVal
$CopyFile = $PathFiles+$File
$PasteFile = $FolderDest+$File
If (Test-Path $CopyFile){
Copy-Item -Path $CopyFile -Destination $PasteFile -Force
Write-Host $File "has been copied successfully!" -BackgroundColor Blue
$Count++
}
}
Write-Host "Result : " -ForegroundColor Green
Write-Host $Count "Emails has been copied succesfully" -ForegroundColor Green
Write-Host "End process" -ForegroundColor Cyan
Write-Host "\\\ --- ///" -ForegroundColor Cyan
##### EN CAMPAIGN :
# Param
$PathFiles = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\05 - Mails envoyés\02_EN\"
$FolderDest = $PathFiles+"_01 - EY\"
Write-Host "/// --- \\\" -ForegroundColor Cyan
Write-Host "Step 2 is running :" -ForegroundColor Green
$NbrEmails = (Get-ChildItem -File $Pathfiles | Measure-Object).Count
Write-Host "Start scanning" $NbrEmails "Emails from EN campaign" -ForegroundColor Green
# Param List to check :
$Current = Get-Content '\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\05 - Mails envoyés\EN_ListEmail.txt'
# Processing Files :
$Count = 0
foreach ($currentVal in $Current){
$File = $currentVal
$CopyFile = $PathFiles+$File
$PasteFile = $FolderDest+$File
If (Test-Path $CopyFile){
Copy-Item -Path $CopyFile -Destination $PasteFile -Force
Write-Host $File "has been copied successfully!" -BackgroundColor Blue
$Count++
}
}
Write-Host "Result : " -ForegroundColor Green
Write-Host $Count "Emails has been copied succesfully" -ForegroundColor Green
Write-Host "End process" -ForegroundColor Cyan
Write-Host "\\\ --- ///" -ForegroundColor Cyan
##### ENTITE CAMPAIGN :
# Param
$PathFiles = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\05 - Mails envoyés\03_Entité\"
$FolderDest = $PathFiles+"_01 - EY\"
Write-Host "/// --- \\\" -ForegroundColor Cyan
Write-Host "Step 3 is running :" -ForegroundColor Green
$NbrEmails = (Get-ChildItem -File $Pathfiles | Measure-Object).Count
Write-Host "Start scanning" $NbrEmails "Emails from Entité campaign" -ForegroundColor Green
# Param List to check :
$Current = Get-Content '\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\05 - Mails envoyés\Entité_ListEmail.txt'
# Processing Files :
$Count = 0
foreach ($currentVal in $Current){
$File = $currentVal
$CopyFile = $PathFiles+$File
$PasteFile = $FolderDest+$File
If (Test-Path $CopyFile){
Copy-Item -Path $CopyFile -Destination $PasteFile -Force
Write-Host $File "has been copied successfully!" -BackgroundColor Blue
$Count++
}
}
Write-Host "Result : " -ForegroundColor Green
Write-Host $Count "Emails has been copied succesfully" -ForegroundColor Green
Write-Host "End process" -ForegroundColor Cyan
Write-Host "\\\ --- ///" -ForegroundColor Cyan
##### EN CAMPAIGN :
# Param
$PathFiles = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\05 - Mails envoyés\02_EN\"
$FolderDest = $PathFiles+"_02 - UAR\"
Write-Host "/// --- \\\" -ForegroundColor Cyan
Write-Host "Step 2 is running :" -ForegroundColor Green
$NbrEmails = (Get-ChildItem -File $Pathfiles | Measure-Object).Count
Write-Host "Start scanning" $NbrEmails "Emails from EN campaign" -ForegroundColor Green
# Param List to check :
$Current = Get-Content '\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\05 - Mails envoyés\EN_ListEmail_UAR.txt'
# Processing Files :
$Count = 0
foreach ($currentVal in $Current){
$File = $currentVal
$CopyFile = $PathFiles+$File
$PasteFile = $FolderDest+$File
If (Test-Path $CopyFile){
Copy-Item -Path $CopyFile -Destination $PasteFile -Force
Write-Host $File "has been copied successfully!" -BackgroundColor Blue
$Count++
}
}
Write-Host "Result : " -ForegroundColor Green
Write-Host $Count "Emails has been copied succesfully" -ForegroundColor Green
Write-Host "End process" -ForegroundColor Cyan
Write-Host "\\\ --- ///" -ForegroundColor Cyan