Copy Outlook data to SharePoint online List using Powershell

I want to copy an outlook email data into a SharePoint online list. To achieve this, we can use CSOM /Power shell.

Here I am showing to copy outlook data to SharePoint List using Power shell.

Step 1 : Create a SharePoint List and columns(Question-Multi line text , CretaedBy - People Picker).

Step 2 : Download and install the below tools.
1.SharePoint Online Management shell - https://www.microsoft.com/en-in/download/details.aspx?id=42038
2. Exchange Web service- EWS API is found at: http://www.microsoft.com/en-us/download/details.aspx?id=28952

Step 3 : Open your Windows Powershell ISE and run the follwing script.

#---Outlook mail box name, Username, password and domain ---#

$MailboxName = "AllHandsQuestions@ftech.com"
$LoginId="venkat.m";
$Password='P@ssw0rd';
$Domain='FTech';

#---Log file location ---#

$downloadDirectory = "C:\OutlookLogs_Test\"
$currentDate = Get-Date -UFormat "%Y%m%d"
$Logfile = "Archiving_"+$currentDate+".txt"
$LogFullPath = "C:\OutlookLogs_Test\"
$LogFilePath = $LogFullPath+ $Logfile
$emailFrom = "AllHandsQuestions@ftech.com"
$emailTo = "AllHandsQuestions@ftech.com"
$UserName="venkat.m@ftech.com"
$SecurePassword="P@ssw0rd"

#--SharePoint Site Collection URL ---#

$SPOSiteUrl ="https://ftech.sharepoint.com/sites/Test"

#--SharePoint List Name --#

$ListTitle= "All Hands Questions"


#--Loading SharePoint APIS
#Import-Module "C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.Online.SharePoint.PowerShell.dll"
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"
$SecurePassword = $Password | ConvertTo-SecureString -AsPlainText -Force
$Credentials = New-Object -TypeName System.Management.Automation.PSCredential -argumentlist $UserName, $SecurePassword

   if(![System.IO.File]::Exists($LogFilePath))
   {
      New-Item  -Path $LogFullPath  -Name $Logfile -ItemType File
    }
 
function Write-Log {
     [CmdletBinding()]
     param(
         [Parameter()]
         [ValidateNotNullOrEmpty()]
         [string]$Message
       
     )
     [string]$logMessage = $(Get-Date -Format "d") + " " + $(Get-Date -Format "T") +"       "+  $Message
     Add-Content -Path $LogFilePath -Value $logMessage 
 } 

 Write-Log -Message "***** ARCHIVING POWERSHELL SCRIPT STARTED *****"

#################PRE REQUISISTE to install ###############################
#
#Load the EWS API and connect to the CAS/EWS
# EWS API is found at: http://www.microsoft.com/en-us/download/details.aspx?id=28952
#
################################################

################################################
#
#Accept any certificates presented by the CAS
#
################################################

## Create a compilation environment
$Provider=New-Object Microsoft.CSharp.CSharpCodeProvider
$Compiler=$Provider.CreateCompiler()
$Params=New-Object System.CodeDom.Compiler.CompilerParameters
$Params.GenerateExecutable=$False
$Params.GenerateInMemory=$True
$Params.IncludeDebugInformation=$False
$Params.ReferencedAssemblies.Add("System.DLL") | Out-Null

$TASource=@'
  namespace Local.ToolkitExtensions.Net.CertificatePolicy{
    public class TrustAll : System.Net.ICertificatePolicy {
      public TrustAll() {
      }
      public bool CheckValidationResult(System.Net.ServicePoint sp,
        System.Security.Cryptography.X509Certificates.X509Certificate cert,
        System.Net.WebRequest req, int problem) {
        return true;
      }
    }
  }
'@
$TAResults=$Provider.CompileAssemblyFromSource($Params,$TASource)
$TAAssembly=$TAResults.CompiledAssembly

## We now create an instance of the TrustAll and attach it to the ServicePointManager
$TrustAll=$TAAssembly.CreateInstance("Local.ToolkitExtensions.Net.CertificatePolicy.TrustAll")
[System.Net.ServicePointManager]::CertificatePolicy=$TrustAll

################################################
#
#Load the EWS API and connect to the CAS/EWS
# EWS API is found at: http://www.microsoft.com/en-us/download/details.aspx?id=28952
#
################################################

## Load Managed API dll
Add-Type -Path "C:\Program Files\Microsoft\Exchange\Web Services\1.2\Microsoft.Exchange.WebServices.dll"
$ModuleDllPath="C:\Program Files\Microsoft\Exchange\Web Services\1.2\Microsoft.Exchange.WebServices.dll"
 Import-Module -Name:$ModuleDllPath -ErrorAction:Stop

## Set Exchange Version (Exchange2010, Exchange2010_SP1 or Exchange2010_SP2)

