Using Self-Signed S/MIME Certificates in Outlook

If you followed my guide to create self-signed S/MIME certificates, then you will have the necessary files to begin digitally signing and receiving encrypted e-mail. As long as the e-mail client supports S/MIME, which Outlook does support, then you can create and use your own certificates for any e-mail address including custom domains, Gmail, iCloud, or even AOL. This guide describes the process for using self-signed S/MIME certificates with Microsoft Outlook 2019 found in the Microsoft Office Professional Plus 2019 suite running on Windows 10.

Part 1 – Trusting the Self-Signed Certificate Authority

Since we’re using self-signed certificates, Windows and Outlook will not automatically recognize your personal certificate authority. If a certificate authority is not trusted, then any certificates issued by that certificate authority are not trusted and they are considered invalid by Outlook. If you try to send a digitally signed e-mail using your personal certificate before the certificate authority is trusted, then Outlook displays the message “Microsoft Outlook cannot sign or encrypt this message because your certificate is not valid.”

Invalid Certificate
Invalid Certificate

Step 1 – Install Certificate

Find the certificate authority certificate. If you followed my guide, the file is named ca.crt. Double-click on the file in File Explorer to open it. The Certificate Information screen is displayed. Confirm that the Issue to, Issued by, and Valid from/to dates match the expected values.

Click the Install Certificate… button.

Install Self-Signed Certificate Authority Certificate
Install Self-Signed Certificate Authority Certificate

Step 2 – Select Store Location

The Certificate Import Wizard screen is displayed. Choose Current User and click the Next button.

Certificate Import Wizard – Store Location
Certificate Import Wizard – Store Location

Step 3 – Select Certificate Store

The Certificate Store screen is displayed. Select Place all certificates in the following store and click the Browse… button.

Certificate Import Wizard – Store Selection
Certificate Import Wizard – Store Selection

The Select Certificate Store screen is displayed. Select Trusted Root Certification Authorities and click the OK button.

Certificate Import Wizard – Trusted Root Certification Authorities
Certificate Import Wizard – Trusted Root Certification Authorities

Confirm the details on the Select Certificate Store screen and click the Next button.

Step 4 – Complete the Certificate Import Wizard

The Completing the Certificate Import Wizard screen is displayed. Confirm the displayed details and click the Finish button.

Certificate Import Wizard – Completion
Certificate Import Wizard – Completion

Step 5 – Security Warning

A Security Warning screen is displayed requesting confirmation that the certificate should be installed. Confirm the displayed details and click the Yes button.

Security Warning
Security Warning

The Import Successful dialog box is displayed.

Import Successful
Import Successful

Close the Certificate Information screen by clicking the OK button.

Step 6 – Confirm Trusted Certification Authorities in CertMgr

From the Windows start menu, run certmgr (Manager user certificates). Under Current User, expand Trusted Root Certification Authorities and click Certificates. Review the list of certificates to confirm your certificate authority is in the store. Close the application.

CertMgr – Trusted Root Certification Authorities
CertMgr – Trusted Root Certification Authorities

Part 2 – Installing the Self-Signed S/MIME Certificate in Outlook

With the certificate authority certificate in the Windows trust store, we can now add our self-signed S/MIME certificate to Outlook.

Step 1 – Open Trust Center

Open Outlook and select File and then Options. The Outlook Options screen is displayed. Select Trust Center.

Outlook Options – Trust Center
Outlook Options – Trust Center

Step 2 – Open Email Security

Click the Trust Center Settings… button. The Trust Center screen is displayed. Select Email Security.

Trust Center – Email Security
Trust Center – Email Security

Step 3 – Import Self-Signed S/MIME Certificate

Click the Import/Export… button. The Import/Export Digital ID screen is displayed. In the Import existing Digital ID from a file section, click the Browse… button. Find the relevant PKCS12 file. If you followed my guide, the file is named smime_test_user.p12. Enter the password for the package.

Trust Center – Import/Export Digital ID
Trust Center – Import/Export Digital ID

Click the OK button.

Step 4 – Import Certificate

The Importing a new private exchange key dialog box is displayed. Click the OK button.

Importing a New Private Exchange Key
Importing a New Private Exchange Key

Step 5 – Change Security Settings

Returning to the Trust Center screen, click the Settings… button. The Change Security Settings screen is displayed. Confirm the displayed information is correct. Change the Security Settings Name value to a unique name for the certificate.

Trust Center – Change Security Settings
Trust Center – Change Security Settings

If the Signing Certificate or Encryption Certificate are blank, then click either Choose… button. The Windows Security Confirm Certificate dialog box is displayed. Click the OK button.

Windows Security – Confirm Certificate
Windows Security – Confirm Certificate

Confirm the information on the Change Security Settings dialog box are correct and click the OK button.

Step 6 – Confirm Default Settings

On the Trust Center Email Security settings screen, confirm the Default Setting references the security settings name created in the prior step. Click the OK button to close the Trust Center screen. On the Outlook Options screen, click the OK button to close the screen.

Trust Center – Email Security Settings Completed
Trust Center – Email Security Settings Completed

Part 3 – Sending a Digitally Signed E-mail

Finally, we can send a digitally signed e-mail in Outlook using a self-signed S/MIME certificate issued by a personal certificate authority. As a reminder, this does not allow you to send encrypted e-mails since public-key cryptography requires the sender to have the public key for the recipient (we only have the sender keys). However, this does allow you to send a digitally signed e-mail to a recipient. Since the digital signature contains your public key, the recipient can than respond with an encrypted e-mail after establishing trust in their e-mail client.

Step 1 – Draft and Sign E-mail

Create a new e-mail using the e-mail address associated with the S/MIME certificate. From the Options ribbon, click Sign.

Outlook – Digitally Signed E-mail
Outlook – Digitally Signed E-mail

Step 2 – Send E-mail

Complete the e-mail and click the Send button. A Windows Security dialog box is displayed requesting access to the private key. Click the Allow button.

Outlook – Credential Required
Outlook – Credential Required

Result

When the e-mail is received, the recipient’s e-mail client displays an indicator that the e-mail is digitally signed. Outlook’s indicator for digitally signed e-mail is a small ribbon. The recipient may then need to trust the certificate (public key) contained in your digital signature in order to respond with an encrypted message.

Outlook – Digital Signature Indicator
Outlook – Digital Signature Indicator

If you send a digitally signed e-mail from the e-mail address back to itself, you can respond to that e-mail with an encrypted message. Open the e-mail and click Reply. From the Options ribbon, click Encrypt. Add a response to the message body and click the Send button. When the response is received, Outlook displays a lock icon to indicate that the e-mail is encrypted. Outlook automatically handles the decryption when the e-mail is opened.

Outlook – Encryption Indicator
Outlook – Encryption Indicator

Part 4 – Establishing Recipient Trust

When a recipient receives a digitally signed e-mail where the sender used a self-signed S/MIME certificate and a personal certificate authority, the message is flagged by Outlook with the message “There are problems with the signature. Click the signature button for details.” The signature button is the yellow triangle with an exclamation point. As the sender, we added trust in Part 1 of this guide to the sending machine, however, the recipient machine does not recognize the certificate authority so the digital signature certificate is not trusted and flagged as invalid.

Outlook – Digital Signature is Invalid Warning
Outlook – Digital Signature is Invalid Warning

Step 1 – Invalid Digital Signature

Click the yellow triangle with an exclamation point icon. The Digital Signature: Invalid dialog box is displayed.

Outlook – Digital Signature Invalid
Outlook – Digital Signature Invalid

Step 2 – Message Security Properties (Invalid)

Click the Details… button. The Message Security Properties screen is displayed. There are many intimidating red circles with exclamation points.

Outlook – Message Security Properties (Invalid)
Outlook – Message Security Properties (Invalid)

Step 3 – Trust Certificate Authority

If available, click the Trust Certificate Authority… button. The Trust Certificate Authority screen is displayed. Click the Trust button. The Trust Certificate Authority screen closes. If the button is not active, proceed to the next step.

Outlook – Trust Certificate Authority
Outlook – Trust Certificate Authority

Step 4 – View Certificate and Edit Trust

Returning to the Message Security Properties screen, click the Edit Trust… button. The View Certificate screen is displayed. In the Edit Trust section, select Explicitly Trust this Certificate. Click the OK button.

Outlook – View Certificate
Outlook – View Certificate

Step 5 – Message Security Properties (Valid)

Returning to the Message Security Properties screen again, we find all the red circles have been replaced with green check marks. Click the Close button.

Outlook – Message Security Properties (Valid)
Outlook – Message Security Properties (Valid)

Step 6 – Valid and Trusted Digital Signature

The Digital Signature: Invalid dialog box is now the Digital Signature: Valid dialog box. Click the Close button. The digital signature is now trusted and flagged as valid.

Outlook – Digital Signature Valid
Outlook – Digital Signature Valid

Further Reading

Automate E-mail Information Extraction from Outlook into Excel Using VBA

In this scenario, assume that well-formatted e-mails containing information to be added to an Excel sheet are received. By using VBA to automate the data extract from Outlook directly into Excel, the data quality/accuracy is improved and it’s significantly faster over large volumes of e-mail vs. a human performing a copy/paste from each e-mail.

I’ll use a simple example to demonstrate the code. I receive account creation e-mails and I need to track the names and attributes associated with those accounts in an Excel worksheet. Each e-mail has the Subject “Template Email Subject” and the content follows the format “The account for <Full Name> has been created.” where <Full Name> represents the full name associated with the created account. The e-mail also contains a structured table with a few additional attributes. Using the sample e-mail below, the macro extracts the Full Name, e.g. John Dalesandro, and adds it to an Excel worksheet. The macro also extracts the additional information about the user such as Last Name, First Name, Name, Department, Company, and Job Title from the structured table.

This example will use two different methods to extract the information. The text-based extract is performed using a combination of REPLACE and SPLIT functions assuming there is common text surrounding the information to extract while the structured table data is extracted using HTML object library functions.

Sample E-mail Structure
Sample E-mail Structure

Outlook Configuration

In Outlook, I create a sub-folder under the default Inbox named “Automation” to collect the relevant e-mails. An Outlook rule may be used to automatically move the relevant e-mails into this folder. By having the e-mails located in a separate folder, it helps reduce the VBA search effort. This folder name “Automation” is used in the VBA code to reference the correct folder. The code assumes the folder exists under the default inbox. If the folder is created elsewhere, then the code would obviously need to be adjusted.

Outlook Folder Structure
Outlook Folder Structure

Enable Microsoft Outlook Object Library and Microsoft HTML Object Library

To use this function, you must enable both the Microsoft Outlook 16.0 Object Library which is needed to access Outlook and the Microsoft HTML Object Library which is used to extract HTML table based information. The version number will vary based on the installed version of Microsoft Excel.

  1. On the Developer ribbon, click Visual Basic
  2. Once the Visual Basic window opens, go to Tools -> References…
  3. From the References dialog box, check/enable:
    • Microsoft Outlook 16.0 Object Library
    • Microsoft HTML Object Library
  4. Click the OK button
Microsoft Excel Visual Basic Tools References
Microsoft Excel Visual Basic Tools References

Add Source Code to VBA Project

The code is added to “ThisWorkbook” within the VBA Project. For the example, I am using the default worksheet named “Sheet1” to capture the full name extract from the e-mails.

VBA Project Structure
VBA Project Structure

Run Macro

From the Developer ribbon, click Macros. The Macro dialog box opens and the ExtractDetailsFromOutlookFolderEmails procedure is listed. Click the Run button to execute the procedure.

Run VBA Macro in Excel
Run VBA Macro in Excel

Result

After executing the macro, the worksheet is populated with both the information extracted through the text-based method and the table-based method. This example only used one e-mail to test, but if multiple e-mails were in the Outlook folder, the sheet would reflect an additional row per e-mail.

Information Extract Result
Information Extract Result

Source Code

This code has been tested in Microsoft Office Professional Plus 2019. Again, there are some hardcoded values that would need to be changed, e.g. the structure of the e-mail template.

Using the example e-mail above, the following code connects to Outlook and obtains a reference to the folder named “Automation” in the default inbox.

It then loops through all of the items in the folder looking for any items with the phrase “Template Email Subject” in the Subject.

For the text-based information extract, the code takes the item’s Body and replaces the text phrases “The account for” and “has been created.” with “####################”. Replacing those phrases with a common delimiter makes it easier to extract the text found between the delimiters. As an example, the newly delimited Body would appear as follows after the replacing the phrases:

