Automatic emails through excel based on new data

Copper Contributor

Hi everyone :)

I'm attempting to create an excel worksheet that automatically emails an individual with reminders of approaching deadlines. I've created a date calculator- an employee enters in a 'submission date', then the calculator provides a 'draft deadline date'- from here I want there to be an automatic email sent to one individual every time the calculator provides a draft deadline date in the column.

Is this possible?

Thank you!!!

1 Reply

@joce1 

Hi.

you can send eMail automatically from Excel. the following example fires an e-Mail if anyone enter cells .A1:A10

 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim objOut As Object
Dim objOutMail As Object

If Not Intersect(Range("A1:A10"), Target) Is Nothing Then
If Target.Value <> "" Then

Set objOut = CreateObject("Outlook.Application")
Set objOutMail = objOut.createitem(0)
With objOutMail
.Subject = "Change by " & Environ("username")
.body = "chance in cell:  " & Target.Address & vbLf & _
"New value: " & Target.Value
.to = "b.held@Held-office.de"
.send
End With
Set objOutMail = Nothing
objOut.Quit
Set objOut = Nothing
End If
End If

End Sub

 

Regards

Bernd

www.vba-tanker.com