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
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies