How to Automatically Send Emails from Excel Based on Cell Content

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.

Our Solution

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

End If

End If

End If

End Sub

Summary

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.

Yoav Ezer co-authors Codswallop, a technology and productivity blog. He is also the CEO of a software company that produces PDF to XLS conversion software.

4 Responses to How to Automatically Send Emails from Excel Based on Cell Content

  1. Bekkah20 says:

    This is a great function of Excel!  I was wondering whether this code can be modified to work with dates rather than simple numbers as well.

    I’ll provide a specific example.  Let’s say I’m tracking service on cars.  In one column is the car purchase date.  In the next column is a calculation of the date for yearly service (value in column 1+365).  I want to create a warning email that generates when the date in the column for yearly service is less than the value of the current date.

    I’ve tried playing with the code in your current example but it doesn’t seem as though the MACRO recognizes the date values.  This could be my ignorance as a very limited programmer so I wanted to check with the experts.

    Any help would be greatly appreciated!

  2. Janio Echevarria says:

    this is great stuff, thank you.
    This code helped me a lot of my projects

  3. Steve says:

    Hi! Thanks for the file. How can I change it so that it automatically send the email without the interaction? Thanks

  4. Ulf says:

    It is also possible to send e-mails from Excel using the Outlook COM library. You can find some usefull information on this page:
    http://www.lazerwire.com/2011/10/excel-vba-send-e-mail-from-excel.html

Leave a Reply

Videos, Slideshows and Podcasts by Cincopa Wordpress Plugin