Export Office 365 Sent Mails to CSV via PowerShell

[PaddyB]

Hi

as a Office 365 Admin i try to export Export an Office 365 Mailbox-Sent Mails to CSV via PowerShell

found some scripts, but nothing works..

has anyone something working?

# One-time process: Install the Graph module
Install-Module Microsoft.Graph -Scope CurrentUser
# Or update the existing module to the latest version
# Update-Module Microsoft.Graph

# Check the cmdlets
# Get-InstalledModule Microsoft.Graph

Import-Module Microsoft.Graph.Mail

# Connect with Mail.Read permissions
Connect-MgGraph -Scopes "Mail.Read"
Connect-MgGraph -Scopes "User.Read.All","Group.ReadWrite.All","GroupMember.ReadWrite.All"


# Show the user context just as info
Get-MgContext

# get your user id - insert your own primary email address here
$user = Get-MgUser -Filter "UserPrincipalName eq 'email address removed for privacy reasons'"
# Get a list of all mail folders
$folders = Get-MgUserMailFolder -UserId $user.Id -All
# Select the Inbox
$inbox = $folders | Where-Object { $_.DisplayName -eq "Inbox" }
# Get a list of all sub folders of the Inbox
$childs = Get-MgUserMailFolderChildFolder -UserId $user.Id -MailFolderId $inbox.Id -All
# Select the desired folder
$myfolder = $childs | Where-Object { $_.DisplayName -eq "<your-subfolder>" }

# Get all mails and export them (add an optional where filter if needed).
# We remove all HTML tags, repair line breaks and HTML spaces to get a readable text in the result file.
Get-MgUserMailFolderMessage -All `
    -UserId $user.Id `
    -MailFolderId $myfolder.Id | `
    Select-Object `
    @{N = 'Received'; E = { $_.ReceivedDateTime } }, `
    @{N = 'Sender'; E = { $_.Sender.foreach{ ($_.Emailaddress) }.address } }, `
    @{N = 'ToRecipient'; E = { $_.ToRecipients.foreach{ ($_.Emailaddress) }.address } }, `
    @{N = 'ccRecipient'; E = { $_.ccRecipients.foreach{ ($_.Emailaddress) }.address } }, `
    @{N = 'Subject'; E = { $_.Subject } }, `
    @{N = 'Importance'; E = { $_.Importance } }, `
    @{N = 'Body'; E = { ($_.Body.Content -replace '</p>',"`r`n" -replace "<[^>]+>",'' -replace "&nbsp;",' ').trim() } } | `
    Where-Object {( ($_.Subject -notlike "*newsletter*") -and ($_.Subject -notlike "*FYI*") ) } | `
    Export-Csv ".\mails.csv" -Delimiter "`t" -Encoding utf8

# End. Check the mails.csv file.
# Best, open it with Microsoft Excel: Menu Data, From Text/CSV and follow the wizard.

# Disconnect when done
Disconnect-MgGraph

[#2]

Hello Paddy

If I understand, you want to export the sent email activity report of one of your users
Have you tried to do it from the admin center
https://admin.microsoft.com/Adminportal/Home?#/reportsUsage/EmailActivity

https://docs.microsoft.com/en-us/microsoft-365/admin/activity-reports/activity-reports?view=o365-wor...

https://docs.microsoft.com/en-us/microsoft-365/admin/activity-reports/email-activity-ww?view=o365-wo...

[#3]

hi, thanks for it. was able to do it with some fix on the above script.

[#4]

@PaddyB 

Hello, could you please share the modified script.

Thank you!

[#5]

@PaddyB 

You posted a script then you did not post the version that worked for you. Have a similar mess, Can you help and post the working script?

Thanks in advance

Greg

[#6]

@b2bww 

# One-time process: Install the Graph module
Install-Module Microsoft.Graph -Scope CurrentUser -Force
# Or update the existing module to the latest version
# Update-Module Microsoft.Graph

# Check the cmdlets
# Get-InstalledModule Microsoft.Graph

Import-Module Microsoft.Graph.Mail

# Connect with required permissions
Connect-MgGraph -Scopes "Mail.Read", "User.Read.All", "Group.ReadWrite.All", "GroupMember.ReadWrite.All"

# Show the user context just as info
Get-MgContext

# get your user id - insert your own primary email address here
$user = Get-MgUser -Filter "UserPrincipalName eq 'email address removed for privacy reasons'"

# Get a list of all mail folders
$folders = Get-MgUserMailFolder -UserId $user.Id -All

# Select the Inbox
$inbox = $folders | Where-Object { $_.DisplayName -eq "Inbox" }

# Get a list of all subfolders of the Inbox
$childs = Get-MgUserMailFolderChildFolder -UserId $user.Id -MailFolderId $inbox.Id -All

# Select the desired folder
$myfolder = $childs | Where-Object { $_.DisplayName -eq "<your-subfolder>" }

# Get all mails and export them (add an optional where filter if needed).
# We remove all HTML tags, repair line breaks and HTML spaces to get readable text in the result file.
Get-MgUserMailFolderMessage -All `
-UserId $user.Id `
-MailFolderId $myfolder.Id | ForEach-Object {
$_ | Select-Object `
@{N='Received'; E={$_.ReceivedDateTime}}, `
@{N='Sender'; E={$_.Sender.EmailAddress.Address}}, `
@{N='ToRecipient'; E={$_.ToRecipients.EmailAddress.Address}}, `
@{N='ccRecipient'; E={$_.CcRecipients.EmailAddress.Address}}, `
@{N='Subject'; E={$_.Subject}}, `
@{N='Importance'; E={$_.Importance}}, `
@{N='Body'; E={($_.Body.Content -replace '</p>', "`r`n" -replace '<[^>]+>', '' -replace '&nbsp;', ' ').Trim()}}
} | Where-Object {($_.Subject -notlike "*newsletter*") -and ($_.Subject -notlike "*FYI*")} | Export-Csv -Path ".\mails.csv" -Delimiter "`t" -Encoding utf8 -Force

# Disconnect when done
Disconnect-MgGraph

Changes made:

1. Added Force parameter: Added the -Force parameter to the Install-Module command to force the installation without prompting.

2. Corrected property access: Changed the property access for email addresses (EmailAddress.Address).

3. Revised the ForEach-Object block: Corrected the property names and removed unnecessary foreach loops.

Make sure to replace <your-subfolder> with the actual name of your subfolder. Additionally, ensure that you have the necessary permissions to access the specified mail folders.