PowerShell play with Outlook & Excel

PowerShell play with Outlook & Excel

Search, download attachments in Outlook

$outlook = New-Object -ComObject Outlook.Application
$namespace = $Outlook.GetNameSpace("MAPI")
$folders = $namespace.Folders

$report_received_folder = $folders["inbox"]
$items = $report_received_folder.items
$items.sort("[ReceivedTime]", $TRUE)  # order by received time descend
# Write-Host $items.Count

# filter by Friday
$last_saturday_str = $last_saturday.ToString($DDMMYYYY_SLASH)
$last_friday_str = $last_friday.ToString($DDMMYYYY_SLASH)
$filter_str = "[SentOn] > ""$last_friday_str"" and [SentOn] < ""$last_saturday_str"" and [From] = ""$REPORT_SENDER"""
$items = $items.Restrict($filter_str)
ForEach($item in $items) {
    $attachments = $item.attachments
    ForEach($att in $attachments) {
        $filename = $att.FileName
        if($filename -in $WORKING_FILES) {
            $subject = $item.Subject
            $receive_time = $item.ReceivedTime
            Log "$LOG_FILE" 'INFO' "`tProcessing '$filename' in Email '$subject' received at $receive_time"
            $att.SaveAsFile("$WORKING_DIR\$filename")
            $passwd = "password"
            & "$ZIP" x -y -o"$WORKING_DIR" -p"$passwd" "$WORKING_DIR\$filename" | Out-Null
            Del "$WORKING_DIR\$filename"
        }
    }
}

Open & manipulate Excel files

Note
Please refer to MS VBA reference document: Office VBA Reference

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $True
$excel.DisplayAlerts = $False

# auto filter
$last_row = $sheet.Cells($sheet.Rows.Count, 'A').End($xl.XlDirection.xlUp).Row
$sheet.Range("`$A1:`$AB${hir_last_row}").AutoFilter(28, "True") | Out-Null
$hir_sheet.Range("`$A2:`$AA${last_row}").SpecialCells($xl.XlCellType.xlCellTypeVisible).Copy() | Out-Null
$other_sheet.Range("`$A2").PasteSpecial($xl.XlPasteType.xlPasteValuesAndNumberFormats) | Out-Null
$hir_sheet.AutoFilter.ShowAllData()

# run micro
$excel.Calculation = $xl.XlCalculation.xlCalculationAutomatic
$excel.Run('YTD_Weekly')