Outlook VBA macro to post email body to the web
At the request of one of my managers, I looked into the possibility of using Outlook to automatically post the contents of an incoming email to a website (an internal group wiki in this case). We previously used procmail to accomplish this task, but now that all email has migrated to Outlook/Exchange, that’s no longer an option. It turns out this is pretty easy to accomplish using the VisualBasic for Applications (VBA) macro capabilities built-in to Outlook.
The basic pieces to make this work:
- A Rule in Outlook that matches the specific email criteria, and runs a script (Outlook Macro)
- The Outlook macro which pulls out the message body and posts to a web site
- The web site / CGI to handle the incoming message
Setup the Outlook Rule
The first part is the easiest – define a rule with filters to match specific messages that should be forwarded onto the web site. The filter criteria should be as specific as possible — not generally a good idea to be posting arbitrary email messages onto a website. The action should be “run a script”. The ’script’ has to be a macro with a specific signature, like this:
Sub PublishMsgToWeb(Msg As Outlook.MailItem)
That defines a macro specifically intended to handle Outlook message (Outlook.MailItem instances).
Define the macro to post to the web
The macro is pretty simple: ready the message body and any other data of interest from the Outlook.MailItem object, package the data up into an HTTP POST request, and send it off.
Sub PublishMsgToWeb(Msg As Outlook.MailItem)
Dim URL As String
URL = "http://example.com/post-msg-from-outlook.cgi"
Dim Subj As String, Body As String
Subj = Msg.Subject
Body = Msg.HTMLBody
PublishMsgToURL URL, Subj, Body
End Sub
Sub PublishMsgToURL(ByVal URL As String, Subject As String, Body As String)
Set xhr = CreateObject("MSXML2.XMLHTTP")
xhr.Open "POST", URL, False
xhr.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
data = "subj=" & URLEncode(Subject) & "&" & "body=" & URLEncode(Body)
xhr.Send data
End Sub
I split the macro into two different pieces for testability. The HTTP POST request is done by instantiating an MSXML2.XMLHTTP request. Web services in Outlook seems to be a bit of a muddy subject, with multiple flavors of XmlHttpRequest. MSXML2.XMLHTTP is what I found to work in my version of Outlook 2003. YMMV.
The only real trick to putting the request together is in encoding the data as “application/x-www-form-urlencoded”, the format typically used for POST requests. I used the URLEncode() VBA function to encoded each piece, then joined them together into a normal query string
subject=…&body=…&…
This example just sends the message subject and body. Obviously there has to be an agreeement between the macro and the receiving website as to what data the request body should contain. Date, sender, and others are readily accessible from the Outlook.MailItem.
Define the CGI on the website to receive the message
For the purpose of this example, I just put together a simple CGI script in Ruby:
#!/usr/env/bin ruby
require 'cgi'
cgi = CGI.new
subj = cgi['subj']
body = cgi['body']
File.open("msg.html",'w') do |fh|
fh << <<END
<html>
<head>
<title>Outlook msg: #{subj}</title>
</head>
<body>
<h1>#{subj}</h1>
#{body}
</body>
</html>
END
end
print cgi.header( "type" => 'text/plain' )
print "ok\n"
This just saves the message content to a static html file that can then be viewed in a browser. The script just returns a simple ‘ok’ message back to the caller, but that will not actually be seen by anyone since the Outlook macro handles this transaction behind the scene.
So there’s the basic framework for posting email to the web using the built-in Outlook rules and macro capabilities. Whether or not this is actually a good idea is another question altogether…
