Good day,
please see attached document, I want Excel to send an automatic email to each rep for each line that has 999 error code (in Yellow), please help...
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Auto Emails from Excel for Specific values
0
Discussion
What do you mean by "send an automatic email" please? If you're familiar with VBA, Excel could send a single email when an error code cell goes to 999 OR you could add a button which causes an email to be sent for all lines currently showing error code 999.
Either way, my personal preference would be for Excel to populate the email(s) but you to press Send (since the email will be in your name).
Also, which email client are you using?
Either way, my personal preference would be for Excel to populate the email(s) but you to press Send (since the email will be in your name).
Also, which email client are you using?
John_Ru (rep: 6142) Nov 9, '20 at 10:04 am
Ron de Bruin has published VBA code to send emails from Excel (or Excel from by email). Google for his name and you will find it. His variations are rather exhaustive but if you want something more special that's still what you have to start out from.
Variatus (rep: 4889) Nov 9, '20 at 6:43 pm
hi,
so what i mean is that if you open the report i attached, you will see some lines is 999 under Act DSC, I need to send an email to that specific sales rep that looks like:
"Good day,
please see below line with SOH and nos Sales:
Sales Rep Site Store Name Vendor Vendor Name Article Article Desc Class Order Unit UOM 06-2020 07-2020 08-2020 09-2020 10-2020 11-2020 11-2019 Curr Y/S SOH SOO SIT PR Qty Act DSC Last Recv Last Sold Lorenzo M06 Ottery 10387 SGX SALES (PTY) LTD 379572001 NANDOS PERI PERI SAUCE 500G, HOT NEW 6 EA 0 24 30 12 0 0 42 253 0 0 0 0 999 28.08.2020 13.09.2020
thank you kindly,
kind regards,
Yolandi"
but I want excel to send this email for me every time there is a 999 in that column
i know this is possible but not sure how the code needs to look as I am not advanced enough to know the coding on Excel.
so what i mean is that if you open the report i attached, you will see some lines is 999 under Act DSC, I need to send an email to that specific sales rep that looks like:
"Good day,
please see below line with SOH and nos Sales:
Sales Rep Site Store Name Vendor Vendor Name Article Article Desc Class Order Unit UOM 06-2020 07-2020 08-2020 09-2020 10-2020 11-2020 11-2019 Curr Y/S SOH SOO SIT PR Qty Act DSC Last Recv Last Sold Lorenzo M06 Ottery 10387 SGX SALES (PTY) LTD 379572001 NANDOS PERI PERI SAUCE 500G, HOT NEW 6 EA 0 24 30 12 0 0 42 253 0 0 0 0 999 28.08.2020 13.09.2020
thank you kindly,
kind regards,
Yolandi"
but I want excel to send this email for me every time there is a 999 in that column
i know this is possible but not sure how the code needs to look as I am not advanced enough to know the coding on Excel.
Yolanidvdberg (rep: 4) Nov 10, '20 at 3:48 am
Yolani
To my question, you "want excel to send this email for me every time there is a 999 in that column" but "every time" can still mean either as a batch (i.e. you open the report and click a button which sends an email for every line where 999 appears under Act DSC at that moment OR an single email is sent whenever a given cell actually changes to 999. I suspect you mean (and would be better with) the batch approach
Suggest you follow the advice from Variatus on seeking the code from Ron de Bruin and come back if you struggle to unnderstand or impelement it.
To my question, you "want excel to send this email for me every time there is a 999 in that column" but "every time" can still mean either as a batch (i.e. you open the report and click a button which sends an email for every line where 999 appears under Act DSC at that moment OR an single email is sent whenever a given cell actually changes to 999. I suspect you mean (and would be better with) the batch approach
Suggest you follow the advice from Variatus on seeking the code from Ron de Bruin and come back if you struggle to unnderstand or impelement it.
John_Ru (rep: 6142) Nov 10, '20 at 4:21 am
hi, so I wil be replacing the data every week and when replaced it needs to send a batch mail
Yolanidvdberg (rep: 4) Nov 10, '20 at 4:32 am
Thanks for the clarification.
Did you find and follow the VBA code published elsewhere by Ron de Bruin?
Did you find and follow the VBA code published elsewhere by Ron de Bruin?
John_Ru (rep: 6142) Nov 10, '20 at 5:21 am
hi, I found a few but I dont quite understand them so not sure which one would work, its a bit advanced for me
Yolanidvdberg (rep: 4) Nov 10, '20 at 6:04 am
See a draft answer I've posted (to be amended after your response).
John_Ru (rep: 6142) Nov 10, '20 at 6:49 am
Answers
0
Selected Answer
Yolani
Further to the discussion above, please try the attached file (which uses some code from Ron De Bruin and assumes you use Outlook as your email client). The file includes a macro so (on opening) you'll need to enable macros before it will work.
You'll see a green button "Send 999 emails" (for now somewhat inconveniently placed near top left of the Report sheet- it can be moved). Click that and the current version should create but not send (though you can) a single email related to the third line of your Report sheet. Check it does what you expect, albeit the product stock data is after the email body (from cell AC3).
If that works well for you, it can be extended to produce emails for all 999 cells from a single click.
Discussion
Hi, this is exactly what i was looking for, can you extend it to the entire report sheet, also can you move the Send 99 Emails to a new Tab?
thank you so very much though :)
thank you so very much though :)
Yolanidvdberg (rep: 4) Nov 11, '20 at 12:26 am
Yolandi
I'm glad that "...this is exactly what i was looking for" but key contributor @Variatus has pointed out separately yesterday that the purpose of this site is to teach Excel, not to provide homework answers or indeed business solutions / consulting on Excel (this is a separate chargeable service provided by Don I believe).
Hopefully my workbook...v0_b.xlsm has given you a glimpse of the power of VBA and you're keen to learn more of VBA. This a great site to start!
I suggest you look in the "Macros and VBA in Excel" section under Tutorials menu and begin with this lesson Excel Macros Class 1 - Getting Started Programming Macros followed by the other 5 classes. The tutorial Get Data from the Worksheet into a Macro in Excel might also be useful.
That should enable you to extend my ...v0_b.xlsm workbook to meet your needs (and alter the code when your needs change).
John_Ru (rep: 6142) Nov 11, '20 at 5:50 am
0
Please do as follows to send an email based on cell value in Excel.
1. In the worksheet you need to send an email based on its cell value, right-click the sheet tab, and select View Code from the context menu.
2. In the popping up Microsoft Visual Basic for Applications window, please copy and paste the below VBA code into the sheet code window.
VBA code: Send email through Outlook based on cell value in Excel :
Dim xRg As Range
'Update by Extendoffice 2018/3/7
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
Set xRg = Intersect(Range("D7"), Target)
If xRg Is Nothing Then Exit Sub
If IsNumeric(Target.Value) And Target.Value > 200 Then
Call Mail_small_Text_Outlook
End If
End Sub
Sub Mail_small_Text_Outlook()
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2"
On Error Resume Next
With xOutMail
.To = "Email Address"
.CC = ""
.BCC = ""
.Subject = "send by cell value test"
.Body = xMailBody
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub
Hope this was helpful.