Office 365 Automated Signature Generator

I’ve been looking for a while at a way to automate email signatures for everybody using OWA on Office 365. The new layout we want for our signatures includes images and everywhere I’ve read says it’s impossible to embed images in a signature set using Powershell. (Note I want them embedded rather than hotlinked).

The solution I came up with takes details from the Active Directory user account – I’m using the description field to insert the person’s name (to allow things like “Mrs Blah” rather than just outputting firstname surname), fields like title (job title), telephone, mobile and also using a few of the extensionAttributes for the Twitter/Facebook links. All of these are standard fields so no need to mess with the AD schema.

This solution is split into two parts – one script which takes a CSV file and updates active directory (I created a form on Office 365 to ask people to enter what they wanted their name displayed as, job title, if they wanted a different phone number than the switchboard showing, alternate twitter etc links), and one which updates the mailbox configuration.

First of all you will need to create a template HTML file. Add the following fields to be substituted by the script:

{{Name}}
{{Title}}
{{Tel}}
{{Mobile}}
{{Facebook}}
{{Twitter}}

Next, where you want to embed an image, you need to do the following:

<img src="" style=""><span id="dataURI" style="display:none">data:image/png;base64,IMAGE_DATA_HERE</span>

You will need to convert your image into base64 encoding (I used https://www.base64-image.de/). The above code probably won’t work if you try it out in a web browser as it’s completely non-standard – to discover how OWA does images in signatures I had to create a signature with embedded image in the OWA interface, then run Get-MailboxMessageConfiguration and examine the HTML it had generated. Paste in the base64 encoding over IMAGE_DATA_HERE.

You can use multiple images all with the same method – don’t change the id on the SPAN or it won’t work (and don’t worry that there are multiple elements with the same ID as it gets converted into a properly embedded image when it leaves OWA).

Here’s a cut down version of my signature template:

<body style="font-family: Calibri,Helvetica,sans-serif;">
<table style="border: none;">
  <tr>
    <td style="border-right: 1px solid #000000; text-align: center; width: 250px;">
      <img src="" style=""><span id="dataURI" style="display:none">data:image/png;base64,logo_data_here</span>
    </td>
    <td style="padding-left: 10px;">
      <span style="font-weight: bold;font-size:12pt;color:#000000;font-family:Calibri,Helvetica,sans-serif;;">{{Name}}</span><br />
      <span style="font-size:12pt;color:#000000;font-family:Calibri,Helvetica,sans-serif;">{{Title}}<br /><br />
      <b>Place name</b><br />
      Address<br />
      goes<br />
      here</span><br /><br />
      <span style="font-weight: bold;font-size:12pt;color:#000000;font-family:Calibri,Helvetica,sans-serif;">{{Tel}}</span><br />
      <span style="font-size:12pt;color:#000000;font-family:Calibri,Helvetica,sans-serif;">{{Mobile}}</span>
      <br /><br />
      <a href="https://www.facebook.com/{{Facebook}}" target="_blank"><img src="" style=""><span id="dataURI" style="display:none">data:image/png;base64,facebook_logo_here</span></a>
      &nbsp;
      <a href="https://twitter.com/{{Twitter}}" target="_blank"><img src="" style=""><span id="dataURI" style="display:none">data:image/png;base64,twitter_logo_here</span></a>
    </td>
  </tr>
</table>
</body>

There is also a field on the mailbox for a plain text signature – although in practise OWA doesn’t seem to use this (if you send a plain text email it just converts the HTML signature to plain text anyway)

Next is the powershell script to update Active Directory – this reads the CSV and updates each user account object in AD. The CSV needs the following fields:

Email, Name, Title, Tel, Mobile, Twitter, Facebook.

Email should match the UserPrincipalName of each user, Title refers to job title.

$csvPath = "\\path\to\signature_data.csv"
$csv = Import-CSV $csvPath
foreach ($entry in $csv) {
  $upn = $entry.Email
  if ($user = Get-ADUser -Filter {UserPrincipalName -like $upn} -SearchBase "dc=your,dc=domain,dc=org,dc=uk" -Properties mail) {
    Set-ADUser $user -Clear "description"
    Set-ADUser $user -Clear "title"
    Set-ADUser $user -Clear "telephoneNumber"
    Set-ADUser $user -Clear "mobile"
    Set-ADUser $user -Clear "extensionAttribute3"
    Set-ADUser $user -Clear "extensionAttribute4"
    Set-ADUser $user -Add @{description=$entry.Name}
    if ($entry.Title) { Set-ADUser $user -Add @{title=$entry.Title} }
    if ($entry.Tel) { Set-ADUser $user -Add @{telephoneNumber=$entry.Tel} }
    if ($entry.Mobile) { Set-ADUser $user -Add @{mobile=$entry.Mobile} }
    if ($entry.Twitter) { Set-ADUser $user -Add @{extensionAttribute3=$entry.Twitter} }
    if ($entry.Facebook) { Set-ADUser $user -Add @{extensionAttribute4=$entry.Facebook} }
    Write-Host $user
  }
}

The final part is the script to merge the user data into the template and update their mailbox configuration. Here we connect to the Office 365 remote powershell, then loop for all users with a UPN ending in @your.domain.org.uk, within the search base (the Users OU).

If the user hasn’t provided any data for the previous script then their description will probably be blank – so in this case we just use the displayName instead. Most other fields are optional and only used to override the default set in this script – e.g. phone number, if a user wants to show their DDI rather than the main switchboard, or if a department has their own twitter feed.

We then add the content of extensionAttribute2 to the bottom of the signature – this isn’t mentioned or populated in any of the other scripts, but it’s able to be manually populated via AD Users and Computers. In my case it’s used to add a line saying to email the IT helpdesk with any issues (in a failed attempt to stop people emailing me directly).

Finally the signature is applied with Set-MailboxMessageConfiguration, along with flags to automatically add the signature to new emails (but not to replies/forwards), and also to set “Reply” as the default option in OWA (because nobody wants “Reply All” to be the default, one of the stupider decisions to come out of Microsoft recently!)

$templatePathHTML = "\\path\to\email_signature_html.htm"

$UserCredential = Get-Credential
$session = New-PSSession -ConfigurationName Microsoft.Exchange -Credential $UserCredential -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Authentication Basic -AllowRedirection
Import-PSSession $session

$users = Get-ADUser -Filter {UserPrincipalName -like "*@your.domain.org.uk"} -SearchBase "ou=Users,dc=your,dc=domain,dc=org,dc=uk" -Properties extensionAttribute2, extensionAttribute3, extensionAttribute4, displayName, userPrincipalName, mail, description, mobile, telephoneNumber, title

$signatureTemplateHTML = Get-Content $templatePathHTML

foreach ($user in $users) {
  $signaturehtml = $signatureTemplateHTML
  if ($user.description) {
    $signaturehtml = $signaturehtml.Replace("{{Name}}", $user.description)
    $signaturehtml = $signaturehtml.Replace("{{Title}}", $user.title)
  } else {
    $signaturehtml = $signaturehtml.Replace("{{Name}}", $user.displayName)
    $signaturehtml = $signaturehtml.Replace("{{Title}}", "")
  }
  if ($user.telephoneNumber.Length -gt 1) {
    $signaturehtml = $signaturehtml.Replace("{{Tel}}", $user.telephoneNumber)
  } else {
   $signaturehtml = $signaturehtml.Replace("{{Tel}}", "switchboard-number-here")
  } 
  if ($user.mobile.Length -gt 1) {
    $signaturehtml = $signaturehtml.Replace("{{Mobile}}", $user.mobile)
  } else {
   $signaturehtml = $signaturehtml.Replace("{{Mobile}}", "")
  }
  if ($user.extensionAttribute3) {
    $signaturehtml = $signaturehtml.Replace("{{Twitter}}", $user.extensionAttribute3)
  } else {
    $signaturehtml = $signaturehtml.Replace("{{Twitter}}", "main-company-twitter")
  } 
  if ($user.extensionAttribute4) {
    $signaturehtml = $signaturehtml.Replace("{{Facebook}}", $user.extensionAttribute4)
  } else {
    $signaturehtml = $signaturehtml.Replace("{{Facebook}}", "main-company-facebook")
  }
  if ($user.extensionAttribute2) {
    $signaturehtml = $signaturehtml + "<br /><br />" + $user.extensionAttribute2 + "<br />"
  }
  Write-Host $user.displayName
  Set-MailboxMessageConfiguration -Identity $user.userPrincipalName -SignatureHTML $signaturehtml -AutoAddSignature $true -AutoAddSignatureOnReply $false -IsReplyAllTheDefaultResponse $false
}

Once I’d imported data for most users and applied the signature for all users, I created a combination of the two which only updates the signature for users present in the CSV, to allow people to manually customise signatures without them being constantly overwritten.

Unfortunately the full Outlook client doesn’t get its signature data from the Exchange server so people will still have to alter their signatures manually if they are using the full client.