Ready to get started?

Learn more about the CData ADO.NET Provider for Email or download a free trial:

Download Now

Automate Email Integration Tasks from PowerShell

Are you looking for a quick and easy way to access Email data from PowerShell? We show how to use the Cmdlets for Email and the CData ADO.NET Provider for Email to connect to Email data and synchronize, automate, download, and more.

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.

Cmdlets or ADO.NET?

The cmdlets are not only a PowerShell interface to the Email API, 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.

After obtaining the needed connection properties, accessing Email data in PowerShell consists of three basic 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("User=username@gmail.com;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 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 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 System.Data.CData.Email.EmailParameter("@myId","001d000000YBRseAAH")) $cmd.ExecuteNonQuery()