In a recent article we demonstrated how you can send email from Microsoft Excel, and how useful this could be. We were asked if it was possible for the spreadsheet to “react” to the data, triggering an email based on the contents.
We thought this was a great example and took up the challenge! The example we decided to use is of an inventory tracking spreadsheet that sends an email automatically if the quantity falls beneath a certain threshold.
If you click here you will be able to download our simple Inventory tracking sheet.
Open it up and change some of the values. Our email is triggered in the ‘change’ event.
How it Works
We check whether the quantity falls below the minimum quantity for reordering, and if it does we ask the user if he wants to order a new batch of the item. If the user answers yes, unlike in previous examples where we use the MAPI object, we simply use the mailto: windows function to open the default mail client and put the details of the order in the subject line, allowing the user to customize the body of the email as required.
You will notice you can not simply view the macro. To see the code, right-click the worksheet tab and hit ‘View Code’. This takes you to see the routines we added.
The mailto: link is launched using a function called ShellExecute, seen below, which integrates with shell32.dll and allows us to send command strings to Windows.
Private Declare Function ShellExecute Lib “shell32.dll” _
Alias “ShellExecuteA” (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal nShowCmd As Long) As Long
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
The rest of the code looks for the change in our sheet contents and checks the values. If necessary the message box is displayed and the email is launched, supplying the subject line as appropriate.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim lngResponse As Long
Dim URL As String, strEmail As String, strSubject As String
If Left(Target.Address, 2) = “$D” Then
If Target.Value < Range(“$E” & Right(Target.Address, 2)).Value Then
lngResponse = MsgBox(“The inventory quantity for this item has dropped below the reorder level. Would you like to reorder this item?”, vbYesNo)
If lngResponse = vbYes Then
strEmail = Range(“$H” & Right(Target.Address, 2)).Value
strSubject = “New order for ” & Range(“$F” & Right(Target.Address, 2)).Value & ” ‘” & Range(“$B” & Right(Target.Address, 2)).Value & “‘ items”
strSubject = Application.WorksheetFunction.Substitute(strSubject, ” “, “%20″)
strURL = “mailto:” & strEmail & “?subject=” & strSubject
ShellExecute 0&, vbNullString, strURL, vbNullString, vbNullString, vbNormalFocus
This simple exercise is a very good “workflow” example of how a little programming can streamline work processes and make them far more efficient. Also, by taking out some decision element but maintaining the human interaction where it is required, it demonstrates a good balance of automation and manual process. As mentioned earlier, we could have emailed right from Excel, but this shows an alternative approach that would allow you to use your Outlook/Outlook Express client instead.