Powershell and EWS - how to update Contacts

"OK, so what we want is a special group of people to get a list of corporate contacts from SQL and push that into their Exchange Contacts every month". Seems a reasonable ask. A good chance to explore some new stuff.

This little exercise had me going in circles, but I was eventually able to lookup the users in AD, then  do some SQL and then punch it into Exchange via EWS.  Powershell and EWS is excellent. I had lots of fun learning Powershell and EWS.

# Import-SQL-Contacts.PS1

# The user running this is assumed to be ADMINISTRATOR and have been granted IMPERSONATE rights.

# see http://msdn.microsoft.com/en-us/library/bb204095.aspx

#

# Get-ExchangeServer

#  | where {$_.IsClientAccessServer -eq $TRUE}

#  | ForEach-Object {Add-ADPermission -Identity $_.distinguishedname -User (Get-User -Identity ADMINISTRATOR

#  | Select-object).identity -extendedRight ms-Exch-EPI-Impersonation}

#

#########################################################################

# for a given OU, retrieve the users in it and return their MAIL attrib.

# usage: Get-Mail-Users "/OU=Key People"  returns email@address.com

#########################################################################

#

function Get-Mail-Users {

  Param([string]$targetOU)

  Process{

#get a GC in this forest

    $for = [System.DirectoryServices.ActiveDirectory.Forest]::GetCurrentForest()

    $gc = $for.FindGlobalCatalog()

    $server = $gc.name

    $forDN = "DC="+ $for.Name.Replace(".",",DC=")

#which is the same as

#   $gcDN = $gc.Partitions[0]

    $ncRoot = "GC://$server$targetOU,$forDN"

    $root = New-Object System.DirectoryServices.DirectoryEntry $ncRoot

# create the query object and execute against GC

    $query = New-Object System.DirectoryServices.DirectorySearcher

    $query.searchroot = $root

# only users who have a mailbox, please

    $query.filter = "(&(objectCategory=person)(msExchHomeServerName=*)(homeMDB=*))"

    $resultset = $query.Findall()

    $resultset | foreach-object {write-output $_.properties.mail}

  }

}

#

#########################################################################

# for a given email address, connect to Exchange and bind to CONTACTS.

# usage: Bind-To-Contacts "username@company.com"  Returns obj$Binding

#########################################################################

#