############## Dalesandro, John ##############

Now that the Body has been properly delimited, the Split function is used to extract the text found between the delimiters. Surrounding whitespace, line feeds, form feeds, and carriage returns are removed using through a combination of Trim and Replace functions.

The cleaned text is then written to “Sheet1” in Column A.

For the HTML table-based information extract, the code obtains a reference to the HTML table tag in the source data through the getElementsByTagName. The code then loops through the table rows and columns to pull all information in the second column. The first column is ignored since it contains information headers.

Option Explicit

Public Sub ExtractDetailsFromOutlookFolderEmails()
  On Error GoTo errorHandler
  
  Dim olApp As Outlook.Application
  Dim olNameSpace As Namespace
  Dim olFolder As Folder
  Dim olItem As Object
  Dim olItemBody As String
  Dim olHTML As MSHTML.HTMLDocument
  Dim olElementCollection As MSHTML.IHTMLElementCollection
  Dim htmlElementTable As IHTMLElement
  Dim iTableRow As Integer
  Dim iTableColumn As Integer
  Dim wksList As Worksheet
  Dim iRow As Integer
  Dim iColumn As Integer
  Dim strFullName As String
  
  Set wksList = ThisWorkbook.Sheets("Sheet1")
  
  Set olApp = CreateObject("Outlook.Application")
  Set olNameSpace = olApp.GetNamespace("MAPI")
  Set olFolder = olNameSpace.GetDefaultFolder(olFolderInbox).Folders("Automation")
  
  iRow = 1

  For Each olItem In olFolder.Items
    If InStr(olItem.Subject, "Template Email Subject") > 0 Then
      iColumn = 1
      
      'Begin extracting the full name from the e-mail body text:  The account for LAST NAME, FIRST NAME has been created.
      olItemBody = Replace(olItem.body, "The account for", "####################")
      olItemBody = Replace(olItemBody, "has been created.", "####################")
      
      strFullName = Trim(Replace(Replace(Replace(Split(olItemBody, "####################")(1), Chr(10), ""), Chr(12), ""), Chr(13), ""))
  
      wksList.Cells(iRow, iColumn) = strFullName
      'End full name extract.
      
      'Begin extracting table data.
      Set olHTML = New MSHTML.HTMLDocument

      olHTML.body.innerHTML = olItem.HTMLBody
      
      Set olElementCollection = olHTML.getElementsByTagName("table")
      
      For Each htmlElementTable In olElementCollection
        For iTableRow = 0 To htmlElementTable.Rows.Length - 1
          For iTableColumn = 0 To htmlElementTable.Rows(iTableRow).Cells.Length - 1
            On Error Resume Next
            'Only extract data from the second column in the table since the first column is a header.
            If iTableColumn Mod 2 = 1 Then
              iColumn = iColumn + 1
              wksList.Cells(iRow, iColumn).Value = htmlElementTable.Rows(iTableRow).Cells(iTableColumn).innerText
            End If
            On Error GoTo errorHandler
          Next iTableColumn
        Next iTableRow
      Next htmlElementTable
      'End extracting table data.
      
      iRow = iRow + 1
    End If
  Next olItem
  
errorHandler:
  If Err.Number <> 0 Then
    MsgBox "Error extracting details. [ExtractDetailsFromOutlookFolderEmails]"
  End If
End Sub

Retrieve Outlook Address Book Data Using Custom Excel VBA Function

I have an Excel worksheet containing thousands of rows of names. For each name, I need to add the individual’s job title as listed in the Outlook address book. I came across several solutions that would loop through the worksheet and add every available attribute from Outlook to the corresponding row. These solutions were all implemented as a sub procedure that operated across the entire worksheet. Instead of a sub procedure and looping, I implemented this as a generic function. This allows for more precision with respect to the attributes/properties to retrieve from Outlook and avoids operating on the entire worksheet when a name is added/changed.

The following custom VBA function will query the Outlook address book given a specific name/alias and return the requested attribute/property value.

To use this function, you must enable Microsoft Outlook 16.0 Object Library. The version number will vary based on the installed version of Microsoft Excel.

  1. On the Developer ribbon, click Visual Basic
  2. Once the Visual Basic window opens, go to Tools -> References…
  3. From the References dialog box, check/enable Microsoft Outlook 16.0 Object Library
  4. Click the OK button
