Sending an Email with Stata (Outlook Edition)

For Use with Windows

After about a month using Stata, most early programmers start asking questions about how to expand Stata's functionalities beyond generate and replace.  A common workplace question is: "how can I get Stata to send out automatic emails for me?"

A quick Google search yields many answers – most of which requires you to know a thing or two about email servers.  After hours of collecting material and trying to send out emails, you'll realize that your work's firewall disallows you to do anything of the sort!  Well, if you use Outlook (which is pretty common), you can easily automate sending emails using the following VBScript:

Set Outlook = CreateObject("Outlook.Application")
Set Mail = Outlook.CreateItem(0)
Mail.To = "[email protected]"
Mail.Subject = "Automated Email"
Mail.Body = "Hello Recipient"
Mail.Send
Set Mail = Nothing
Set Outlook = Nothing

​
​But how do we send this out?  It's really quite simple.  Open up any text editor (though I recommend using Notepad++), copy this code into the text editor, and save this file as "whatever.vbs", and either double click the file or run it by using Stata's shell command.

yourdirectory + "whatever.vbs"

CRLF

​This is great for simple emails, but what if we want to make our body a little more complex and less "robotic?"  Let me (re)introduce you to ASCII characters 10 and 13.  Since we're dealing with Windows for this post, let's switch them around and show char(13)+char(10) which corresponds to \r\n or translated: carriage return + line feed (CRLF).  What the heck is a carriage you say?  (Likely if you're under 50)  Well, it all relates back to typewriters.

​The carriage is the tray thing that moves along as you type, and by asking it to "return", you usually will get it shooting back with that distinctive PING sound.  Line feed tells the typewriter to move the paper up one line.  A carriage return without a line feed would overwrite anything you've typed, and a line feed without a carriage return could start a newline in the middle of a page.  Of course, things don't work that way anymore, but hopefully that makes a little more sense!

Anyway, this combination is useful to you if you want to send multiple paragraphs in an email.  Even more useful, write out your email body in a text file and let Stata pick up the file for you, and write the body by itself.  But more on that in a little bit.  For now, let’s talk about writing the actual script.

In Stata, in order to write a script, we must first familiarize ourselves with the file processing commands: file open, file write, and file close.  The secret here is that we can use locals to fill out any missing information in the file such as an email recipient, a subject line, or an attachment location.  Boom!  We have a program.

program define outlook_email
        syntax , to(string) [subject(string) body(string) attachment(string)]
end

​Since we always need a recipient for an email "to" is a must, but everything else should remain optional.  We want to allow our users to do whatever they want, which might include sending out 10,000 blank emails to a specific person.  Why?  They probably have their reasons.

File Processing

Want to write a file using Stata?  It's very easy to write the VBScript which runs the entire process.  For those who are unfamiliar with the file command, it follows three basic steps: open that file (even if it doesn't exist yet), write stuff to that file, and close that file!  Let's take a look at some basic syntax below:

The blue denotes these steps, the green is the name that we gave our program to point to which file to write to, and the red denotes the options.  Notice that our green "name" is myfile, but it could be anything, yet our physical file name is "testfile.txt".  Think of myfile as an id for which file we're writing to (or reading from).

Notice that we just need to put a string followed by _n to write a single line to the file.  We then do that again for line two, and for lines three and four.  We're able to write lines three and four on the same line, because they contain that _n which calls for an end of line character (in this case CRLF) to be written.

The Body

There are four steps to a making a text file work with VBS:

1. Get rid of quotes: from " to "+Chr(34)+"
   
2. Get rid of apostrophes: from ' to "+Chr(39)+"
   
3. Replace CRL characters: from \013d\010d to "+ Chr(13)+Chr(10)+"
   
4. Get rid of useless stuff: from +""+ to +

​You’ll see that we use filefilter to take care of these four scenarios, creating a single string that contains the entire body of our email (line breaks included).  Putting all of this together, we arrive at the final code:

Final Program

********************************************************************************
* Will Matsuoka: 2015-12-17 version 2.0.0 - for demonstrative purposes
********************************************************************************

program define outlook_email
        syntax , to(string) [subject(string) body(string) attachment(string)]
        
* Allow for the subject to contain quotes and apostrophes
        if `"`subject'"' != "" {
                sta_2_vbs `"`subject'"'
                local subject = r(vbstring)
        }
        
        if `"`body'"' != "" {
                preserve
                tempfile f1 f2
                filefilter "`body'" `f1', from(`"""') to(`""+Chr(34)+""')
                filefilter `f1' `f2', from(`"'"') to(`""+Chr(39)+""')
                filefilter `f2' `f1', from(\013d\010d) to(`""+Chr(13)+Chr(10)+""') replace
                filefilter `f1' `f2', from(`"+""+"') to("+") replace
                
                clear
                set obs 1
                gen file = fileread("`f2'")
                local body = file[1]
                restore
        }
        
        tempname myfile
        file open `myfile' using sendemail.vbs, write replace
        file write `myfile' ///
                `"Set Outlook = CreateObject("Outlook.Application")"' _n ///
                `"Set Mail = Outlook.CreateItem(0)"' _n ///
                `"Mail.To = "`to'""' _n ///
                `"Mail.Subject = "`subject'""' _n ///
                `"Mail.Body = "`body'""' _n
        if "`attachment'" != "" {
                file write `myfile' `"Mail.Attachments.Add("`attachment'")"' _n
        }
        file write `myfile' ///
                `"Mail.Send"' _n ///
                `"Set Mail = Nothing"' _n ///
                `"Set Outlook = Nothing"' _n
        file close `myfile'
        
        shell sendemail.vbs
end

program define sta_2_vbs, rclass
        local string = subinstr(`"`1'"', `"""', `""+Chr(34)+""', .)
        local string = subinstr(`"`string'"', `"'"', `""+Chr(39)+""', .)
        local string = subinstr(`"`string'"', `"+""+"', "+", .)
        return local vbstring `"`string'"'
end

​Not too terrible, although I do have a few things to note:

• For troubleshooting, if your VBScript works, your Stata script should work - in other words, start at your VBScript
• There are times where it helps to keep Outlook open, to make sure that your email doesn't just sit in the outbox
• If you're trying to run this in batch mode, omit the shell command.  Just call the script on the next line of your batch file (since shell doesn't work in batch mode)