Excel: Send outlook email based on cell content

Question

I would like to learn:

When it is 16 days past the date in a certain column(column D), how do we send an outlook email to the email address (format: name@abc.com) which is listed in another column C. eg. An email should be sent to the email address in C1 if the date is 16 days past the date in cell D1 and the cell E1 is blank(has no text).

Also, how can I set up the body of the email in a way such that I can insert a string into the body by taking the text in another column B. eg. the body of the email sent to the email address in cell C1 should contain the string of text in cell B1.

skkakkar skkakkar · Accepted Answer · 2019-02-22T05:17:07

Please add a Worksheet_Change event on the code page of your worksheet where you are having data as shown in your snapshot.Open VB Editor by pressing F11 key. Then you click on sheet, it will open up code page of the sheet. From the drop down select change and a routine will be added to the code page. Insert code in that routine.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 7 Then   'e.g. for column G
          Sendmail   'name of your sub
    End If

End Sub

Set up of our sheet is like the snap shot shown below.

I have considered following points.

There should be an alert only and system should not automatically send mails without review. Many times worksheet events give unexpected results as such caution is required here.
To provide latest status I have entered following formula in F2 cell which will be "YES" or "NO" depending upon date in D2 cell and E2 status.
For worksheet event to work properly a Cell in Column G has to be clicked. I have added a form control button in cell G2. When status is yes click the button in G2 to send the mail.
Now Insert a module in the workbook in VBE and put the code of Sendmail in that module.

Subject is in cell F1, body of message apart from general salutations is in cell B1, Name of the recipient is in cell C2

Sub Sendmail()
        ' Display a message when one of the designated cells has been
        ' changed.
        ' Place your code here.
Dim answer As String
Dim SubmitLink As String
Dim KeyCells As Range

 Set KeyCells = Range("F2:F100")
SubmitLink = Range("B1").Value

answer = MsgBox("Do you wish to save this change. An Email will be sent to the User", vbYesNo, "Save the change")

If answer = vbNo Then Cancel = True
If answer = vbYes Then

 Application.EnableEvents = False
 Application.ScreenUpdating = False

'open outlook type stuff
Set OutlookApp = CreateObject("Outlook.Application")
Set OlObjects = OutlookApp.GetNamespace("MAPI")
Set newmsg = OutlookApp.CreateItem(olMailItem)

On Error Resume Next

'add recipients
'newmsg.Recipients.Add ("Name Here")
newmsg.Recipients.Add Worksheets("Sheet1").Range("C2").Value
'add subject
newmsg.Subject = Worksheets("Sheet1").Range("F1").Value
'add body
newmsg.Body = "Dear Customer, " & SubmitLink & vbLf & vbLf & vbLf & " Look Forward to your confirmation" & vbLf & vbLf & vbLf & "Sincerely," & vbLf & "Customer Care department"

newmsg.Display 'display
newmsg.Send 'send message

'give conformation of sent message


 MsgBox "Modification confirmed", , "Confirmation"



End If
   '     MsgBox "Cell " & Target.Address & " has changed."
 On Error GoTo 0
 Set newmsg = Nothing 
 Set OutlookApp =Nothing

End Sub

EDIT : OP comments location of codes shown in snap shots

Excel: Send outlook email based on cell content

1 Answers