Microsoft Excel Visual Basic Tools References - Microsoft Outlook Object Library
Microsoft Excel Visual Basic Tools References – Microsoft Outlook Object Library

Now that the Microsoft Outlook reference is activated, insert a new module into the workbook if one doesn’t already exist. The code will not work if added directly to a worksheet object.

Add Module to Microsoft Excel VBA Project
Add Module to Microsoft Excel VBA Project

Add the below code to the module. The function is then a usable formula in the workbook.

Usage

Once the code is properly added to the workbook, the function may be called as part of an Excel formula.

=GetOutlookAddressBookProperty(alias, propertyName)

alias accepts any string value, but the function works best if the value is a uniquely resolvable attribute of an address book entry (full name, alias name, alias, etc.). The function fails if it is unable to resolve to an individual entry in the address book.

propertyName accepts the following values:

  • Job Title
  • Company Name
  • Department
  • Name
  • First Name
  • Last Name

There are many other properties available as part of the ExchangeUser, however, I’ve only exposed a handful through this function. The code may be easily modified to include additional properties as needed.

Examples

Assume the Outlook address list includes an entry with the following attributes:

  • Last Name: Dalesandro
  • First Name: John
  • Name: Dalesandro, John
  • Department: Super Important Button Clickers
  • Company Name: Widgets International
  • Job Title: Supervisor
Example Outlook Address Book Entry Contact Information
Example Outlook Address Book Entry Contact Information
=GetOutlookAddressBookProperty("Dalesandro, John", "Job Title")

Returns the value “Supervisor”.

=GetOutlookAddressBookProperty("Dalesandro, John", "Last Name")

Returns the value “Dalesandro”.

VBA Function Results - Information Pulled from Outlook Address Book (Formula)
VBA Function Results – Information Pulled from Outlook Address Book (Formula)
VBA Function Results - Information Pulled from Outlook Address Book (Results)
VBA Function Results – Information Pulled from Outlook Address Book (Results)

Source Code

Option Explicit

Public Function GetOutlookAddressBookProperty(alias As String, propertyName As String) As Variant
  On Error GoTo errorHandler

  Dim olApp As Outlook.Application
  Dim olNameSpace As Namespace
  Dim olRecipient As Outlook.Recipient
  Dim olExchUser As Outlook.ExchangeUser
  Dim olContact As Outlook.AddressEntry

  Set olApp = CreateObject("Outlook.Application")
  Set olNameSpace = olApp.GetNamespace("MAPI")
  Set olRecipient = olNameSpace.CreateRecipient(LCase(Trim(alias)))

  olRecipient.Resolve

  If olRecipient.Resolved Then
    Set olExchUser = olRecipient.AddressEntry.GetExchangeUser

    If Not olExchUser Is Nothing Then
      'Attempt to extract information from Exchange
      GetOutlookAddressBookProperty = Switch(propertyName = "Job Title", olExchUser.JobTitle, _
                                             propertyName = "Company Name", olExchUser.CompanyName, _
                                             propertyName = "Department", olExchUser.Department, _
                                             propertyName = "Name", olExchUser.Name, _
                                             propertyName = "First Name", olExchUser.FirstName, _
                                             propertyName = "Last Name", olExchUser.LastName)
    Else
      'If Exchange not available, then attempt to extract information from local Contacts
      Set olContact = olRecipient.AddressEntry
      
      If Not olContact Is Nothing Then
        GetOutlookAddressBookProperty = Switch(propertyName = "Job Title", olContact.GetContact.JobTitle, _
                                               propertyName = "Company Name", olContact.GetContact.CompanyName, _
                                               propertyName = "Department", olContact.GetContact.Department, _
                                               propertyName = "Name", olContact.GetContact.FullName, _
                                               propertyName = "First Name", olContact.GetContact.FirstName, _
                                               propertyName = "Last Name", olContact.GetContact.LastName)
      Else
        GetOutlookAddressBookProperty = CVErr(xlErrNA)
      End If
    End If
  Else
    GetOutlookAddressBookProperty = CVErr(xlErrNA)
  End If

errorHandler:
  If Err.Number <> 0 Then
    GetOutlookAddressBookProperty = CVErr(xlErrNA)
  End If
End Function