A little bit of Excel Automation

Posted: 2023-10-05

Excel applications are the good, the bad and the ugly of trading or logistics process. Spreadsheets are great as a simple database with couple of formulas to act as a business logic, however what happens when a spreadsheet becomes integral part of the business process?

There is a reason for this and this reason is either to support a new business initiative or to implement workaround due to an existing system deficiency. The situation becomes very ugly very quickly. The new process means copying and pasting data into Excel and then copying and pasting into Outlook to communicate it to another group. Some of the copy and paste errors we have seen in our career were counted into hundreds of thousands of dollars.

There are couple of tricks that can help to fortify Excel worksheets:
- Lock the cells, such as only few input cells are editable - No overriding of formulas. For example, two trades are represented as two separate records rather than a single record with additional formula for combined quantity - Control sums with conditional formatting if values are outside of tolerance. For example, a scheduling spreadsheet can show supply and market for trades and storage and conditional formatting can flash warning in case of imbalance - Our favorite: Adding a bit of VBA to send an email via Outlook just by pressing a button

The last point is also a great time saver. Simply pressing a button opens up a new Outlook email with pre-populated content. This reduces number of copy-paste errors. Here how it works in practice:

Data input

Pre-populated email

Here is VBA for those who are interested in

Private Sub btnSendEmail_Click()
On Error GoTo ErrorHandler
    Dim EmailTo As String

    EmailTo = Me.Parent.Names("EmailTo").RefersToRange.Value
    Dim EmailSubject As String

    EmailSubject = Me.Parent.Names("EmailSubject").RefersToRange.Value
    Dim EmailBody As Range

    Set EmailBody = Me.Parent.Names("EmailBody").RefersToRange
    SendEmail EmailBody, EmailTo, EmailSubject
    Exit Sub

ErrorHandler:
    MsgBox "Got Error: " & Err.Description, vbExclamation
End Sub

Private Function SendEmail(rng As Range, eTo As String, eSubject As String)
    Dim htmlContent As String, i As Long, j As Long
    htmlContent = "<table>"

    For i = 1 To rng.Rows.Count
        htmlContent = htmlContent & "<tr>"
        For j = 1 To rng.Columns.Count
            htmlContent = htmlContent & "<td>" & rng.Cells(i, j).Value & "</td>"
        Next
        htmlContent = htmlContent & "</tr>"
    Next
    htmlContent = htmlContent & "</table>"

    Dim OutApp As Object, OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    With OutMail
        .To = eTo
        .Subject = eSubject
        .HTMLBody = htmlContent
        .Display
    End With
End Function