A small Powershell script I created for exporting emails from MS Outlook to CSV file. It retrieves Date, Time, Subject, To, From, CC, BCC and Body fields. The reason i wrote this script is an Outlook’s Export to CSV function failure to perform export properly. It put internal X500 type of address (LegacyExchangeDN attribute) for non SMTP emails. My script doesn’t have this problem and resolves all X500 address to SMTP standard.
It uses Outlook API (Office Outlook Primary Interop Assembly) and requires MS Outlook to be installed on a system where you run the script.
Script has 4 variables defined at the beggining of a code. You may need to change some values to adopt script to your system.
$maxRecordToProcessForEachFolder – limit of records to process for each folder, Low values are good for trial runs.
$rootFolderName – Outlook’s root folder name. You can get this name from first run of a program – it will print root folders connected to Outlook. This also can be connected PST file name if you have any.
$pathToInteropDLL – in your computer you might need to locate Microsoft.Office.Interop.Outlook.dll and place path to it in this variable. $foldersToExport – set of folders (under the root folder) to perform export from. Will also include subfolders if they have any.
I also have next version which utilizes MS Graph API and reads mails directrly from Exchange online. It doesn’t require Outlook to be installed and works several times faster.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 |
$maxRecordToProcessForEachFolder = 100000 $rootFolderName ="user@maildomain.com" #name of a root folder to start from. Usually it is mailbox name or attached PST file name $pathToInteropDLL = "C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Outlook\15.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Outlook.dll" $foldersToExport = "Inbox","Sent Items","Deleted Items" $global:PSOobj4CSV = @() Function Listfolders { param($Folders, $Indent) ForEach ($Folder in $Folders | sort-object name) { write-host $Indent$($Folder.Name)" $($folder.FullFolderPath) - ("$($Folder.Items.Count)") FolderID: $($folder.FolderID)" Listfolders $Folder.Folders $Indent" " } } function Folder2PSO($folder, $parentfolderPath){ $currFolderPath = $parentfolderPath +"\"+ $folder.Name Write-Host "Start processing folder $($currFolderPath)" $childFolders = $folder.Folders if ($childFolders){ foreach($childFolder in $childFolders){ Folder2PSO $childFolder $currFolderPath } } $mails = $folder.Items $nrOfMails = $mails.Count $currMailNr = 0 foreach($currEmail in $mails){ $currMailNr++ Write-Host "$currFolder Mail $currMailNr from $nrOfMails" Write-Host " Recepients:" $recipientToList = @() $recipientCCList = @() $recipientBCCList = @() foreach ($recip in $currEmail.Recipients) { $smtpAddress = $recip.PropertyAccessor.GetProperty($PR_SMTP_ADDRESS) Switch ($recip.type){ 1{ $To $recipientToList += $smtpAddress } 2{ #CC $recipientCCList += $smtpAddress } 3{ #bcc $recipientBCCList += $smtpAddress } } Write-Host " $($recip.type) $smtpAddress" } $senderList = @() Write-Host " Senders:" if($currEmail.SenderEmailType -eq "EX"){ foreach ($sender in $currEmail.Sender) { $smtpAddress = $sender.PropertyAccessor.GetProperty($PR_SMTP_ADDRESS) $senderList += $smtpAddress Write-Host " $smtpAddress" } } Else{ $SenderList = $currEmail.SenderEmailAddress } $PSOline = [pscustomobject]@{ 'Folder' = $currEmail.Parent.FullFolderPath 'DateTimeSentOn' = $currEmail.SentOn 'Subject' = $currEmail.Subject 'SenderName' = $currEmail.SenderName 'SenderEmail' = $senderList -join ";" 'To' = $currEmail.To 'ToEmails' = $recipientToList -join ";" 'CC' = $currEmail.CC 'CCEmails' = $recipientCCList -join ";" 'BCC' = $currEmail.BCC 'BCCEmails' = $recipientBCCList -join ";" 'Body' = $currEmail.Body } $global:PSOobj4CSV += $PSOline If($currMailNr -ge $maxRecordToProcessForEachFolder){ write-host "Max record limit defined in maxRecordToProcessForEachFolder varible is reached. Stopping process this folder" break } } } try{ Add-Type -path $pathToInteropDLL } Catch{ Write-host "Problem locating interop DLL.. locate DLL manually and fill $pathToInteropDLL variable" -ForegroundColor Red exit } add-type -assembly "System.Runtime.Interopservices" try { $outlook = [Runtime.Interopservices.Marshal]::GetActiveObject('Outlook.Application') $outlookWasAlreadyRunning = $true } catch { try { $Outlook = New-Object -comobject Outlook.Application $outlookWasAlreadyRunning = $false } catch { write-host "Outlook is running. Please exit it program before running this script" exit } } $namespace = $Outlook.GetNameSpace("MAPI") $folderList = $namespace.Folders Write-Host write-host "Folders structure:" ListFolders $folderList $rootFolder = $folderList | where-object -property FolderPath -eq "\\$($rootFolderName)" $PR_SMTP_ADDRESS = "http://schemas.microsoft.com/mapi/proptag/0x39FE001E" foreach ($currFolder in $foldersToExport){ $currentTopFolder = $rootFolder.Folders | where-object -Property name -EQ $currFolder Folder2PSO $currentTopFolder "" } [string]$pathToSaveFiles = $PSScriptRoot +"\" $CurrDateTimeStr=[DateTime]::Now.ToString("yyyyMMdd-HHmmss") $pathToCSV = "$($pathToSaveFiles)$($CurrDateTimeStr)_$($rootFolderName)_exported.csv" $global:PSOobj4CSV|export-CSV $pathToCSV -NoTypeInformation -append -force write-host "CSV is written to $pathToCSV" #Get-Process "*outlook*" | Stop-Process |