function Bind-To-ContactsFolder {

   Param ([string]$mailAddress)

 

# you will need to download the API for EWS.  Search on "Exchange Web Services Managed API"

# version used here is 14.0.650.7  dated 1/11/2009

 

# first, tell it where the new EWS stuff lives

#

   $dllpath = "C:\Program Files\Microsoft\Exchange\Web Services\1.0\Microsoft.Exchange.WebServices.dll"

   [void][Reflection.Assembly]::LoadFile($dllpath)

# now create the EWS service we will be working with

#

   $SCRIPT:objService = new-object Microsoft.Exchange.WebServices.Data.ExchangeService([Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2007_SP1)

#  $objService.TraceEnabled = $true

#  get THIS USERs info from AD so that it can connect.(rather than hard-code the email address)

#

   $windowsIdentity = [System.Security.Principal.WindowsIdentity]::GetCurrent()

   $sidbind = "LDAP://<SID=" + $windowsIdentity.user.Value.ToString() + ">"

   $aceuser = [ADSI]$sidbind

 

#################################################################################################

# NOTE: The Autodiscover will bomb if CERTS are not sorted out.

#       You should register a callback, etc. Don't bother testing the cert, just say "OK!" :-)

#       I copied the Exchange server Personal cert into Trusted Certs on this machine

#       to get this working initially. i.e. if pre-2.0 RTM Powershell then copy the cert...

#

# see:

#   http://msdn.microsoft.com/en-us/library/dd633677.aspx

#   http://msdn.microsoft.com/en-us/library/system.net.servicepointmanager.servercertificatevalidationcallback.aspx

#

# this callback will not work until Powershell 2.0 RTM

#

#   [Net.ServicePointManager]::ServerCertificateValidationCallback = {

#     param([Object]$object,  [Security.Cryptography.X509Certificates.X509Certificate]$certificate, [Security.Cryptography.X509Certificates.X509Chain]$chain, [Net.Security.SslPolicyErrors]$errors)

#     return $true

#   }

 

# first step, autodiscover the EXCHANGE box of ME, and connect to Exchange,

# then Impersonate and access the target users mailbox.

# see:  http://msdn.microsoft.com/en-us/library/bb204095.aspx

 

   $objService.AutodiscoverUrl($aceuser.mail.ToString())

   $enumSmtpAddress = [Microsoft.Exchange.WebServices.Data.ConnectingIdType]::SmtpAddress

   $objservice.ImpersonatedUserId =  New-Object Microsoft.Exchange.WebServices.Data.ImpersonatedUserId($enumSmtpAddress,$mailAddress)

# now we can bind to their Contacts folder

# see: http://msdn.microsoft.com/en-us/library/microsoft.exchange.webservices.data.wellknownfoldername.aspx

   $SCRIPT:ContactsFolder = [Microsoft.Exchange.WebServices.Data.Folder]::Bind($objService, [Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Contacts)

   $C = $ContactsFolder.TotalCount

   write-host "$C contacts under $mailAddress"

}

#

#################################################################################################

# Connected as the user and bound to their Contacts Folder, so ADD/Update contacts

# usage Add-Contacts-To-User

#################################################################################################

#

function Add-Contact-To-User {  

   begin {

     write-host "enter Add-Contact-To-User"

     $dllpath = "C:\Program Files\Microsoft\Exchange\Web Services\1.0\Microsoft.Exchange.WebServices.dll"

     [void][Reflection.Assembly]::LoadFile($dllpath)

   }

   process {

     $objService.TraceEnabled = $false

     $ldcb_verbose = $false

#  check if this Contact already exists

     $WhoToFind = $_."Contacts#FullName"

     write-host "$WhoToFind " -noNewLine

#  ItemView(1) says retrieve 1 item.

     $objView =  New-Object Microsoft.Exchange.WebServices.Data.ItemView(1)

     $objView.PropertySet  = New-Object Microsoft.Exchange.WebServices.Data.PropertySet([Microsoft.Exchange.WebServices.Data.ContactSchema]::Id)

     $objSF = New-Object Microsoft.Exchange.WebServices.Data.SearchFilter+IsEqualTo([Microsoft.Exchange.WebServices.Data.ContactSchema]::FileAs,$WhoToFind)

 

     $resultSet = $objService.FindItems([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Contacts,$objSF,$objView)

     $numFound = $resultSet.Items.Count

     $updateAction="ADD"

     if ($numFound -gt 0) {

        $existingContact = $resultSet.Items[0]

        $SCRIPT:ID = $existingContact.Id

#       $ck = $ID.ChangeKey

#       $ui = $ID.UniqueId

        $updateAction="UPDATE"

     }

#   now create the new object and load into Contacts...

     if ($updateAction -eq "ADD") {

       $objContact = New-Object Microsoft.Exchange.WebServices.Data.Contact($objService)

       Write-Host " ADDING"   

     }

     else {

       $objContact = [Microsoft.Exchange.WebServices.Data.Contact]::Bind($objService,$ID)

       Write-Host " UPDATING"

#      [enum]::getvalues([Microsoft.Exchange.WebServices.Data.ConflictResolutionMode])

       $enumAlwaysOverWrite = [Microsoft.Exchange.WebServices.Data.ConflictResolutionMode]::AlwaysOverWrite

     }

#   See all the fields here:

#   http://msdn.microsoft.com/en-us/library/microsoft.exchange.webservices.data.contact_members.aspx 

   

     $objContact.FileAs           = $_."Contacts#FullName"

     $objContact.GivenName        = $_."Contacts#FirstName"

     $objContact.Surname          = $_."Contacts#LastName"

     $objContact.CompanyName      = $_."Companies#CompanyName"

#    $objContact.Department       = "Slave Den"

#    $objContact.JobTitle         = "Cannon fodder"

#    $objContact.Profession       = "Software Fool"

#    $objContact.Manager          = "The Pointy Haired Boss"

     if ($_."Companies#Website".Length -gt 0) {

            $objContact.BusinessHomePage = $_."Companies#Website"

     }

#   see here for clues

#    http://msdn.microsoft.com/en-us/library/aa563318.aspx

#   and

#    http://msdn.microsoft.com/en-us/library/dd636262.aspx

#

#       Microsoft.Exchange.WebServices.Data.EmailAddressEntry

#       Microsoft.Exchange.WebServices.Data.ImAddressEntry

#       Microsoft.Exchange.WebServices.Data.PhoneNumberEntry

#       Microsoft.Exchange.WebServices.Data.PhysicalAddressEntry

 

     $objNewPhysicalAddress1 = New-Object Microsoft.Exchange.WebServices.Data.PhysicalAddressEntry

     $objNewPhysicalAddress2 = New-Object Microsoft.Exchange.WebServices.Data.PhysicalAddressEntry

# BUSINESS

     $objNewPhysicalAddress1.Street          = $_."Contacts#BusinessAddressStreet"

     $objNewPhysicalAddress1.City            = $_."Contacts#BusinessAddressCity"

     $objNewPhysicalAddress1.State           = $_."Contacts#BusinessAddressState"

     $objNewPhysicalAddress1.PostalCode      = $_."Contacts#BusinessAddressPostcode"

     $objNewPhysicalAddress1.CountryOrRegion = $_."Contacts#BusinessAddressCountry"

 

# OTHER

     $objNewPhysicalAddress2.Street          = $_."Companies#MailingAddressStreet"

     $objNewPhysicalAddress2.City            = $_."Companies#MailingAddressCity"

     $objNewPhysicalAddress2.State           = $_."Companies#MailingAddressState"

     $objNewPhysicalAddress2.PostalCode      = $_."Companies#MailingAddressPostCode"

     $objNewPhysicalAddress2.CountryOrRegion = $_."Companies#MailingAddressCountry"

 

# enum! see http://msdn.microsoft.com/en-us/library/exchangewebservices.physicaladdressdictionaryentrytype_members.aspx

#           http://msdn.microsoft.com/en-us/library/exchangewebservices.physicaladdresskeytype.aspx

 

#[enum]::getvalues([Microsoft.Exchange.WebServices.Data.PhysicalAddressKey])

     $enumBusinessValue = [Microsoft.Exchange.WebServices.Data.PhysicalAddressKey]::Business

     $enumHomevalue     = [Microsoft.Exchange.WebServices.Data.PhysicalAddressKey]::Home

     $enumOtherValue    = [Microsoft.Exchange.WebServices.Data.PhysicalAddressKey]::Other

 

#[enum]::getvalues([Microsoft.Exchange.WebServices.Data.EmailAddressKey])

     $enumEmailAddress1Value = [Microsoft.Exchange.WebServices.Data.EmailAddressKey]::EmailAddress1

     $enumEmailAddress2Value = [Microsoft.Exchange.WebServices.Data.EmailAddressKey]::EmailAddress2

     $enumEmailAddress3Value = [Microsoft.Exchange.WebServices.Data.EmailAddressKey]::EmailAddress3

 

#[enum]::getvalues([Microsoft.Exchange.WebServices.Data.PhoneNumberKey])

     $enumPhoneHomeValue        = [Microsoft.Exchange.WebServices.Data.PhoneNumberKey]::HomePhone

     $enumPhoneMobileValue      = [Microsoft.Exchange.WebServices.Data.PhoneNumberKey]::MobilePhone

     $enumPhoneBusinessValue    = [Microsoft.Exchange.WebServices.Data.PhoneNumberKey]::BusinessPhone

     $enumPhoneBusiness2Value   = [Microsoft.Exchange.WebServices.Data.PhoneNumberKey]::BusinessPhone2

     $enumPhoneCompanyMainValue = [Microsoft.Exchange.WebServices.Data.PhoneNumberKey]::CompanyMainPhone

     $enumPhoneBusinessFaxValue = [Microsoft.Exchange.WebServices.Data.PhoneNumberKey]::BusinessFax

     $objContact.PhysicalAddresses[$enumBusinessValue] = $objNewPhysicalAddress1

     $objContact.PhysicalAddresses[$enumOtherValue]    = $objNewPhysicalAddress2

     $BUG = 0

#    if we have something to write, write it.

#    otherwise check if there is an existing value - and remove it.

#

     if ($_."Contacts#Email".Length -gt 0) {

        $objContact.EmailAddresses[$enumEmailAddress1Value]   = $_."Contacts#Email"

     } else {

#        $mail will be the [Microsoft.Exchange.WebServices.Data.EmailAddress]  returned by TryGetValue()

         $mail = ""

         if ($ldcb_verbose) { Write-Host "`$_.`"Contacts#Email`" IS NULL" }

         $lookup = $objContact.EmailAddresses.TryGetValue($enumEmailAddress1Value, [ref] $mail)

         if ($lookup) {

##

##            $objContact.EmailAddresses[$enumEmailAddress1Value] = [System.String]::Empty

##            write-host "reset emailAddress1 to NULL"

              $BUG = $BUG + 1

         }

       }

 

#    if we have something to write, write it.

#    otherwise check if there is an existing value - and remove it.

#

     if ($_."Companies#DomainEmailAddress".Length -gt 0) {

        $objContact.EmailAddresses[$enumEmailAddress2Value]   = $_."Companies#DomainEmailAddress"

     } else {

#        $mail will be the [Microsoft.Exchange.WebServices.Data.EmailAddress]  returned by TryGetValue()

         $mail = ""

         if ($ldcb_verbose) { Write-Host "`$_.`"Companies#DomainEmailAddress`" IS NULL" }

         $lookup = $objContact.EmailAddresses.TryGetValue($enumEmailAddress2Value, [ref] $mail)

         if ($lookup) {

##

##            $objContact.EmailAddresses[$enumEmailAddress2Value] = [System.String]::Empty

##            if ($ldcb_verbose) {write-host "reset emailAddress2 to NULL" }

              $BUG = $BUG + 2

         }

       }

    #$objContact.EmailAddresses[$enumEmailAddress3Value]   = "secret.agent@cia.com"

 

    if ($_."Contacts#PhoneNumber".Length -gt 0) {

         $objContact.PhoneNumbers[$enumPhoneBusinessValue]     = $_."Contacts#PhoneNumber"

     }

    if ($_."Companies#GeneralPhoneNumber" -gt 0) {

        $objContact.PhoneNumbers[ $enumPhoneCompanyMainValue] = $_."Companies#GeneralPhoneNumber"

     }

#   $objContact.PhoneNumbers[$enumPhoneHomeValue]         = "6123456789"

    if ($_."Contacts#MobileNumber" -gt 0 ) {

     $objContact.PhoneNumbers[$enumPhoneMobileValue]       = $_."Contacts#MobileNumber"

     }

     if ($_."Contacts#FaxNumber".Length -gt 0) {

         $objContact.PhoneNumbers[$enumPhoneBusinessFaxValue]  = $_."Contacts#FaxNumber"

     } 

#    $objContact.Body = "Imagine some text in the Notes!"

     $objCategories  = New-Object Microsoft.Exchange.WebServices.Data.StringList

     if ($_."Contacts#ID" -eq $_."Companies#PrimaryContactLookup_ID") {

        $objCategories.Add("Primary Contact")

     }

     if ($_."Contacts#ID" -eq $_."Companies#AlternateContactLookup_ID") {

        $objCategories.Add("Alternate Contact")

     }

     if ($_."Contacts#ID" -eq $_."Companies#FinancialContactLookup_ID") {

        $objCategories.Add("Financial Contact")

     }

     if ($_."Contacts#ID" -eq $_."Companies#CommercialContactLookup_ID") {

        $objCategories.Add("Commercial Contact")

     }

     $objContact.Categories  = $objCategories

 

     if ($updateAction -eq "ADD") {

       $objContact.Save()

     }

     else {

       $objContact.Update($enumAlwaysOverWrite)

       if ($BUG -ne 0) {

         if (($BUG -eq 1) -or ($BUG -eq 3)) { EWS_BUG_WIPE_EMAILADDRESS_HACK ( 1 ) }

         if (($BUG -eq 2) -or ($BUG -eq 3)) { EWS_BUG_WIPE_EMAILADDRESS_HACK ( 2 ) }

       }

     }

    

     # Uncomment the below line when you want to stop and break after each update / add.

     # $throwAway = Read-Host

  }

}

 

function EWS_BUG_WIPE_EMAILADDRESS_HACK { PARAM ( $mail_Prop_N )

 

  $enumAlwaysOverWrite = [Microsoft.Exchange.WebServices.Data.ConflictResolutionMode]::AlwaysOverWrite

 

## see http://msdn.microsoft.com/en-us/library/bb905283.aspx

 

  if ( $Mail_Prop_N  -eq 1) {

    $LidEmailnDisplay    = 0x8080;

    $LidEmailnAddrType   = 0x8082;

    $LidEmailn           = 0x8083;

    $LidEmailnOrgDisplay = 0x8084;

    $LidEmailnOrgEntryID = 0x8085;

  }

  if ( $Mail_Prop_N  -eq 2) {

    $LidEmailnDisplay    = 0x8090;

    $LidEmailnAddrType   = 0x8092;

    $LidEmain            = 0x8093;

    $LidEmailnOrgDisplay = 0x8094;

    $LidEmailnOrgEntryId = 0x8095;

  }

  if ( $Mail_Prop_N  -eq 3) {

    $LidEmailnDisplay    = 0x80A0;

    $LidEmailnAddrType   = 0x80A2;

    $LidEmain            = 0x80A3;

    $LidEmailnOrgDisplay = 0x80A4;

    $LidEmailnOrgEntryId = 0x80A5;

  }

 

  $objPropSet                 = New-Object Microsoft.Exchange.WebServices.Data.PropertySet

  $objPropSet.BasePropertySet = [Microsoft.Exchange.WebServices.Data.BasePropertySet]::IdOnly

 

  $dfltExPropSet = [Microsoft.Exchange.WebServices.Data.DefaultExtendedPropertySet]::Address

 

  $PT_STRING = [Microsoft.Exchange.WebServices.Data.MapiPropertyType]::String

  $PT_BINARY = [Microsoft.Exchange.WebServices.Data.MapiPropertyType]::Binary

 

  $PidLidEmailnDisplay    = New-Object Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition($dfltExPropSet, $LidEmailnDisplay,    $PT_STRING);

  $PidLidEmailnAddrType   = New-Object Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition($dfltExPropSet, $LidEmailnAddrType,   $PT_STRING);

  $PidLidEmailn           = New-Object Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition($dfltExPropSet, $LidEmain,            $PT_STRING);

  $PidLidEmailnOrgDisplay = New-Object Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition($dfltExPropSet, $LidEmailnOrgDisplay, $PT_STRING);

  $PidLidEmailnOrgEntryId = New-Object Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition($dfltExPropSet, $LidEmailnOrgEntryId, $PT_BINARY);

 

  $objPropSet.Add($PidLidEmailnDisplay)

  $objPropSet.Add($PidLidEmailnAddrType)

  $objPropSet.Add($PidLidEmailn)

  $objPropSet.Add($PidLidEmailnOrgDisplay)

  $objPropSet.Add($PidLidEmailnOrgEntryId)

 

 

  $objContact = [Microsoft.Exchange.WebServices.Data.Contact]::Bind($objService, $ID, $objPropSet)

 

  [void] $objContact.RemoveExtendedProperty($PidLidEmailnDisplay)

  [void] $objContact.RemoveExtendedProperty($PidLidEmailnAddrType)

  [void] $objContact.RemoveExtendedProperty($PidLidEmailn)

  [void] $objContact.RemoveExtendedProperty($PidLidEmailnOrgDisplay)

  [void] $objContact.RemoveExtendedProperty($PidLidEmailnOrgEntryId)

 

  $objContact.Update($enumAlwaysOverWrite)

 

}

 

 

#

#########################################################################

# Get the contacts rows from the specified DB, via the specified query.

# Build an object and populate with the named columns.

#

# Usage: Get-SQLData "(local)" "Contacts" "Select * from dbo.Contacts"

#########################################################################

#

function Get-SQLData {

   param ($strSQLServer="(local)\SQLEXPRESS",$strDB="ContactDB",$strQuery="Select * from dbo.Contacts")

 

   $strConnection = "Data Source=$strSqlServer; Initial Catalog=$($strDB); Integrated Security=SSPI"

   $objConnection = New-Object System.Data.SqlClient.SqlConnection($strConnection)

   $objCommand = New-Object System.Data.SqlClient.sqlCommand($strQuery,$objConnection)

 

   $objConnection.open()

 

   $objDataSet = new-object System.Data.DataSet("ContactData")

   $objdataAdapter = new-object System.Data.SqlClient.SqlDataAdapter($strQuery,$objConnection)

 

   [void]$objDataAdapter.Fill($objDataSet,"ContactData")

 

   $objConnection.close()

 

   $colNames=$objDataSet.tables["ContactData"].columns | foreach-object {$_.columnName}

 

   $objDataSet.tables["ContactData"].rows | foreach-object {

      $objRow = $_

      $objPS = New-Object psobject

      $colNames |

         foreach-object {

          $colName = $_

        if ($objRow[$colName].GetType().Name -eq "DBNull") {

            #  Change NULLS to Empty Strings

              $objPS = $objPS | add-member -passthru NoteProperty $colName ""

        }else {

              $objPS = $objPS | add-member -passthru NoteProperty $colName $objRow[$colName]

         }

        }

      $objPS

   }

}

 

 

#

################################################################

#  Do-Contacts-Update   for each user, query SQL and pump in..

################################################################

#

function Do-Contacts-Update {

 

  Process {

   write-host "Processing $_"

   $ewsBinding = Bind-To-ContactsFolder $_

   Get-SQLData "(local)" "Contacts" "Select * from dbo.Contacts WHERE (Contacts#FullName IS NOT NULL AND Contacts#FullName <> ' ' AND Contacts#FullName <> 'Not Yet Specified' AND Contacts#FullName <> 'RETIRED' )" | Add-Contact-To-User

  }

}

 

#

########

#  MAIN

########

#

Get-Mail-Users "/OU=Key People" |  Do-Contacts-Update