Sending business email: Automate the boring stuff with R

I spoke at the December LondonR meetup, run by Mango Solutions, about automating work with R. I covered a few of the jobs I’ve handed over to R, but most of the talk was about one of them: getting R to send email through Outlook. This post is how I do it, and the slides are a 3 MB PDF.

Why email

Heathrow’s front-of-house team is 120 people across four terminals, speaking 35 languages between them. They help passengers with their shopping, and every sale they help with is reported by hand. A sale goes from the shop floor, to a form, to a database, to a report, and the typed-in step is where the errors get in.

So every morning R pulls yesterday’s figures and emails each terminal’s team a summary: the day’s assisted sales, the shift average, and a row for each person on shift with their hours, their sales and a comments column flagging anything that needs fixing.

Once a week everyone also gets an email of their own: how they did, the shifts they worked, their shift average, and where they sit in a league table of the whole team. That last part has turned out to be the most motivating thing in it.

None of this was tedious work I’d automated away. It was work nobody could have done at all. No one is going to write 120 personal emails every week by hand.

Getting R to talk to Outlook

Sending email from R is harder than it should be. Jenny Bryan’s send-email-with-r covers Gmail, but at work everything goes through Outlook.

The answer was the RDCOMClient package from the Omega Project for Statistical Computing. It lets R act as a component object model (COM) client, which means it can drive Outlook the same way Outlook’s own macros do. It needs no administrator rights, and it takes a handful of lines.

library(tidyverse)
library(htmlTable)
# install.packages("RDCOMClient", repos = "http://www.omegahat.net/R")
library(RDCOMClient)

COM is Microsoft’s standard for letting one program call another. Microsoft has written up most of what its software can do as COM objects, so anything that can act as a COM client can use it. Microsoft’s documentation has the detail.

Start Outlook as a COM object:

OutApp <- COMCreate("Outlook.Application")

From here R can call Outlook’s methods with $ and read or set its properties with [[. First, create an email:

outMail <- OutApp$CreateItem(0)  # 0 = olMailItem

Then fill it in, attach anything you need, and send it:

outMail[["To"]] <- "client@somecompany.com"
outMail[["Subject"]] <- "Hello"
outMail[["Body"]] <- "Is it me you're looking for?"
outMail[["Attachments"]]$Add("C:/Users/somepath/Admin.html")

outMail$Send()

That’s it. If you’d rather look at it before it goes, outMail$Display() opens the email in Outlook instead of sending it.

Finding out what Outlook can do

The hard part is knowing which methods and properties exist. Outlook’s own VBA editor has an Object Browser that lists every class and its members. MailItem alone has dozens: DeferredDeliveryTime, for one, holds an email back until a set time.

One email per person

A single email is a party trick. The point is sending a different one to each person. Wrap the steps above in a function, set HTMLBody instead of Body, and build each table with htmlTable():

send_email <- function(to, subject, html) {
  outMail <- OutApp$CreateItem(0)
  outMail[["To"]] <- to
  outMail[["Subject"]] <- subject
  outMail[["HTMLBody"]] <- html
  outMail$Send()
}

Then give it a data frame with one row per person, holding to, subject and html columns, and send the lot with pwalk(emails, send_email).

From there you can add your HTML email signature, style the tables with CSS, or send a whole R Markdown report in the body. Building fancy-pants HTML emails is a post of its own.

The second half of the talk was about a formal monthly report that R now writes as a PDF. That one meant learning some TeX: front pages, landscape pages, and wrapping text around images.