$ExchangeVersion = [Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2010_SP2

## Create Exchange Service Object
$service = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService($ExchangeVersion)

## Set Credentials to use two options are availible Option1 to use explict credentials or Option 2 use the Default (logged On) credentials
#Credentials Option 1 using UPN for the windows Account
$creds = New-Object System.Net.NetworkCredential($LoginId,$Password,$Domain)
$service.Credentials = $creds 

#Credentials Option 2
$service.UseDefaultCredentials = $true

## Set the URL of the CAS (Client Access Server) to use two options are availbe to use Autodiscover to find the CAS URL or Hardcode the CAS to use

#CAS URL Option 1 Autodiscover
$service.AutodiscoverUrl($MailboxName,{$true})
"Using CAS Server : " + $Service.url

#CAS URL Option 2 Hardcoded  
$uri=[system.URI] $Service.url
$service.Url = $uri 
Write-Log -Message ("GET Exchange Web service URL "+  $service.Url)


#Bind to the Inbox folder
$Sfha = new-object Microsoft.Exchange.WebServices.Data.SearchFilter+IsEqualTo([Microsoft.Exchange.WebServices.Data.EmailMessageSchema]::HasAttachments, $false)
$folderid= new-object Microsoft.Exchange.WebServices.Data.FolderId([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Inbox,$MailboxName) 
$Inbox = [Microsoft.Exchange.WebServices.Data.Folder]::Bind($service,$folderid) 

#Get the ID of the folder to move to 
$fvFolderView =  New-Object Microsoft.Exchange.WebServices.Data.FolderView(100) 
$fvFolderView.Traversal = [Microsoft.Exchange.WebServices.Data.FolderTraversal]::Shallow;



  Write-Log -Message "GET PROCESSED FOLDER"
 
####################################################################################################
#
#This section finds attachments and copies the attachment to the download directory
#
####################################################################################################


$ivItemView = New-Object Microsoft.Exchange.WebServices.Data.ItemView(10)

$psPropset = new-object Microsoft.Exchange.WebServices.Data.PropertySet([Microsoft.Exchange.WebServices.Data.ItemSchema]::MimeContent)
$psPropsetSubject = new-object Microsoft.Exchange.WebServices.Data.PropertySet([Microsoft.Exchange.WebServices.Data.ItemSchema]::Subject)
$psPropertySet = new-object Microsoft.Exchange.WebServices.Data.PropertySet([Microsoft.Exchange.WebServices.Data.BasePropertySet]::FirstClassProperties)
$psPropertySet = new-object Microsoft.Exchange.WebServices.Data.PropertySet([Microsoft.Exchange.WebServices.Data.BasePropertySet]::FirstClassProperties)
$psPropertySet.RequestedBodyType = [Microsoft.Exchange.WebServices.Data.BodyType]::Text;


$errorMessage =''
$attachmentCount = 0;
$findItemsResults = $Inbox.FindItems($ivItemView)
$service.LoadPropertiesForItems($findItemsResults,$psPropertySet)

#Connection Sharepoint online
$creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,$SecurePassword);
$Context= New-Object Microsoft.SharePoint.Client.ClientContext($SPOSiteUrl);         
$Context.Credentials=$creds;
$testWeb = $Context.Web;
$Context.Load($testWeb);
$Context.Load($testWeb.Lists);
$Context.ExecuteQuery();
$tuitionFeed=$testWeb.Lists.GetByTitle($ListTitle)
$Context.Load($tuitionFeed)
$Context.ExecuteQuery()
Write-Host "Credentials verified successfully!!"

foreach($miMailItems in $findItemsResults.Items){
try{
       
       
            $subject = $miMailItems.Subject.ToString()
            $body = $miMailItems.Body.Text
            $sender = $miMailItems.Sender.Address
            $from = $miMailItems.From.Name
           
            Write-Log -Message ("Downloading E-Mail: " + $subject)           
            $lici =New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
            $listItem = $tuitionFeed.AddItem($lici)
            $listItem["Question"]=$body  

            $listItem["CreatedBy"] = $testWeb.EnsureUser($from)
                    
            $listItem.Update()
          

           $Context.ExecuteQuery()
        #   $miMailItems.IsRead=$true
        #   $miMailItems.Update(Microsoft.Exchange.WebServices.Data.
       
    }
     catch [System.Net.WebException],[System.Exception]
     {
        $ErrorMessage = $_.Exception.Message
        Write-Log -Message $_.Exception.Message

Write-Log -Message "***** ERROR OCCURED *****"
        $failedCount++;
        $ErrorMessage = $_.Exception.Message
        Write-Log -Message $_.Exception.Message       
        Write-Log -Message "***** ERROR CLOSED *****"
        $errorMessage +=$_.Exception.Message
     }
   
   
}
$comments=''
try
{   
$message ='';
   if($errorMessage -eq '')
{
$message="Job result: Succeeded"
}
else
{
$message="Job result: Failed"
$comments = "`nError Message: " +$errorMessage

}

     
     
        Write-Log -Message "***** SENDING CONFIRMATION EMAIL *****"
       
       # $smtpServer = "mail.ftech.com "
       # $subject = "EFT Invoice mailbox archive job report"
       # $body = $message +"`nComments: "+$comments
       # Send-MailMessage -To $emailTo -From $emailFrom -Subject $subject -Body $body -SmtpServer $smtpServer
        Write-Log -Message "***** SENT CONFIRMATION EMAIL *****"

}
     catch [System.Net.WebException],[System.Exception]
     {
        Write-Log -Message "***** ERROR OCCURED *****"
         
        $ErrorMessage = $_.Exception.Message
        Write-Log -Message $_.Exception.Message       
        Write-Log -Message "***** ERROR CLOSED *****"
     }

Write-Log -Message "***** ARCHIVING POWERSHELL  SCRIPT END *****"


Step 4: Run the above script and followed by output.

Step 5 : Check SharePoint List now.

Happy Coding..!