One of the tricks I use on reports is to create an email form letter, and then send it out. I’ve talked about this before and it’s a pretty simple feat to pull off. You just create your cool looking email, either in an HTML editor or MS Word and copy it into Outlook, or you can even just skip the middle man and create it in Outlook itself… complete with graphics and all the pretties (a while back I even showed how to incorporate Excel Spreadsheets or MS Project plans into them).
But it occurred to me that I’ve never actually talked about how to create a report – and auto generate the email output from this. This is a real time saver trick if you have to do regular status reports and you’re pulling the information from several sources because you can just create it in Excel, click a button… and have it pull data from an external source and then … wallah – instant status report for 30, 50 or 500 people, and it looks good every time.
First, you need to have something to autogenerate or autopopulate the HTML into Outlook with. You can do this from Excel, or any number of progams using a macro. There are dozens of these around, but this is probably one of the more useful ones I’ve found. In it, you’ll find the code to create an Outlook Email and attach files to it.
The code that I’ve been using on my JobHunter program – cheats a bit, as it already knows the email it’s replying to, so I can actually pull who I need to reply to and other information directly off the jobHunter form and input it into my emails, and this I do by creating ‘tags’ like HTML tags that I embed in my email. But you can do this with any Macro, so don’t feel like you need to create some advanced program or Outlook addin to do this.
Something simple like a Excel macro will do just fine. Since the code for one, is very similar to the other I’ll show you the code I’m currently using to generate the email you’re seeing here. Keep in mind, this code is part of a Outlook Add-In, so it’s hooking into functionality you don’t have access to. But the basics of this code can be used on any Macro since it’s pretty much VBA code.
Dim oMail As Outlook.MailItem Dim oFSO Dim oFS Dim newApp As New Outlook.Application Dim sText As String Dim sFname As String = Left(tb_recName.Text, InStr(1, tb_recName.Text, " ")) If newApp.Application.ActiveExplorer.Selection.Count Then If TypeOf newApp.Application.ActiveExplorer.Selection(1) Is Outlook.MailItem Then oMail = newApp.Application.ActiveExplorer.Selection(1).Reply oFSO = CreateObject("Scripting.FileSystemObject") oFS = oFSO.OpenTextFile("C:\Users\Robert\Documents\hello_new.html") sText = oFS.readall 'Here we get rid of those nasty extra characters that are in the HTML file sText = sText.Replace("ï»¿", "") sText = sText.Replace("â€™", "'") If sFname IsNot "" Then sText = sText.Replace("<replytoname>", Trim(sFname)) Else sText = sText.Replace("<replytoname>", "") End If oMail.To = tb_recEmail.Text oMail.Attachments.Add("C:\Users\Robert\Documents\RobertAJakobson.doc", Outlook.OlAttachmentType.olByValue, 0, "RobertAJakobson Resume") oMail.HTMLBody = sText & vbCr & oMail.HTMLBody oMail.Display() End If End If
Now, notice that the HTML file is brought in as text, and like any text, I can then use replace, either as with VB.net code, or the more common Replace("my text string", "what I’m trying to find", "thing I’m trying to replace") which is what you’d use in VBA with Macros.
You might also notice that I’m also using the .Attachments.Add to add my resume to the document (although you could add any file that you want to attach using this trick.
The end result is a visually appealing email.
Now, it’s not like there aren’t some downsides to these kind of emails. For one thing, the images need to be stored on a web server somewhere. When I design these, I do so with an HTML editor – for this I use Microsoft Expression Blend and I create the email first on a web server and view the web page from that server. So even though I may save the HTML file onto my hard drive for the script… I make sure all the links in it – are pointed to the web server’s image directory.
The reason why this is needed is because if the images can be seen via the internet, then you can see them from your email. Image pointers which point to your hard drive – viewers of the email can’t access because they have no access to your hard drive.
Another trick which makes the pages load vaster is to eliminate images completely and just use CSS or clean typography. By eliminating images, there’s no load time. And with a bit of careful planning it’s more than possible to make visually appealing emails without images.
The email shown here (left) has no images. Even the gradient text, is done with some crafty use of coloring with the FONT tag in HTML. Play around with things, you can do some amazing things and make yourself look very impressive.