Sometimes, it is useful or needed to send an email outside office hours (a market snapshot, settlement prices,…). Staying in the office just for the sake of sending this particular email can be a lack of productivity, therefore, automating the process is really welcomed. I even know some people who use this method to make believe they are still in the office… Anyway, it is pretty easy to achieve ant it has become a very needed and popular tool among users of VBA and Excel all over the web.

For example, Ron de Bruin (Excel MVP) explains very well how it’s achievable, taking a look at his website really worth it in general. And for the particular matter, not only he’s explaining how to send the mail automatically but he’s showing the excellent RangeToHTML function.

Using the Excel Object Model to Send Workbooks and Ranges through E-Mail with Outlook (Part 1 of 2)

Using the Excel Object Model to Send Workbooks and Ranges through E-Mail with Outlook (Part 2 of 2)

Ron de Bruin website

So, let’s face it, it is pretty easy to send a simple email:

Sub EmlSnd()

Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

With OutMail
     .Subject = "Subject of the email"
     .body = "Body of the email"
End With

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

.CC, .BCC, .SentOnBehalfOf,  .Send.Attachments.Add can be added in the With statement

But the whole point is to send Excel data as if we were doing a copy and paste in an email to keep the formatting. The RangeToHTML function is coming into play there. If I managed, in the past, creating by myself the above routine of sending automatically an email I have to admit I give back the full credit to the RangeToHTML owner (Ron de Bruin I believe).

It basically copy and paste the range on a new workbook, keeping the format, save it as HTML and the HTML string will be ready to be plugged as HTML body. Very clever.

Function RangetoHTML(rng As Range)

'Variable declaration
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook

'Creating a temporary Excel file
    TempFile = Environ$("temp") & "" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

'The selected range is copied and paste in the Excel file created above
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial xlPasteValues, , False, False
        .Cells(1).PasteSpecial xlPasteFormats, , False, False
        Application.CutCopyMode = False
        On Error Resume Next
        .DrawingObjects.Visible = True
        On Error GoTo 0
    End With

'Publish the sheet to a htm file
    With TempWB.PublishObjects.Add( _
         SourceType:=xlSourceRange, _
         Filename:=TempFile, _
         Sheet:=TempWB.Sheets(1).Name, _
         Source:=TempWB.Sheets(1).UsedRange.Address, HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With

'Read all data from the htm file into RangetoHTML
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.readall
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", "align=left x:publishsource=")

'Close TempWB
    TempWB.Close savechanges:=False

'Delete the htm file we used in this function
    Kill TempFile

    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
End Function

The code need to be copied in a module.

Then, finally, the macro to send a bit of Excel sexy data becomes:

Sub MailSnap()

Dim OutApp As Object
Dim OutMail As Object
Dim rng As Range
Dim StrBody As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

Set rng = ThisWorkbook.Sheets(1).Range("D5:L123").SpecialCells(xlCellTypeVisible)

With OutMail
     .BCC = ThisWorkbook.Sheets(2).Range("E6")
     .Subject = ThisWorkbook.Sheets(2).Range("E7")
     .HTMLbody = RangetoHTML(rng)
End With

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

Why do I use BCC only? Common sense… Confidentiality, then, it avoids one of the recipients to reply to all to shame you if it was because of a bug or to shame himself as it happened already to everyone! Who hasn’t sent a personal email with reply to all instead of reply to sender?

If I stopped there, one could think: “what is the point of another article on the matter?”. True. But there is only one problem with a such code. It has to be on a Workbook_Open routine, or on a timer routine (the macro is called by another macro at a specific time), or using the Wait method. This is a problem for us in the financial industry.

If we have to wait a certain time to send the email, it might be because we’re awaiting for some data to be populated, like Bloomberg, Reuters, DataStream,… And the problem with their add-in is… Well, they are add-ins! So when a macro is running, their add-in is stopping running. So a two hours Wait method in the macro will prevent from updating the dynamic data. A macro automatically launched on the workbook opening will prevent as well the dynamic data to be updated before the actual execution of the macro. There might be a method but hours of research have been unsuccessful. The alternative method would be to retrieve data directly from VBA rather than in Excel, but this is much less user friendly to my mind and providers are not really keen on giving support on VBA as it’s not their proprietary system.

Another problem most of us have to cope with is the rights on the computer. Indeed, most of the time we need this for work, not for personal leisure! Quite often, IT services are not giving the admin right on the user’s machine. I know for a fact some scheduler exist and enable to open an Excel file, wait a particular amount of (free) time and then launch a macro and that works perfectly: well, if you can download and install the software!

So what can we play with? The Windows Task Scheduler cannot be used as such as it cannot open an Excel workbook. Yes but it can open a script VBS! The VBS Script could open the Excel workbook and then launch a macro. However, that doesn’t change our problem of data to be updated but this is the main hope!

I finally figured out a stable shortcut. When you leave the office, leave the workbook open and just lock the computer (check your power saving settings, you don’t want the computer to go to sleep mode). Your Excel and add-in are still working and updating in the background.

The Windows Task Scheduler will call the VBS Script at a certain time. The VBS script will simply lauch the macro of the file already opened! A tiny two lines script is solving a big problem for us!

Set xl = GetObject(, "Excel.Application")  
xl.Run "'MarketSnapshot.xlsm'!MailSnap"

I wouldn’t tempt the fate of having two different instances of Excel opened. The VB Script might be confused on which instance to work on! But it can be tried.

So it is now easy to send email with futures settlement prices of the Chicago Board of Trade in the middle of the night if you’re based in the east of Chicago!

That said, there is a potential problem. A disconnection of the data feed might occur at night. Indeed, they don’t like system running at night because some might use it in order to feed a software while providers actually sell separate licences directly plugged in third part software to feed prices… Reuters disconnection is happening every Sunday night I believe for example. The re-connection to the feed might be just a VBS script away, it might be the subject of another post!

But to prevent sending incorrect data, a simple check can be done. The last update of EURUSD (for example) should not be really far from the date and time of the computer form Monday to Friday. So just a little check the difference of time is acceptable before launching the macro wil solve the issue (a simple If Then End If statement).