Office 365 services change at a rapid pace, with new features added each week. Getting the word out to your users about these changes can be difficult, especially if not all changes and features will apply to everyone.
To help solve this problem, we’re using this PowerShell script. It collects user and licence info from all customer Office 365 tenants, and signs them up to a MailChimp List. In this case, the list is called ‘Office 365 Tips’.
It also creates list segments based on the licences that your users have, allowing you to filter your user base and send out communications when a new feature is released for that licence type.
For example, you can let all the Project Online users know when something cool has been released for Project Online, or tell the Business Premium users to get onto Microsoft Teams, or let the Exchange Online Plan 1 users know that they’re missing out on Microsoft Teams and they should upgrade 🙂
You can run the script before each mailout to ensure that it stays in sync. If a user’s details change, they’ll be updated in MailChimp. If a MailChimp list member is no longer licensed in Office 365, they’ll be unsubscribed from the list
Prerequisites
- To set this up, you’ll need a mailchimp account and delegated access to your customers’ tenants.
- You’ll also need permission to contact these users. If you’re a managed service provider, this might be an inclusion for your customer contracts.
- If you’re running the single Office 365 tenant version of this script, you’ll need the admin details for that tenant.
Running the script to sync customers’ Office 365 users with MailChimp
- Sign into MailChimp
- Click your account name on the top left, then click Account
- Click Extras, then API keys.
- Click Create A Key
- Copy the API Key value and paste it into Notepad for later. Take note of the data center value at the end. In our case it’s us14.
- You should also see it in the URL of the current page.
- Copy and paste the script at the bottom of this page into Visual Studio Code.
- Save the file as a Powershell (.ps1) file
- Update the script with a username and apiKey. The username can actually be whatever you like, I’ve just set it to be gcits.
- Update the baseUri variable with the dc details of your MailChimp account
- Update the listInfo object with the correct values for your business.
- Press F5 to run the script and sign in using your Office 365 credentials with delegated access to your customers’ tenants. If you’re running the single tenant version of this script, just sign in with your Office 365 admin credentials.
- The script will then create a MailChimp list with the required merge fields and collect the information on your licensed users. It’ll use their licence info to build a collection of licence types to create MailChimp List Segments.
- The Office 365 licence segments are then added to the MailChimp list, and the list is checked for existing members. All Office 365 users that don’t exist in the list are added to a collection for upload in batches of 100.
- If existing user details have changed, the users are added to a collection for upload in batches of 100 too.
- If an existing MailChimp list member is no longer licensed in Office 365, they’ll be unsubscribed from the list.
- Once completed, you can switch over to MailChimp and see your new list.
- Open the list to view your Office 365 user information.
- All licence types will be added as segments that you can use to filter and contact your users.
Download scripts to sync your own or customers’ Office 365 users with a MailChimp List
The on-page script below is for Microsoft Partners who manage multiple Office 365 tenants for their customers. If you’d like to run this script against a single tenant, you can download first script below. Just remember to save it as a PowerShell (.ps1) file first.
- Download script to sync a single tenant’s Office 365 users with MailChimp.
- Download script to sync all customers’ Office 365 users with MailChimp.
Complete script to sync customers’ Office 365 users with a MailChimp list
$user = "anythingGoesHere"
$apiKey = "APIKeyGoesHere"
$pair = "${user}:${apiKey}"
$bytes = [System.Text.Encoding]::ASCII.GetBytes($pair)
$base64 = [System.Convert]::ToBase64String($bytes)
$basicAuthValue = "Basic $base64"
$Headers = @{
Authorization = $basicAuthValue
}
$baseUri = "https://[DC].api.mailchimp.com"
$listInfo = @{
listName = "Office 365 Tips"
company = "GCITS"
address1 = "38 Myeerimba Parade"
address2 = " "
city = "Tweed Heads"
state = "NSW"
zip = "2485"
country = "Australia"
phone = "+61755999888"
permission_reminder = "You are recieving this email because you are a client of GCITS"
from_name = "Elliot Munro from GCITS"
from_email = "admin@contoso.onmicrosoft.com"
subject = " "
language = "en"
}
$Office365members = @()
Connect-MsolService
function Ensure-MailChimpList ($ListName) {
$lists = Invoke-RestMethod -URI $baseUri/3.0/lists?offset=0"&"count=100 -Method Get -Headers $Headers
if ($lists.lists.name -notcontains $ListName) {
$list = New-MailChimpList -ListInfo $listInfo
return $list
}
else {
$list = $lists.lists | Where-object {$_.name -contains $listName}
return $list
}
}
function New-MailChimpList ($ListInfo) {
$listBody = @{
name = $listInfo.listName
contact = @{
company = $listInfo.company
address1 = $listInfo.address1
address2 = $listInfo.address2
city = $listInfo.city
state = $listInfo.state
zip = $listInfo.zip
country = $listInfo.country
phone = $listInfo.phone
}
permission_reminder = $listInfo.permission_reminder
campaign_defaults = @{
from_name = $listInfo.from_name
from_email = $listInfo.from_email
subject = $listInfo.subject
language = $listInfo.language
}
email_type_option = $false
}
$listBody = $listBody | ConvertTo-Json
$newList = Invoke-RestMethod -URI $baseUri/3.0/lists -Method POST -Headers $Headers -Body $listBody
return $newList
}
function Get-MailChimpListSegments {
$segments = Invoke-RestMethod -URI $baseUri/3.0/lists/$listId/segments?offset=0"&"count=200 -Method Get -Headers $Headers
return $segments
}
function Get-MailChimpListMergeFields {
$mergeFields = Invoke-RestMethod -URI $baseUri/3.0/lists/$listId/merge-fields -Method Get -Headers $Headers
return $mergeFields
}
function New-MailChimpListMergeField ($Name, $Type, $Tag) {
$merge_field = @{
name = $Name
type = $Type
tag = $Tag
}
$merge_field = $merge_field | ConvertTo-Json
Invoke-RestMethod -URI $baseUri/3.0/lists/$listId/merge-fields -Method POST -Headers $Headers -Body $merge_field
}
function New-MailChimpListBatch ($MemberBatch) {
$body = @{
members = $MemberBatch
update_existing = $true
}
$body = $body | ConvertTo-Json -Depth 10
$batchresult = Invoke-RestMethod -URI $baseUri/3.0/lists/$listId -Method POST -Headers $Headers -Body $body
return $batchresult
}
function New-MailChimpListMember ($email, $FirstName, $LastName, $Company, $Licenses) {
$merge_fields = @{
FNAME = $FirstName
LNAME = $LastName
COMPANY = $Company
LICENSES = $Licenses
}
$member = @{
email_address = $email
status = "subscribed"
merge_fields = $merge_fields
}
return $member
}
function Update-MailChimpListMember ($ExistingMember, $Office365Member) {
$merge_fields = @{
FNAME = $Office365Member.FirstName
LNAME = $Office365Member.LastName
COMPANY = $Office365Member.Company
LICENSES = $Office365Member.Licenses
}
$member = @{
email_address = $Office365Member.Email
status = $existingMember.Status
merge_fields = $merge_fields
}
return $member
}
function Unsubscribe-MailChimpListMember ($ExistingMember) {
$member = @{
email_address = $ExistingMember.Email
status = "unsubscribed"
}
return $member
#Invoke-RestMethod -URI $baseUri/3.0/lists/$listId/members/$($existingMember.SubscriberHash) -Method PATCH -Headers $Headers -Body $member
}
function Get-ExistingMailChimpListMembers {
$existingMembers = $null
$existingmembers = Invoke-RestMethod -URI $baseUri/3.0/lists/$listId/members?offset=0"&"count=100 -Method Get -Headers $Headers
for ($i = 100; $i -le $existingMembers.total_items; $i += 100) {
$members = Invoke-RestMethod -URI $baseUri/3.0/lists/$listId/members?offset=$i"&"count=100 -Method Get -Headers $Headers
$existingMembers.members += $members.members
}
return $existingMembers
}
function New-LicenseSegment ($SkuPartNumber) {
$conditions = @()
$conditionproperty = @{
condition_type = "TextMerge"
field = "LICENSES"
op = "contains"
value = $SkuPartNumber.TrimStart("License: ")
}
$conditions += $conditionproperty
$segment = @{
name = $SkuPartNumber
options = @{
match = "any"
conditions = $conditions
}
}
$segment = $segment | ConvertTo-Json -Depth 10
$newSegment = Invoke-RestMethod -URI $baseUri/3.0/lists/$listId/segments -Method POST -Headers $Headers -Body $segment
return $newSegment
}
function Create-ExistingMemberCollection ($ExistingMembers) {
$existingMemberCollection = @()
foreach ($existingmember in $existingmembers) {
$memberObject = New-Object PSObject -Property @{
Licenses = $existingmember.merge_fields.LICENSES
FirstName = $existingmember.merge_fields.FNAME
LastName = $existingmember.merge_fields.LNAME
Company = $existingmember.merge_fields.COMPANY
Email = $existingmember.email_address
SubscriberHash = $existingmember.id
Status = $existingmember.status
}
$existingMemberCollection += $memberObject
}
return $existingMemberCollection
}
function Create-UploadBatch ($collection) {
# Upload the collection in batches of 100
$counter = [pscustomobject] @{ Value = 0 }
$batchsize = 100
$batches = $collection | Group-Object -Property { [math]::Floor($counter.Value++ / $batchSize) }
foreach ($batch in $batches) { New-MailChimpListBatch -MemberBatch $batch.Group}
}
# Check whether list exists and create it if it doesn't
$listID = (Ensure-MailChimpList -ListName $listInfo.listName).id
# Check for current merge fields in list
Write-Host "Retrieving Merge Fields"
$mergeFields = Get-MailChimpListMergeFields
$updateSegments = $false
$requiredMergeFields = "Company", "Licenses"
foreach ($requiredMergeField in $requiredMergeFields) {
if (!$mergeFields.merge_fields.Name.contains($requiredMergeField)) {
Write-Host "Creating Merge Field: $requiredMergeField" -ForegroundColor Yellow
$newMergeField = New-MailChimpListMergeField -Name $requiredMergeField -Type text -Tag $requiredMergeField.ToUpper()
}
}
# Retrieve existing segments, then filter by license related segments
$existingSegments = Get-MailChimpListSegments
$existingLicenseSegments = $existingSegments | Where-Object {$_.segments.name -match "License: "}
$requiredSegments = @()
# Get all customers from Office 365. Add a 'Where-Object {$_.name -match "CustomerName" -or $_.name -notmatch "CustomerName"}' filter here to include or exclude specific customers.
$customers = Get-MsolPartnerContract
# Create collection containing required info for all users in all tenants
foreach ($customer in $customers) {
Write-Host "Retrieving users and license info for $($customer.name)" -ForegroundColor Blue
$users = $null
$users = Get-MsolUser -TenantId $customer.TenantId | Where-Object {$_.islicensed}
$company = (Get-MsolCompanyInformation -TenantId $customer.TenantId).DisplayName
foreach ($user in $users) {
[string[]]$userLicenses = $null
foreach ($license in $user.Licenses.AccountSku.SkuPartNumber) {
$userLicenses += "'$license'"
}
$userLicensesString = $userLicenses -join ","
# Check if these licenses already have segments created or queued
foreach ($license in $userlicenses) {
$expectedSegmentName = "License: $license"
if ($existingLicenseSegments.segments.Name -notcontains $expectedSegmentName -and $requiredSegments -notcontains $expectedSegmentName) {
$requiredSegments += $expectedSegmentName
Write-Host "Adding $expectedSegmentName to required segments list. Update pending." -ForegroundColor Yellow
$updateSegments = $true
}
}
$firstName = $user.FirstName
if (!$user.FirstName) {
$firstName = $user.DisplayName.Split(" ")[0]
}
$lastName = $user.LastName
if (!$user.lastName -and !$user.firstname) {
$lastName = $user.DisplayName.Split(" ") | Where-Object {$_ -notcontains $user.DisplayName.Split(" ")[0]}
$lastName = $lastName -join " "
}
elseif (!$user.lastName -and $user.firstname) {
$lastname = " "
}
elseif (!$user.LastName -and !($user.DisplayName.Split(" ") | Where-Object {$_ -notcontains $user.DisplayName.Split(" ")[0]})) {
$lastname = " "
}
$email = $user.UserPrincipalName
$memberObject = New-Object PSObject -Property @{
Licenses = $userLicensesString
FirstName = $firstName
LastName = $lastName
Company = $company
Email = $email
}
$Office365members += $memberObject
}
}
Write-Host "Retrieved $($office365Members.count) licensed users from $($customers.Count) customers" -ForegroundColor Green
# If an update is required, add new segments
if ($updateSegments) {
Write-Host "New segments required: $requiredSegments" -ForegroundColor Green
# Create new license segments
if ($requiredSegments.count -gt 0) {
foreach ($requiredSegment in $requiredSegments) {
Write-Host "Creating new segment - $requiredSegment" -ForegroundColor Blue
$newSegment = New-LicenseSegment -SkuPartNumber $requiredSegment
}
}
}
# Check if members exist in list
# Build list of existing members
Write-Host "Getting existing members"
$existingMembers = (Get-ExistingMailChimpListMembers).members
$newMemberCollection = @()
# Check if Office 365 users already exist in mailchimp. If they don't, add them to a collection for upload
foreach ($member in $Office365members) {
if ($existingMembers.email_address -notcontains $member.Email) {
Write-Host "Adding $($member.firstName) $($member.lastName) to new subscriber collection" -ForegroundColor Green
$newMemberObject = New-MailChimpListMember -email $member.Email -FirstName $member.FirstName -LastName $member.LastName -Licenses $member.Licenses -Company $member.Company
$newMemberCollection += $newMemberObject
}
}
Create-UploadBatch -collection $newMemberCollection
# Retrieve updated list of existing members
$existingMembers = (Get-ExistingMailChimpListMembers).members
$existingMemberCollection = Create-ExistingMemberCollection -ExistingMembers $existingMembers
# Update users when details have changed.
$updatedMemberCollection = @()
foreach ($existingMember in $existingMemberCollection) {
$office365Member = $null
$office365Member = $Office365members | Where-Object {$_.Email -contains $existingMember.Email}
if ($office365member) {
if ($office365Member.FirstName -notmatch $existingMember.FirstName -or `
$office365Member.LastName -notmatch $existingMember.LastName -or `
$office365Member.Licenses -notmatch $existingMember.Licenses -or `
$office365Member.Company -notmatch $existingMember.Company) {
Write-Host "Updating record for $($existingMember.Email)" -ForegroundColor Yellow
$updatedMemberObject = Update-MailChimpListMember -ExistingMember $existingMember -Office365Member $office365Member
$updatedMemberCollection += $updatedMemberObject
}
}
}
Create-UploadBatch ($updatedMemberCollection)
# Unsubscribe users who no longer exist in Office 365
$unsubscribedMemberCollection = @()
foreach ($existingMember in $existingMemberCollection) {
if ($Office365members.Email -notcontains $existingmember.Email -and $existingmember.status -notcontains "unsubscribed") {
Write-Host "$($existingmember.Email) is no longer licensed in Office 365, unsubscribing." -ForegroundColor Red
$unsubscribedMemberObject = Unsubscribe-MailChimpListMember -ExistingMember $existingMember
$unsubscribedMemberCollection += $unsubscribedMemberObject
}
}
Create-UploadBatch ($unsubscribedMemberCollection)
