Domenic has a worksheet that shows due dates for projects in column E. He knows he can use conditional formatting to show when the due date is reached (when it is the same as today’s date), but what he really needs is an e-mail to be sent when the due date is reached. He wonders if there is a way to do this in Excel.
Actually, there is a way to do this, provided you don’t mind using a macro. In addition, you’ll need to send the e-mail via Outlook, which VBA will communicate with just fine. (Unfortunately, VBA cannot be easily used to connect with other mail clients.)
Here, for example, is a macro that will run whenever your workbook opens. It automatically checks each row in a worksheet, specifically keying on two things: the due date in column E and a “flag value” in column F. (This flag value is set by the macro. If column F contains the letter “S,” then the macro assumes an e-mail has previously been sent.)
Private Sub Workbook_Open() Dim OutApp As Object Dim OutMail As Object Dim lLastRow As Long Dim lRow As Long Dim sSendTo As String Dim sSendCC As String Dim sSendBCC As String Dim sSubject As String Dim sTemp As String Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon ' Change the following as needed sSendTo = "[email protected]" sSendCC = "" sSendBCC = "" sSubject = "Due date reached" lLastRow = Cells(Rows.Count, 3).End(xlUp).Row For lRow = 2 To lLastRow If Cells(lRow, 6) "S" Then If Cells(lRow, 5) "" Then .CC = sSendCC If sSendBCC > "" Then .BCC = sSendBCC .Subject = sSubject sTemp = "Hello!" & vbCrLf & vbCrLf sTemp = sTemp & "The due date has been reached " sTemp = sTemp & "for this project:" & vbCrLf & vbCrLf ' Assumes project name is in column B sTemp = sTemp & " " & Cells(lRow,2) sTemp = sTemp & "Please take the appropriate" sTemp = sTemp & "action." & vbCrLf & vbCrLf sTemp = sTemp & "Thank you!" & vbCrLf .Body = sTemp ' Change the following to .Send if you want to ' send the message without reviewing first .Display End With Set OutMail = Nothing Cells(lRow, 6) = "S" Cells(lRow, 7) = "E-mail sent on: " & Now() End If End If Next lRow Set OutApp = Nothing End Sub
When the macro runs (again, when the workbook is first opened), it checks each row in the worksheet to see if there is an “S” in column F. If not, then it checks to see if the date in column E is equal to today’s date. If it is, then the code puts together an e-mail message (which you can modify, as desired) to be sent. The e-mail is displayed, and you can click on the Send button after making any desired changes. At that point, the worksheet is updated by placing the “S” indicator in column F and the date the e-mail was sent into column G.
Note that the macro assumes that the name of the project is in column B. This information is used to put together the message that will be e-mailed.