Introduction
Hi guys, sometimes we may come across the scenario where we want to copy email (.eml file) from Office o365 to Sharepoint library. This article will help you to solve this scenario.
We have to follow these steps to achieve the above requirement:
- Read emails from the Office O365 account.
- Extract the emails to the local directory in .eml file format.
- Upload the extracted .eml file to SharePoint library files.
- Moved the read emails to another folder in the o365 account from the inbox.
- Finally, delete the extracted files from local directory
We can execute the above steps with the help of:
- Using Powershell Script
- Using MS Flow
Before starting with any methods, create one folder inside inbox in office o365 account and one library(Test) in SharePoint online to upload the .eml file.
Using PowerShell Script
The PowerShell script requires the following library files.
It requires EWS Managed API 2.2, which can be download and install from
here
It requires a SharePoint Online SDK, which can be downloaded and install from here.
Store the credentials of office 365 account and SharePoint online account in the files and read the credentials in the script from the same files. To create and use the credentials in the script in a file and another way, refer my article
Passing Credentials in SharePoint Online Using PowerShell
Firstly, import and refer the downloaded library files in your script like this.
- #Add dll files
- Import-Module -Name "C:\Program Files\Microsoft\Exchange\Web Services\2.2\Microsoft.Exchange.WebServices.dll"
-
- #Load SharePoint CSOM Assemblies
- Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
- Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
Read the emails from office o365 account:
- <#
- This function is used to read emails from particular emails
- #>
- Function ReadEmails() {
- param(
- [Parameter(Mandatory = $true)] $SiteURL,
- [Parameter(Mandatory = $true)] $USER_DEFINED_FOLDER_IN_MAILBOX
- )
- If(!(test-path $exportPath))
- {
- Write-Host "Path doesn't exist $($exportPath), Hence creating the path." -f Red
- New-Item -ItemType Directory -Force -Path $exportPath
- }
- $service = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService
- $service.Credentials = New-Object System.Net.NetworkCredential -ArgumentList $fileCred.UserName, $fileCred.Password
- $service.URL = New-Object Uri("https://outlook.office365.com/EWS/Exchange.asmx")
- # create Property Set to include body and header of email
- $PropertySet = New-Object Microsoft.Exchange.WebServices.Data.PropertySet([Microsoft.Exchange.WebServices.Data.BasePropertySet]::FirstClassProperties)
-
- # set email body to text
- $PropertySet.RequestedBodyType = [Microsoft.Exchange.WebServices.Data.BodyType]::Text;
-
- # Set how many emails we want to read at a time
- $numOfEmailsToRead = 100
-
- # Index to keep track of where we are up to. Set to 0 initially.
- $index = 0
- # Do/while loop for paging through the folder
- do {
- # Set what we want to retrieve from the folder. This will grab the first $pagesize emails
- $view = New-Object Microsoft.Exchange.WebServices.Data.ItemView($numOfEmailsToRead, $index)
- # Retrieve the data from the folder
- $findResults = $service.FindItems([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Inbox, $view)
- foreach ($item in $findResults.Items) {
- # load the additional properties for the item
- $item.Load($propertySet)
-
- # Output the results
- $msgProperty = New-Object Microsoft.Exchange.WebServices.Data.PropertySet([Microsoft.Exchange.WebServices.Data.EmailMessageSchema]::MimeContent)
- $email = [Microsoft.Exchange.WebServices.Data.EmailMessage]::Bind($service, $item.Id, $msgProperty)
- $fileName = "$($item.Subject)_$($item.DateTimeReceived)"
- $subject = Remove-InvalidFileNameChars($fileName)
- $filePath = "$($exportPath)$($subject).eml";
- Write-Host "File Name:"$filePath
-
- # Export the file into .eml format
- Export-EMLFile $filePath $email
- $fileSize = 0;
- $currentFile = New-Object System.IO.FileInfo($filePath);
- #while ($fileSize -ilt $currentFile.Length)#check size is stable or increased
- #{
- #$fileSize = $currentFile.Length; #get current size
- #Start-Sleep -s 60
- #$currentFile.Refresh(); #refresh length value
- #}
-
- # moved the file to destination folder in office 365 email
- Move-Email $service $item $USER_DEFINED_FOLDER_IN_MAILBOX $fileName
- }
- # Increment $index to next block of emails
- $index += $numOfEmailsToRead
- } while ($findResults.MoreAvailable) # Do/While there are more emails to retrieve
- #Upload the file to library after exporting it
- }
We will check and removed the invalid character in email subject with underscore (_).
- #Removes invalid Characters for file names from a string input and outputs the clean string
- #Similar to VBA CleanString() Method
- #Currently set to replace all illegal characters with a hyphen (_)
- Function Remove-InvalidFileNameChars {
- param(
- [Parameter(Mandatory = $true, Position = 0)]
- [String]$Name
- )
- return [RegEx]::Replace($Name, "[{0}]" -f ([RegEx]::Escape([String][System.IO.Path]::GetInvalidFileNameChars())), '_')
- }
Export all the emails from inbox to local directory:
- <#
- This function is used to export the email to .eml files
- #>
- Function Export-EMLFile() {
- param(
- [Parameter(Mandatory = $true, Position = 0)]$filePath,
- [Parameter(Mandatory = $true, Position = 1)]$email
- )
- try {
- Write-Host "Extracting Email file"
-
- $fs = New-Object System.IO.FileStream($filePath, [System.IO.FileMode]::Create, [System.IO.FileAccess]::Write)
- $fs.Write($email.MimeContent.Content, 0, $email.MimeContent.Content.Length)
- }
- catch {
- Write-host -Message $_.Exception.Message
- }
- }
Upload the extracted .eml files to SharePoint library with this code.
- <#
- This function is used to upload the files into sharepoint library
- #>
- Function Upload-MultipleFile-To-Library() {
-
- param(
- [Parameter(Mandatory = $true)][String]$siteUrl,
- [Parameter(Mandatory = $true)][String]$libraryName,
- [Parameter(Mandatory = $true)]$folder
- )
-
- $Cred = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($spfileCred.UserName, $spfileCred.Password)
- #Set up the context
- $Context = New-Object Microsoft.SharePoint.Client.ClientContext($siteUrl)
- $Context.Credentials = $Cred
-
- #Retrieve list
- $List = $Context.Web.Lists.GetByTitle($libraryName)
- $Context.Load($List)
- $Context.ExecuteQuery()
- Write-Host "Folder" $folder
- # Upload file
- Foreach ($File in (Get-ChildItem $folder)) {
- Write-Host "Uploading File......$($File.FullName)"
- $FileStream = New-Object IO.FileStream($File.FullName, [System.IO.FileMode]::Open)
- $FileCreationInfo = New-Object Microsoft.SharePoint.Client.FileCreationInformation
- $FileCreationInfo.Overwrite = $true
- $FileCreationInfo.ContentStream = $FileStream
- $FileCreationInfo.URL = $File
- $Upload = $List.RootFolder.Files.Add($FileCreationInfo)
- $Context.Load($Upload)
- $Context.ExecuteQuery()
- $File.Delete()
-
- }
- }
Move the read emails from inbox to another folder (i.e Moved folder)
- <#
- This function is used to moved the email from inbox to another folder in office 365
- #>
- Function Move-Email() {
- param
- (
- [Parameter(Mandatory = $true)]$service,
- [Parameter(Mandatory = $true)]$Item,
- [Parameter(Mandatory = $true)]$USER_DEFINED_FOLDER_IN_MAILBOX,
- [Parameter(Mandatory = $true)]$fileName
- )
- $FolderId = @();
- $FolderView = new-object -TypeName Microsoft.Exchange.WebServices.Data.FolderView -ArgumentList (100)
- $FolderView.Traversal = [Microsoft.Exchange.Webservices.Data.FolderTraversal]::Deep
- $SearchFilter = new-object -TypeName Microsoft.Exchange.WebServices.Data.SearchFilter+IsEqualTo -ArgumentList ([Microsoft.Exchange.WebServices.Data.FolderSchema]::DisplayName,$USER_DEFINED_FOLDER_IN_MAILBOX)
- $FindFolderResults = $service.FindFolders([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::MsgFolderRoot,$SearchFilter,$FolderView)
- if($FindFolderResults.Id) {
- Write-host "Moving File $($fileName) from Inbox to $($USER_DEFINED_FOLDER_IN_MAILBOX)" -ForegroundColor Yellow
- $FolderId += $FindFolderResults.Id
- }
- $Message = [Microsoft.Exchange.WebServices.Data.EmailMessage]::Bind($service,$Item.Id)
- $Message.Move($FolderId[0])
- }
Delete all the .eml files from directory with this line.
- Foreach ($File in (Get-ChildItem $folder)) {
- Write-Host "Deleting File......$($File.FullName)"
- $File.Delete()
- }
The complete powershell script will look like this:
- <#
- This script will perform the following fuction,
-
- -> It will export the email from o365 mail to .eml file on local computer directory.
- -> Once file is available in local directory it will upload the .eml file into SharePoint Library.
- -> It will delete the files from local directory.
- -> Finally, It will moved the files from 'Inbox' to another folder in o365 mail box.
-
- ***************************************************************************************************
- Prerequisites
- ***************************************************************************************************
-
- 1 - The script requires EWS Managed API 2.2, which can be downloaded here:
- https:
-
- 2 - The script requires SharePoint Online SDK, Which can be downloaded here:
- https:
-
- 3 - TargetFolder has to be created previous to run the Script
-
- 4 - Source and Target folders have to be unique names. No repeated folders in subfolders.
-
- ***************************************************************************************************
- Required Parameters
- ***************************************************************************************************
-
- 1. $credPath
- 2. $fileCred
- 3. $spCredPath
- 4. $spfileCred
- 5. $SiteURL
- 6. $libraryName
- 7. $exportPath
- 8. $USER_DEFINED_FOLDER_IN_MAILBOX
-
-
- ***************************************************************************************************
- Created by : Arvind Kushwaha
- E-mail : arvind.kushwaha
- Created Date : 22-05-2020
- version : 1.0
- ***************************************************************************************************
- #>
-
- #Add dll files
- Import-Module -Name "C:\Program Files\Microsoft\Exchange\Web Services\2.2\Microsoft.Exchange.WebServices.dll"
-
- #Load SharePoint CSOM Assemblies
- Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
- Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
-
- #Pass Credentials
- $credPath = "D:\Arvind\safe\secret.txt"
- $fileCred = Import-Clixml -path $credpath
-
- $spCredPath = 'Path to SharePoint Online Account Credentials Files'
- $spfileCred = Import-Clixml -path $spCredPath
-
- $SiteURL = "Your Site Url"
- $libraryName = "Your library Name"
- $exportPath = "D:\Arvind\logs\qc\"
-
- $USER_DEFINED_FOLDER_IN_MAILBOX = "Moved"
- $folder = $exportPath
- <#
- call the function
- #>
- #ReadEmails $SiteURL $USER_DEFINED_FOLDER_IN_MAILBOX
- Upload-MultipleFile-To-Library $SiteURL $libraryName $folder
- <#
- This function is used to read emails from particular emails
- #>
- Function ReadEmails() {
- param(
- [Parameter(Mandatory = $true)] $SiteURL,
- [Parameter(Mandatory = $true)] $USER_DEFINED_FOLDER_IN_MAILBOX
- )
- If(!(test-path $exportPath))
- {
- Write-Host "Path doesn't exist $($exportPath), Hence creating the path." -f Red
- New-Item -ItemType Directory -Force -Path $exportPath
- }
- $service = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService
- $service.Credentials = New-Object System.Net.NetworkCredential -ArgumentList $fileCred.UserName, $fileCred.Password
- $service.URL = New-Object Uri("https://outlook.office365.com/EWS/Exchange.asmx")
- # create Property Set to include body and header of email
- $PropertySet = New-Object Microsoft.Exchange.WebServices.Data.PropertySet([Microsoft.Exchange.WebServices.Data.BasePropertySet]::FirstClassProperties)
-
- # set email body to text
- $PropertySet.RequestedBodyType = [Microsoft.Exchange.WebServices.Data.BodyType]::Text;
-
- # Set how many emails we want to read at a time
- $numOfEmailsToRead = 100
-
- # Index to keep track of where we are up to. Set to 0 initially.
- $index = 0
- # Do/while loop for paging through the folder
- do {
- # Set what we want to retrieve from the folder. This will grab the first $pagesize emails
- $view = New-Object Microsoft.Exchange.WebServices.Data.ItemView($numOfEmailsToRead, $index)
- # Retrieve the data from the folder
- $findResults = $service.FindItems([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Inbox, $view)
- foreach ($item in $findResults.Items) {
- # load the additional properties for the item
- $item.Load($propertySet)
-
- # Output the results
- $msgProperty = New-Object Microsoft.Exchange.WebServices.Data.PropertySet([Microsoft.Exchange.WebServices.Data.EmailMessageSchema]::MimeContent)
- $email = [Microsoft.Exchange.WebServices.Data.EmailMessage]::Bind($service, $item.Id, $msgProperty)
- $fileName = "$($item.Subject)_$($item.DateTimeReceived)"
- $subject = Remove-InvalidFileNameChars($fileName)
- $filePath = "$($exportPath)$($subject).eml";
- Write-Host "File Name:"$filePath
-
- # Export the file into .eml format
- Export-EMLFile $filePath $email
- $fileSize = 0;
- $currentFile = New-Object System.IO.FileInfo($filePath);
- #while ($fileSize -ilt $currentFile.Length)#check size is stable or increased
- #{
- #$fileSize = $currentFile.Length; #get current size
- #Start-Sleep -s 60
- #$currentFile.Refresh(); #refresh length value
- #}
-
- # moved the file to destination folder in office 365 email
- Move-Email $service $item $USER_DEFINED_FOLDER_IN_MAILBOX $fileName
- }
- # Increment $index to next block of emails
- $index += $numOfEmailsToRead
- } while ($findResults.MoreAvailable) # Do/While there are more emails to retrieve
- #Upload the file to library after exporting it
- }
- #Removes invalid Characters for file names from a string input and outputs the clean string
- #Similar to VBA CleanString() Method
- #Currently set to replace all illegal characters with a hyphen (_)
- Function Remove-InvalidFileNameChars {
- param(
- [Parameter(Mandatory = $true, Position = 0)]
- [String]$Name
- )
- return [RegEx]::Replace($Name, "[{0}]" -f ([RegEx]::Escape([String][System.IO.Path]::GetInvalidFileNameChars())), '_')
- }
- #checked file is locked or not
- Function IsFileLocked() {
- param
- (
- [Parameter(Mandatory = $true, Position = 0)][System.IO.FileStream]$fileInfo
- )
- [System.IO.FileStream]$stream = $null;
-
- try {
- $stream = $fileInfo.Open([System.IO.FileMode]::Open, [System.IO.FileAccess]::Write, [System.IO.FileShare]::None);
- }
- catch {
- return $true;
- }
-
- #file is not locked
- return $false;
- }
- <#
- This function is used to export the email to .eml files
- #>
- Function Export-EMLFile() {
- param(
- [Parameter(Mandatory = $true, Position = 0)]$filePath,
- [Parameter(Mandatory = $true, Position = 1)]$email
- )
- try {
- Write-Host "Extracting Email file"
-
- $fs = New-Object System.IO.FileStream($filePath, [System.IO.FileMode]::Create, [System.IO.FileAccess]::Write)
- $fs.Write($email.MimeContent.Content, 0, $email.MimeContent.Content.Length)
- }
- catch {
- Write-host -Message $_.Exception.Message
- }
- }
- <#
- This function is used to upload the files into sharepoint library
- #>
- Function Upload-MultipleFile-To-Library() {
-
- param(
- [Parameter(Mandatory = $true)][String]$siteUrl,
- [Parameter(Mandatory = $true)][String]$libraryName,
- [Parameter(Mandatory = $true)]$folder
- )
-
- $Cred = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($spfileCred.UserName, $spfileCred.Password)
- #Set up the context
- $Context = New-Object Microsoft.SharePoint.Client.ClientContext($siteUrl)
- $Context.Credentials = $Cred
-
- #Retrieve list
- $List = $Context.Web.Lists.GetByTitle($libraryName)
- $Context.Load($List)
- $Context.ExecuteQuery()
- Write-Host "Folder" $folder
- # Upload file
- Foreach ($File in (Get-ChildItem $folder)) {
- Write-Host "Uploading File......$($File.FullName)"
- $FileStream = New-Object IO.FileStream($File.FullName, [System.IO.FileMode]::Open)
- $FileCreationInfo = New-Object Microsoft.SharePoint.Client.FileCreationInformation
- $FileCreationInfo.Overwrite = $true
- $FileCreationInfo.ContentStream = $FileStream
- $FileCreationInfo.URL = $File
- $Upload = $List.RootFolder.Files.Add($FileCreationInfo)
- $Context.Load($Upload)
- $Context.ExecuteQuery()
- $File.Delete()
-
- }
- }
- <#
- This function is used to moved the email from inbox to another folder in office 365
- #>
- Function Move-Email() {
- param
- (
- [Parameter(Mandatory = $true)]$service,
- [Parameter(Mandatory = $true)]$Item,
- [Parameter(Mandatory = $true)]$USER_DEFINED_FOLDER_IN_MAILBOX,
- [Parameter(Mandatory = $true)]$fileName
- )
- $FolderId = @();
- $FolderView = new-object -TypeName Microsoft.Exchange.WebServices.Data.FolderView -ArgumentList (100)
- $FolderView.Traversal = [Microsoft.Exchange.Webservices.Data.FolderTraversal]::Deep
- $SearchFilter = new-object -TypeName Microsoft.Exchange.WebServices.Data.SearchFilter+IsEqualTo -ArgumentList ([Microsoft.Exchange.WebServices.Data.FolderSchema]::DisplayName,$USER_DEFINED_FOLDER_IN_MAILBOX)
- $FindFolderResults = $service.FindFolders([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::MsgFolderRoot,$SearchFilter,$FolderView)
- if($FindFolderResults.Id) {
- Write-host "Moving File $($fileName) from Inbox to $($USER_DEFINED_FOLDER_IN_MAILBOX)" -ForegroundColor Yellow
- $FolderId += $FindFolderResults.Id
- }
- $Message = [Microsoft.Exchange.WebServices.Data.EmailMessage]::Bind($service,$Item.Id)
- $Message.Move($FolderId[0])
- }
Using MS Flow
Open SharePoint online account and navigate to power automate to create the flow.
Now click on Create and select Automated flow.
Write the name of flow and choose the flow trigger action i.e When a new email arrives(V3)
Select the appropriate folder from where the email will be pulled out. (i.e Inbox)
Choose another action by clicking on the plus sign (+) or click on New Step, and select Export email(V2).
Choose another action i.e Create file and configure the action in this way.
*Site Address : The Url of the SharePoint site
*Folder Path: /Document Library Name
*File Name: Write this expression in this field concat(triggerBody()?['Subject'],triggerBody()?['DateTimeReceived'])
*File Content: Body of Email.
Finally, move the email from the inbox to another folder (Moved) with Move email(v2) action.
Test the flow
Send an email to office 365 account for which flow is configured. (i.e I had configured for my account.)
The flow will trigger as soon as you send an email to our configured account and will look like this
Check the moved folder which contains the test email.
Now, check the SharePoint library which contains the .eml files.
Conclusion
In these articles, we have seen how to copy the email from an Office O365 account and upload it into the SharePoint library with PowerShell script and MS flow.