Value from Cell to Autofill Email

Edg38426 

Code could look like this. It will work best if Outlook is already running.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim objOL As Object
    Dim objMsg As Object
    ' Get out if multiple cells have been changed
    If Target.CountLarge > 1 Then Exit Sub
    ' Get out if the changed cell is in row 2 or not in column B
    If Target.Row = 1 Or Target.Column <> 2 Then Exit Sub
    ' Get out if the changed cell does not contain a date
    If Not IsDate(Target.Value) Then Exit Sub
    ' Get out if the cell in column D in the same row is empty
    If Target.Offset(0, 2).Value = "" Then Exit Sub
    ' Start Outlook
    Set objOL = CreateObject("Outlook.Application")
    ' Create a new message
    Set objMsg = objOL.CreateItem(0) ' olMailItem
    ' Recipient
    objMsg.To = "an email address"
    ' Subject
    objMsg.Subject = "This is the subject"
    ' Body text
    objMsg.Body = "Work order number " & Target.Offset(0, 2).Value & _
        " was completed on " & Format(Target.Value, "mm/dd/yyy")
    ' Use only ONE of the following two lines
    objMsg.Display ' show the message so that the user can inspect it
    objMsg.Send    ' send the message
End Sub