Home

Automatic emails through excel based on new data

%3CLINGO-SUB%20id%3D%22lingo-sub-644126%22%20slang%3D%22en-US%22%3EAutomatic%20emails%20through%20excel%20based%20on%20new%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644126%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%20%3A)%3C%2FP%3E%3CP%3EI'm%20attempting%20to%20create%20an%20excel%20worksheet%20that%20automatically%20emails%20an%20individual%20with%20reminders%20of%20approaching%20deadlines.%20I've%20created%20a%20date%20calculator-%20an%20employee%20enters%20in%20a%20'submission%20date'%2C%20then%20the%20calculator%20provides%26nbsp%3Ba%20'draft%20deadline%20date'-%20from%20here%20I%20want%20there%20to%20be%20an%20automatic%20email%20sent%20to%20one%20individual%20every%20time%20the%20calculator%20provides%20a%20draft%20deadline%20date%20in%20the%20column.%3C%2FP%3E%3CP%3EIs%20this%20possible%3F%3C%2FP%3E%3CP%3EThank%20you!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-644126%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-644155%22%20slang%3D%22en-US%22%3ERe%3A%20Automatic%20emails%20through%20excel%20based%20on%20new%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644155%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F348251%22%20target%3D%22_blank%22%3E%40joce1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi.%3C%2FP%3E%3CP%3Eyou%20can%20send%20eMail%20automatically%20from%20Excel.%20the%20following%20example%20fires%20an%20e-Mail%20if%20anyone%20enter%20cells%20.A1%3AA10%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3CBR%20%2F%3EDim%20objOut%20As%20Object%3CBR%20%2F%3EDim%20objOutMail%20As%20Object%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20Not%20Intersect(Range(%22A1%3AA10%22)%2C%20Target)%20Is%20Nothing%20Then%3CBR%20%2F%3EIf%20Target.Value%20%26lt%3B%26gt%3B%20%22%22%20Then%3CBR%20%2F%3E%3CBR%20%2F%3ESet%20objOut%20%3D%20CreateObject(%22Outlook.Application%22)%3CBR%20%2F%3ESet%20objOutMail%20%3D%20objOut.createitem(0)%3CBR%20%2F%3EWith%20objOutMail%3CBR%20%2F%3E.Subject%20%3D%20%22Change%20by%20%22%20%26amp%3B%20Environ(%22username%22)%3CBR%20%2F%3E.body%20%3D%20%22chance%20in%20cell%3A%26nbsp%3B%20%22%20%26amp%3B%20Target.Address%20%26amp%3B%20vbLf%20%26amp%3B%20_%3CBR%20%2F%3E%22New%20value%3A%20%22%20%26amp%3B%20Target.Value%3CBR%20%2F%3E.to%20%3D%20%22b.held%40Held-office.de%22%3CBR%20%2F%3E.send%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3ESet%20objOutMail%20%3D%20Nothing%3CBR%20%2F%3EobjOut.Quit%3CBR%20%2F%3ESet%20objOut%20%3D%20Nothing%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3EBernd%3C%2FP%3E%3CP%3E%3CA%20href%3D%22http%3A%2F%2Fwww.vba-tanker.com%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.vba-tanker.com%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
joce1
Occasional Visitor

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
Highlighted

@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

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies