Forum Discussion

PaddyB's avatar
PaddyB
Brass Contributor
Aug 08, 2022

Export Office 365 Sent Mails to CSV via PowerShell

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
admin
office 365
    • PaddyB's avatar
      PaddyB
      Brass Contributor
      Aug 14, 2022
      hi, thanks for it. was able to do it with some fix on the above script.
  • b2bww's avatar
    b2bww
    Copper Contributor
    Nov 22, 2023

    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

    • NewarkComputers's avatar
      NewarkComputers
      Copper Contributor
      Nov 29, 2023

      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.

Resources