Export Lync Contacts to Excel

  • Article

This script exports contacts from Microsoft Lync 2010 to a Microsoft Excel worksheet.

Requirements:

· Lync 2010 must be running and you must be signed in.

· The Microsoft Lync 2010 SDK must be installed on the client machine.
Download the Lync 2010 SDK.

· You must have Microsoft Excel 2010 installed. (This should also work on previous versions of Excel, but we haven’t tested it so we can’t say for sure which ones. If it works on versions prior to 2007 you’ll need to change the file extension in the SaveAs call from .xlsx to .xls.)

· You must be running Windows PowerShell 2.0.

To run this script, copy the script, paste it into Notepad (or your favorite script editor) and save it with a .ps1 file extension. Start Windows PowerShell and type in the full path and filename of the saved script.

For a full explanation of how this script works, see the article How To Export Lync Contacts to Excel .

# Import the Lync SDK namespace.

# This is the default installation path; modify the path

# if you did not install the SDK to the default location.

$assemblyPath = “C:\Program Files (x86)\Microsoft Lync\SDK\Assemblies\Desktop\Microsoft.Lync.Model.DLL”

Import-module $assemblyPath

# Set the variables to retrieve specific contact information.

$DisplayName = 10

$PrimaryEmailAddress = 12

$Title = 14

$Company = 15

$Phones = 27

$FirstName = 37

$LastName = 38

# Open Excel.

$objExcel = New-Object -ComObject Excel.Application

$wb = $objExcel.Workbooks.Add()

$item = $wb.Worksheets.Item(1)

# Add a header row to the worksheet.

$item.Cells.Item(1,1) = "Contact Group"

$item.Cells.Item(1,2) = "Last Name"

$item.Cells.Item(1,3) = "First Name"

$item.Cells.Item(1,4) = "Title"

$item.Cells.Item(1,5) = "Company"

$item.Cells.Item(1,6) = "Primary Email Address"

$item.Cells.Item(1,7) = "Work Phone"

$item.Cells.Item(1,8) = "Mobile Phone"

$item.Cells.Item(1,9) = "Home Phone"

# Retrieve a reference to the Lync client. The client

# must be running and the user must be logged in.

$cl = [Microsoft.Lync.Model.LyncClient]::GetClient()

# Retrieve a collection of all the groups in the

# running instance of Lync.

$gs = $cl.ContactManager.Groups

$i = 2

# Loop through the collection of groups.

foreach ($g in $gs)

{

    # Retrieve the name of the current group.

    $gn = $g.Name

   

    # Loop through the contacts within each group.

    foreach ($contact in $g)

    {

        # Retrieve the specified contact information.

        $ln = $contact.GetContactInformation($LastName)

        $fn = $contact.GetContactInformation($FirstName)

        $t = $contact.GetContactInformation($Title)

        $c = $contact.GetContactInformation($Company)

        $email = $contact.GetContactInformation($PrimaryEmailAddress)

        $eps = $contact.GetContactInformation($Phones)

       

        # Loop through the endpoints to retrieve phone numbers.

        foreach ($ep in $eps)

        {

            switch ($ep.type)

     {

                "WorkPhone" {$work = $ep.DisplayName}

                "MobilePhone" {$mobile = $ep.DisplayName}

                "HomePhone" {$homep = $ep.DisplayName}

             }

        }

           

        # Add the contact information to the worksheet.

        $item.Cells.Item($i,1) = $gn

        $item.Cells.Item($i,2) = $ln

        $item.Cells.Item($i,3) = $fn

        $item.Cells.Item($i,4) = $t

        $item.Cells.Item($i,5) = $c

        $item.Cells.Item($i,6) = $email

        $item.Cells.Item($i,7) = $work

        $item.Cells.Item($i,8) = $mobile

        $item.Cells.Item($i,9) = $homep

          

        # Clear the contact information variables to

        # prepare for the next contact.

      $ln = ""

        $fn = ""

        $t = ""

        $c = ""

        $email = ""

        $work = ""

        $mobile = ""

        $homep = ""

         

        # increment the row counter by 1.

        $i++

          

    }

}

# Display Excel onscreen.

# Move this line up earlier in the script to see the

# worksheet as it's being populated.

$objExcel.Visible = $True

# Save the file.

# If the file exists, you'll be prompted to overwrite it.

$wb.SaveAs("C:\Scripts\LyncContacts.xlsx")

# Remove or comment out the following lines if you want

# to leave Excel open and close it manually.

# Quit Excel and clean up memory.

$objExcel.Quit()

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb) | Out-Null

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($item) | Out-Null

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel) | Out-Null

[System.GC]::Collect()

[System.GC]::WaitForPendingFinalizers()

# Remove the object variables.

Remove-Variable objExcel

Remove-Variable wb

Remove-Variable item