Forum Discussion
How to Export all Messages from MS Outlook into CSV file using PowerShell?
Hi Guys,
How are you guys doing today? I am still new to Powershell and keep on learning it every day. I need some helps here, I need to export all MS Outlook messages to CSV files. I know this is easy for some of you guys. Here is my requirements:
1. Read all folders in MS Outlook in local system.
2. Display the Outlook folder path that contain the messages.
3. Count number of messages in each folder.
4. If folder got messages, then export the message items with this properties (SenderName, To, SentOn, ReceivedTime, Body) to a CSV file.
Here is what I got so far:
1. I managed to connect to the current MS Outlook session using Powershell.
2. I can iterate all the Outlook folders and count number of message items in each folders.
3. Create the CSV output files to hold the messages.
However I got zero content for my CSV output. I am having problem to get the Messages item properties from the OutlookNameSpace.
I know we can do it like below to get the properties out to CSV files, but how can I use it in my code because the Outlook folders name are dynamic ?
PS C:\WorkDev > $OutlookNameSpace.Folders.Item("Personal_PST").Folders.Item("UserName1").Folders.Item("Subfolder2.2").Folders.Item("Subfolder3.1").Items.count
6
PS C:\WorkDev > $OutlookNameSpace.Folders.Item(2).Folders.Item(2).Folders.Item(2).Folders.Item(1).Items.count
6
PS C:\WorkDev > $OutlookNameSpace.Folders.Item(2).Folders.Item(2).Folders.Item(2).Folders.Item(1).Items | Select-Object -Property SenderName, To, SentOn, ReceivedTime, Body | Export-Csv -Path CSV_OutputFile.csv -NoTypeInformation
Can anybody advice me on how to make this works? Thank you very much in advance. I really appreciate it.
Code:
Function Main
{
#Clear console screen
Clear-Host
Write-Host "PS Script Start! `n"
# Import assembly files
Add-Type -assembly "Microsoft.Office.Interop.Outlook"
# This creates a new COM (Component Object Model) Object to access Outlook with.
$Outlook = New-Object -ComObject Outlook.Application
# Map MAPI GetNameSpace to OutlookNameSpace
$OutlookNameSpace = $Outlook.GetNameSpace("MAPI")
# Iterate through all Outlook folders
foreach ($folder in $OutlookNameSpace.Folders)
{
Get-MailboxFolder $folder
}
Write-Host "`nPS Script End!"
}
Function Get-MailboxFolder($OutlookFolder)
{
#Get current date and time
$GetCurrentDateTime = (Get-Date -format "yyyyMMddHHmmss_UTCzz")
foreach ($f in $OutlookFolder.Folders)
{
Get-MailboxFolder $f
}
#If $OutlookFolder.Items.Count != 0
if($OutlookFolder.items.count -ne 0)
{
$OutlookFolderPath = $OutlookFolder.FolderPath
$OutlookFolderName = $OutlookFolder.name
$OutlookFolderItemsCount = $OutlookFolder.items.count
$ExportOutlookMessage = $OutlookFolder.Folders.items
# Create the CSV output file
$CSV_OutputFile = "OutFile_"+$OutlookFolderName+"_"+$GetCurrentDateTime+".csv"
$ExportOutlookMessage | Select-Object -Property SenderName, To, SentOn, ReceivedTime, Body | Export-Csv -Path $CSV_OutputFile -NoTypeInformation
Write-Host "Outlook Folder Path: $OutlookFolderPath"
Write-Host "Outlook Message Count: $OutlookFolderItemsCount"
Write-Host "CSV File Output: $CSV_OutputFile `n"
}
}
. Main
Here is my script run output:
PS Script Start!
Outlook Folder Path: \\Personal_PST\UserName1\Subfolder2.4
Outlook Message Count: 110
CSV File Output: OutFile_Subfolder2.4_20210125223011_UTC-06.csv
Outlook Folder Path: \\Personal_PST\UserName1\Subfolder2.2\Subfolder3.1
Outlook Message Count: 6
CSV File Output: OutFile_Subfolder3.1_20210125223011_UTC-06.csv
Outlook Folder Path: \\Personal_PST\UserName1\Subfolder2.3
Outlook Message Count: 143
CSV File Output: OutFile_Subfolder2.3_20210125223011_UTC-06.csv
Outlook Folder Path: \\Personal_PST\UserName1\Subfolder2.1\Deletions
Outlook Message Count: 27
CSV File Output: OutFile_Deletions_20210125223011_UTC-06.csv
Outlook Folder Path: \\Personal_PST\UserName1\Subfolder2.1\subfolder3.2
Outlook Message Count: 50
CSV File Output: OutFile_subfolder3.2_20210125223011_UTC-06.csv
PS Script End!
Regards,
Skylar 🙂
- Skylar2000Copper Contributor
Hi guys,
I am trying to break the problem in to small piece.
1. Now I have the "Outlook Folder Path: \\Personal_PST\UserName1\Subfolder2.1\subfolder3.2" output.
2. I want to use this method "OutlookNameSpace.Folders.Item("Personal_PST").Folders.Item("UserName1").Folders.Item("Subfolder2.2").Folders.Item("Subfolder3.1").Items"
3. So I need to process the Outlook folder path into variable and use it in the OutlookNameSpace.
Here is my code so far but I do not know how to make the result display horizontal. I am open for any advice. Thank you very much in advance.
Code:
#PS Script End
# Clear console
Clear-Host# Input Outlook folder path
$OutlookFolderPath = "\\Personal_PST\UserName1\Subfolder2.1\subfolder3.2"# Split Outlook folder by \ and store ito array $SplitOutlookFolderPath
$SplitOutlookFolderPath = $OutlookFolderPath.Split("\")Write-Host "Split Array Output: $SplitOutlookFolderPath"
$ArrayCount = $SplitOutlookFolderPath.Count
Write-Host "Split Array count : $ArrayCount `n"
foreach($FolderName in $SplitOutlookFolderPath)
{
#"$FolderName = " + $FolderName.lengthif($FolderName.Length -ne 0)
{
$NewFolderName = $FolderName
Write-Host "$NewFolderName"
}
}#Write-Host "`nOutlookNameSpace.Folders.Item(Personal_PST).Folders.Item(UserName1).Folders.Item(Subfolder2.2).Folders.Item(Subfolder3.1).Items`n"
#PS Script End
PS Script Output:
Split Array Output: Personal_PST UserName1 Subfolder2.1 subfolder3.2
Split Array count : 6Personal_PST
UserName1
Subfolder2.1
subfolder3.2I want the output to display like below:
Split Array Output: Personal_PST UserName1 Subfolder2.1 subfolder3.2
Split Array count : 6OutlookNameSpace.Folders.Item(Personal_PST).Folders.Item(UserName1).Folders.Item(Subfolder2.2).Folders.Item(Subfolder3.1).Items
- Aleksandr_ReznikCopper Contributor
You can try changing $ExportOutlookMessage = $OutlookFolder.Folders.items to $ExportOutlookMessage = $OutlookFolder.items
I also recommend to install Visual Studio Code and run powershell from it. You can add a breakpoint at place you need and after the program will pause on breakpoint review variable values in Watch window.
Example of working program which exports mails from Outlook to CSV: https://it4it.solutions/2022/06/13/exporting-mails-from-microsoft-outlook-to-csv-file-with-powershell/ - it lists folders recursively with number of items first, then it export emails from defined set of folders to single CSV file (subfolders included).