How To Export Lync Contacts to Excel
Operator, oh could you help me place this call?
See, the number on the matchbook is old and faded…
- Jim Croce, “Operator (That’s Not the Way it Feels)”
When was the last time you called the telephone operator? Do operators even exist anymore? With your lists of contacts in your cell phone, Outlook, and Lync, how could you ever be without the number you need? And with everything all nicely synched up, you have all the numbers you need regardless of the device or the application you’re using. Right?
Okay, maybe not.
It’s possible that maybe things aren’t always synched. Because of that you might want to export your contacts from an application so you can either import them somewhere else, save them as a backup, or just have them available in another format. However, you’ll soon discover that Microsoft Lync 2010 doesn’t actually have an Export feature. But that’s okay, we’ve come up with a script to remedy that situation. This script will export your Lync 2010 contacts to a Microsoft Excel spreadsheet.
Requirements. Before you get too excited about this, there are a few things you need to know about. First of all, this script runs only on the client machine and applies to the currently logged-in Lync user. Lync must be running and you must be logged in to it for this script to work.
Second, you must have the Microsoft Lync 2010 SDK installed on the client machine.
And, of course, third, you need Windows PowerShell 2.0. You don’t need the Lync Server Management Shell; this script runs against Lync, not Lync Server.
Oh yeah, you also need Microsoft Excel.
I’ve overcome the blow, I’ve learned to take it well
I only wish my words could just convince myself…
Hey, come on, the requirements aren’t that bad!
We’re going to start by showing you a stripped-down version of the script. In this version we retrieve all the contact information and display it to the screen. After walking you through how to retrieve all the information we’ll show you the full script that exports everything to Excel.
Ready? Okay, here we go:
$assemblyPath = “C:\Program Files (x86)\Microsoft Lync\SDK\Assemblies\Desktop\Microsoft.Lync.Model.DLL”
Import-Module $assemblyPath
$DisplayName = 10
$PrimaryEmailAddress = 12
$Title = 14
$Company = 15
$Phones = 27
$FirstName = 37
$LastName = 38
$cl = [Microsoft.Lync.Model.LyncClient]::GetClient()
$gs = $cl.ContactManager.Groups
foreach ($g in $gs)
{
Write-Host
$g.Name
foreach ($contact in $g)
{
$contact.GetContactInformation($LastName)
$contact.GetContactInformation($FirstName)
$contact.GetContactInformation($Title)
$contact.GetContactInformation($Company)
$contact.GetContactInformation($PrimaryEmailAddress)
$eps = $contact.GetContactInformation($Phones)
foreach ($ep in $eps)
{
switch ($ep.Type)
{
"WorkPhone" {"work: " + $ep.DisplayName}
"MobilePhone" {"mobile: " + $ep.DisplayName}
"HomePhone" {"$home: " + $ep.DisplayName}
}
}
}
}
Yes, we did say this is the simplified version. But don’t worry, we’ll walk through it and you’ll see it’s not nearly as complicated as it might seem at first.
The first two lines are required anytime you’re going to be working with the Lync SDK:
$assemblyPath = “C:\Program Files (x86)\Microsoft Lync\SDK\Assemblies\Desktop\Microsoft.Lync.Model.DLL”
Import-module $assemblyPath
The first line sets a variable ($assemblyPath) to the full path of the DLL that contains the objects for the Microsoft.Lync.Model namespace. A namespace is simply a container for a bunch of objects, methods, and properties. (Stay with us here just a minute longer, it gets easier.) When we create objects (such as Lync Contacts) that are part of an SDK, Windows PowerShell doesn’t know where to get those objects from until you tell it which SDK, and which namespace, the object is in. The path we’ve provided here is the default installation path of the Lync SDK, and the Microsoft.Lync.Model.DLL file contains all the objects for the Microsoft.Lync.Model namespace. If you didn’t install the SDK to the default path you’ll need to modify this line.
That was probably more explanation than you needed, but it’s there in case you wanted to know. All you really need to know is that you have to include the full path to the DLL for this script to work, and you need to pass that DLL path to the Import-Module cmdlet:
Import-Module $assemblyPath
Import-Module is a Windows PowerShell cmdlet that reads the DLL and enables you to use all the objects, methods, and so on that are in that DLL.
Okay, we now have everything from the Lync SDK that we need to actually get started. The next thing we do is define some variables:
$DisplayName = 10
$PrimaryEmailAddress = 12
$Title = 14
$Company = 15
$Phones = 27
$FirstName = 37
$LastName = 38
We’ll get back to these in a bit. For now we’re going to move on:
$cl = [Microsoft.Lync.Model.LyncClient]::GetClient()
This line grabs the instance of Lync 2010 currently running on your machine and puts it in the variable $cl. It does this by calling the GetClient method of the Microsoft.Lync.Model.LyncClient class. (This class is part of the Lync SDK, so the script would crash at this line if we hadn’t imported the DLL with the Import-Module cmdlet.) Now that we have a reference to your Lync client stored in $cl, we need to access all the contact groups (such as Frequent Contacts and Current Contacts). We do that with this line:
$gs = $cl.ContactManager.Groups
In this line we retrieve the Groups collection, which is available through the client’s ContactManager object. The Groups collection contains just what it sounds like: all the group you have in your instance of Lync. We store the collection of groups in the variable $gs.
Now all we need to do is go through all the groups and pull out the information for each contact. To do that we set up a foreach loop to loop through the groups collection and look at one group at a time:
foreach ($g in $gs)
{
Write-Host
$g.Name
We start the loop by saying that for every group ($g) in the groups collection ($gs) we’re going to display a blank line, then the name of the group ($g.Name). (The Write-Host cmdlet, with no parameters or values, will write a blank line.) We don’t really need to do this to display contact information, but we thought it would be helpful to know which group each contact is in.
For each group, we want to get information for every contact within that group. What do you think we need to do next? That’s right, we need another foreach loop. (The For each at the beginning of the sentence gave it away, didn’t it?)
foreach ($contact in $g)
Each group is really a collection of contacts. So for each group ($g) we go through and retrieve information about one contact ($contact) at a time. In order to retrieve information about a contact, you call the GetContactInformation method:
$contact.GetContactInformation($LastName)
$contact.GetContactInformation($FirstName)
$contact.GetContactInformation($Title)
$contact.GetContactInformation($Company)
$contact.GetContactInformation($PrimaryEmailAddress)
Remember all those variables we defined at the beginning of the script? Well, here they are. The GetContactInformation method takes a number as a parameter. The number you pass to GetContactInformation determines the information the method will return. For example, if you pass GetContactInformation the value 38, it will return the last name of the contact. We could have written the preceding lines like this:
$contact.GetContactInformation(38)
$contact.GetContactInformation(37)
$contact.GetContactInformation(14)
$contact.GetContactInformation(15)
$contact.GetContactInformation(12)
This works just fine, but it’s not really considered good scripting. We put the numbers into variables with names that describe what they’re for, which makes the script much easier to read (and debug). This line doesn’t tell you what data to expect in return:
$contact.GetContactInformation(38)
Whereas this line makes it clear you’re expecting a last name:
$contact.GetContactInformation($LastName)
Note. Yes, you could just add comments to the script. (We haven’t done that here simply to keep things cleaner as we walk you through.) But it’s still best to use descriptive variables, too. Trust us.
Oh, and if you’re wondering where all these numbers are coming from, take a look at the Lync SDK documentation. It tells you which numbers will retrieve which piece of contact information.
Operator, oh could you help me place this call?
‘Cause I can’t read the number that you just gave me…
So as you can see, we’ve displayed the last name, first name, title, company, and primary email address of the contact. But what about this next line?
$eps = $contact.GetContactInformation($Phones)
Notice here that we put the output from the phone information into a variable. Why didn’t we simply display the phone numbers like we did everything else? Because it’s possible for a contact to have more than one phone number. You know what that means: that’s right, another foreach loop:
foreach ($ep in $eps)
One thing to note here: The variable $eps doesn’t actually contain a collection of phone numbers. What it contains is a collection of contact endpoints. An endpoint can be one of several things, including a telephone number or a SIP address. You determine what type of endpoint you’re working with by checking the Type property of the endpoint. Since we’re interested only in phone numbers, we put in a switch statement to take an action based on the endpoint type:
switch ($ep.Type)
{
"WorkPhone" {"work: " + $ep.DisplayName}
"MobilePhone" {"mobile: " + $ep.DisplayName}
"HomePhone" {"$home: " + $ep.DisplayName}
}
If you haven’t seen a switch statement before, don’t worry, it’s actually pretty simple. For this statement, we’re looking at the Type property of the endpoint, $ep.Type. Within the curly braces ({}) we include each of the values we’re interested in. We want to retrieve the contact’s work phone number, mobile phone number, and home phone number (assuming you have the level of access required to see those values, and that the contact has provided them). Take a look at the first line in the switch statement:
"WorkPhone" {"work: " + $ep.DisplayName}
All we’re doing here is checking to see if the value of $ep.Type is the string WorkPhone. If it is, we perform the actions inside the {}, which is to display the string work: followed by the DisplayName property of the endpoint ($ep.DisplayName). The DisplayName for an endpoint of type WorkPhone is the work phone number.
If the type matches WorkPhone, we display the work phone number, exit the switch statement, then move on to the next endpoint for the current contact. If the type isn’t WorkPhone, we check to see if the type is MobilePhone, and so on.
Note. For a list of all the endpoint types you can check for, take a look at the ContactEndpointType enumeration in the Lync SDK.
After we loop through all the endpoints, we go back and do this all over again with the next contact in the group. When we’re done with the current group, we go back to the first foreach loop we started with and move on to the next group, and once again do this all over again with all the contacts in that group. This continues until we run out of groups and contacts, at which point the script ends.
That’s all well and good that we can display this information to the Windows PowerShell window, but that really isn’t much more useful than simply looking at the information in Lync.
Isn’t that the way they say it goes?
Well let’s forget all that.
No, no, don’t forget all that; we still need everything we just did. But now we’re going to add to it. This script collects all the information from your Lync contacts, and it exports that information to an Excel spreadsheet. (You’ll probably need to do a little formatting of the spreadsheet to make it look nice.)
$assemblyPath = “C:\Program Files (x86)\Microsoft Lync\SDK\Assemblies\Desktop\Microsoft.Lync.Model.DLL”
Import-module $assemblyPath
$DisplayName = 10
$PrimaryEmailAddress = 12
$Title = 14
$Company = 15
$Phones = 27
$FirstName = 37
$LastName = 38
$objExcel = New-Object -ComObject Excel.Application
$wb = $objExcel.Workbooks.Add()
$item = $wb.Worksheets.Item(1)
$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"
$cl = [Microsoft.Lync.Model.LyncClient]::GetClient()
$gs = $cl.ContactManager.Groups
$i = 2
foreach ($g in $gs)
{
$gn = $g.Name
foreach ($contact in $g)
{
$ln = $contact.GetContactInformation($LastName)
$fn = $contact.GetContactInformation($FirstName)
$t = $contact.GetContactInformation($Title)
$c = $contact.GetContactInformation($Company)
$email = $contact.GetContactInformation($PrimaryEmailAddress)
$eps = $contact.GetContactInformation($Phones)
foreach ($ep in $eps)
{
switch ($ep.type)
{
"WorkPhone" {$work = $ep.DisplayName}
"MobilePhone" {$mobile = $ep.DisplayName}
"HomePhone" {$homep = $ep.DisplayName}
}
}
$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
$ln = ""
$fn = ""
$t = ""
$c = ""
$email = ""
$work = ""
$mobile = ""
$homep = ""
$i++
}
}
$objExcel.Visible = $True
# Remove the comment marks from the following lines to save
# the worksheet, close Excel, and clean up.
# $wb.SaveAs("C:\Scripts\LyncContacts.xlsx")
# $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-Variable objExcel
Remove-Variable wb
Remove-Variable item
We’re not going to explain how this all works, that’s a bit too much for this article. To learn about using Windows PowerShell to export data to Excel, take a look at this article . And if you’d like to see a version of this script fully commented, look here .
Thank you for your time
Oh you’ve been so much more than kind
You can keep the dime.