Office 365 - Export From Folder only sender and time/date stamps
April 26, 2023 11:51 AM Subscribe
Oof. In the old days, this would have been so much easier. I need export emails from Outlook from a particular folder and export to excel so can graph when sender has sent email based on time date stamp/header.
Mac is useless. Set up a parellels account to do via Windows with native Outlook client but when exporting, do not get the option to choose a time/date stamp. I end up with list of fields from/to but no date/time stamp.
It is beyond frustrating and with 365 I cannot see any info on the web that would allow me to do without purchasing a 3rd party product. Again, tried a couple on a .oml and .pst but zero success.
Am I missing something? And does anyone have any recommendations?
Have exhausted the google-fu on this one.
Mac is useless. Set up a parellels account to do via Windows with native Outlook client but when exporting, do not get the option to choose a time/date stamp. I end up with list of fields from/to but no date/time stamp.
It is beyond frustrating and with 365 I cannot see any info on the web that would allow me to do without purchasing a 3rd party product. Again, tried a couple on a .oml and .pst but zero success.
Am I missing something? And does anyone have any recommendations?
Have exhausted the google-fu on this one.
Heh, used my wife's ChatGPT account....
To export emails from a particular folder in Outlook Online and save them in an Excel file using PowerShell, you can use the Microsoft Graph API. First, you'll need to register an application and obtain the necessary access tokens. You can follow the steps mentioned in the official documentation: https://docs.microsoft.com/en-us/graph/auth-register-app-v2
After registering your application and obtaining the access tokens, you can use the following PowerShell script to export the emails to a CSV file. Make sure to replace the placeholders with your actual values (e.g., client ID, tenant ID, client secret, and folder ID).
# Required modules
Install-Module Microsoft.Graph.Authentication
Install-Module ImportExcel
# Variables
$clientId = ""
$tenantId = ""
$clientSecret = ""
$folderId = ""
$csvFileName = "exported_emails.csv"
# Authenticate with Microsoft Graph API
Connect-MgGraph -ClientId $clientId -TenantId $tenantId -ClientSecret $clientSecret
# Get emails from the specified folder
$messages = Get-MgUserMessage -Top 999 -Filter "folderId eq '$folderId'"
# Extract necessary information from the email messages
$emailData = $messages | ForEach-Object {
[PSCustomObject]@{
"Sender" = $_.Sender.EmailAddress.Address
"Subject" = $_.Subject
"SentDateTime" = $_.SentDateTime
}
}
# Export the email data to a CSV file
$emailData | Export-Csv -Path $csvFileName -NoTypeInformation
# Convert CSV to Excel
$csvData = Import-Csv -Path $csvFileName
$csvData | Export-Excel -Path "exported_emails.xlsx"
# Clean up
Remove-Item $csvFileName
This script will export the sender, subject, and sent date-time information from the specified folder and save it to an Excel file. You can then use the data in Excel to create graphs based on the date and time stamps.
posted by rozcakj at 12:13 PM on April 26, 2023 [1 favorite]
To export emails from a particular folder in Outlook Online and save them in an Excel file using PowerShell, you can use the Microsoft Graph API. First, you'll need to register an application and obtain the necessary access tokens. You can follow the steps mentioned in the official documentation: https://docs.microsoft.com/en-us/graph/auth-register-app-v2
After registering your application and obtaining the access tokens, you can use the following PowerShell script to export the emails to a CSV file. Make sure to replace the placeholders with your actual values (e.g., client ID, tenant ID, client secret, and folder ID).
# Required modules
Install-Module Microsoft.Graph.Authentication
Install-Module ImportExcel
# Variables
$clientId = "
$tenantId = "
$clientSecret = "
$folderId = "
$csvFileName = "exported_emails.csv"
# Authenticate with Microsoft Graph API
Connect-MgGraph -ClientId $clientId -TenantId $tenantId -ClientSecret $clientSecret
# Get emails from the specified folder
$messages = Get-MgUserMessage -Top 999 -Filter "folderId eq '$folderId'"
# Extract necessary information from the email messages
$emailData = $messages | ForEach-Object {
[PSCustomObject]@{
"Sender" = $_.Sender.EmailAddress.Address
"Subject" = $_.Subject
"SentDateTime" = $_.SentDateTime
}
}
# Export the email data to a CSV file
$emailData | Export-Csv -Path $csvFileName -NoTypeInformation
# Convert CSV to Excel
$csvData = Import-Csv -Path $csvFileName
$csvData | Export-Excel -Path "exported_emails.xlsx"
# Clean up
Remove-Item $csvFileName
This script will export the sender, subject, and sent date-time information from the specified folder and save it to an Excel file. You can then use the data in Excel to create graphs based on the date and time stamps.
posted by rozcakj at 12:13 PM on April 26, 2023 [1 favorite]
Response by poster: This feedback is brilliant. WIll check and see if works! Many many thanks.
posted by Funmonkey1 at 1:06 PM on April 26, 2023
posted by Funmonkey1 at 1:06 PM on April 26, 2023
Mod note: One deleted, let's avoid copy/pasting ChatGPT answers.
posted by loup (staff) at 1:16 PM on April 26, 2023 [1 favorite]
posted by loup (staff) at 1:16 PM on April 26, 2023 [1 favorite]
Excel can connect to MS Exchange Online and pull data out of it through PowerQuery which can perform useful transformations along the way.
Data tab -> Get Data -> From Online Services -> From Microsoft Exchange Online
Sign in the account
Choose Mail
Choose Transform Data
In PowerQuery, you can filter for the specific folder and perform other transformations (omitting fields, calculating, heck you can cross-reference against other data sources)
Leila Gharani is good for tutorials on this kind of thing.
posted by idb at 5:38 PM on April 26, 2023 [1 favorite]
Data tab -> Get Data -> From Online Services -> From Microsoft Exchange Online
Sign in the account
Choose Mail
Choose Transform Data
In PowerQuery, you can filter for the specific folder and perform other transformations (omitting fields, calculating, heck you can cross-reference against other data sources)
Leila Gharani is good for tutorials on this kind of thing.
posted by idb at 5:38 PM on April 26, 2023 [1 favorite]
This thread is closed to new comments.
posted by rozcakj at 12:06 PM on April 26, 2023 [1 favorite]