Learn more
 

Ready to get started?

Download a free trial of the Email Data Provider to get started:

 Download Now

Learn more:

Email Icon Email ADO.NET Provider

The easiest way to integrate powerful Email send and receive capabilities with .NET applications. Send & Receive Email through POP3, IMAP, and SMTP, Verify Addresses, and more!

In this article

Related articles

Automate Email Integration Tasks from PowerShell


Are you in search of a quick and easy way to access Email data from PowerShell? This article demonstrates how to utilize the Email Cmdlets for tasks like connecting to Email data, automating operations, downloading data, 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.

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("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-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()