Automate Email Integration Tasks from PowerShell

The CData Cmdlets for Email are standard PowerShell cmdlets that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time and bidirectional access to Email.

PowerShell Cmdlets or ADO.NET Provider?

The Cmdlets are not only a PowerShell interface to Email, but also an SQL interface; this tutorial shows how to use both to create, retrieve, update, and delete Email data. We also show examples of the ADO.NET equivalent, which is possible with the CData ADO.NET Provider for Email. To access Email data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for Email.

Once you have acquired the necessary connection properties, accessing Email data in PowerShell can be enabled in three steps.

The User and Password properties, under the Authentication section, must be set to valid credentials. The Server must be specified to retrieve emails and the SMTPServer must be specified to send emails.

PowerShell

1. Install the module:

   Install-Module EmailCmdlets

2. Connect:

   $email = Connect-Email -User "$User" -Password "$Password" -Server "$Server" -Port "$Port" -SMTP Server "$SMTP Server" -SMTP Port "$SMTP Port" -SSL Mode "$SSL Mode" -Protocol "$Protocol" -Mailbox "$Mailbox"
   

3. Search for and retrieve data:

   $mailbox = "Spam"
   $mailboxes = Select-Email -Connection $email -Table "Mailboxes" -Where "Mailbox = `'$Mailbox`'"
   $mailboxes
   

   You can also use the Invoke-Email cmdlet to execute SQL commands:

   $mailboxes = Invoke-Email -Connection $email -Query 'SELECT * FROM Mailboxes WHERE Mailbox = @Mailbox' -Params @{'@Mailbox'='Spam'}
   

ADO.NET

1. Load the provider's assembly:

   [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for Email\lib\System.Data.CData.Email.dll")
    

2. Connect to Email:

    
   $conn= New-Object System.Data.CData.Email.EmailConnection("[email protected];Password=password;Server=imap.gmail.com;Port=993;SMTP Server=smtp.gmail.com;SMTP Port=465;SSL Mode=EXPLICIT;Protocol=IMAP;Mailbox=Inbox;")
   $conn.Open()
   

3. Instantiate the EmailDataAdapter, execute an SQL query, and output the results:

   $sql="SELECT Mailbox, RecentMessagesCount from Mailboxes"
   
   $da= New-Object System.Data.CData.Email.EmailDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.mailbox $_.recentmessagescount
   }
    

Update Email Data

PowerShell

Update-Email -Connection $Email -Columns @('Mailbox','RecentMessagesCount') -Values @('MyMailbox', 'MyRecentMessagesCount') -Table Mailboxes -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.Email.EmailCommand("UPDATE Mailboxes SET Mailbox='Spam' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Email.EmailParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert Email Data

PowerShell

Add-Email -Connection $Email -Table Mailboxes -Columns @("Mailbox", "RecentMessagesCount") -Values @("MyMailbox", "MyRecentMessagesCount") 

ADO.NET

$cmd = New-Object System.Data.CData.Email.EmailCommand("INSERT INTO Mailboxes (Mailbox) VALUES (@myMailbox)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Email.EmailParameter("@myMailbox","Spam")))
$cmd.ExecuteNonQuery()

Delete Email Data

PowerShell

Remove-Email -Connection $Email -Table "Mailboxes" -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.Email.EmailCommand("DELETE FROM Mailboxes WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Email.EmailParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()