VBA question - email alerts prompted by cell values

Toph42

New Member
Joined
May 19, 2019
Messages
10
Hi,

I need to have email alerts prompted based on cell values (e.g. when call value is >200 an email alert is triggered). I've used the below VBA code, but it only works when I manually enter into the specific cells. I now need an email to be prompted when the values in the specified cells are populated by a formula. Does anyone know if I can amend this VBA code, or if there is another way to achieve this? Thank you!
File-Copy-icon.png

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">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</code>
 
No value in F2 to F24 is greater than 10.5?

It works for me, only if any of the values ​​in F2 to F24 is greater than 10.5 send the mail.
You can put an image of your data or upload an image or upload your file.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.



Did you modify something else in the macro?

I think I have found the problem. When there is a cell value from a previous day that is >10.5, the new values entered always prompt the email based on any value.

For example, in the below table you can see that on Thursday the value is 10.74. After this all new values that are entered in this column prompt the email. If i start a new sheet without any previous numbers being >10.5 it is fine though. I have no idea how to get around this. Do you know?

DATEAM READINGPM READINGAM USAGEUNITSL PER Unit
Wednesday, 1 May 20193317843319211371323110.35
Thursday, 2 May 20193321393322751361266810.74
Friday, 3 May 2019332524332680156173359.00
Monday, 6 May 2019332970333135165206407.99
Tuesday, 7 May 2019333347333509162171919.42

Thanks for your help. It is greatly appreciated.
 
Upvote 0

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Let's go back to the formula.
I already understood that in F2 you do not capture data because there in F2 you have a formula.
Can you put again what formula you have in F2?
 
Upvote 0
In each of F2:F24, I have =(D6*1000)/E6.

If there is already one cell populated with a value >10.5, every subsequent number populated in that range prompts an email (even when the number is lower than 10.5).

What do you think?

[TABLE="width: 713"]
[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]DATE[/TD]
[TD]AM READING[/TD]
[TD]PM READING[/TD]
[TD]AM USAGE[/TD]
[TD]BIRDS[/TD]
[TD]L PER BIRD[/TD]
[/TR]
[TR]
[TD]Wednesday, 1 May 2019[/TD]
[TD]331784[/TD]
[TD]331921[/TD]
[TD]137[/TD]
[TD]13231[/TD]
[TD]10.35[/TD]
[/TR]
[TR]
[TD]Thursday, 2 May 2019[/TD]
[TD]332139[/TD]
[TD]332275[/TD]
[TD]136[/TD]
[TD]12668[/TD]
[TD]10.74[/TD]
[/TR]
[TR]
[TD]Friday, 3 May 2019[/TD]
[TD]332524[/TD]
[TD]332680[/TD]
[TD]156[/TD]
[TD]17335[/TD]
[TD]9.00[/TD]
[/TR]
[TR]
[TD]Monday, 6 May 2019[/TD]
[TD]332970[/TD]
[TD]333135[/TD]
[TD]165[/TD]
[TD]20640[/TD]
[TD]7.99[/TD]
[/TR]
[TR]
[TD]Tuesday, 7 May 2019[/TD]
[TD]333347[/TD]
[TD]333509[/TD]
[TD]162[/TD]
[TD]17191[/TD]
[TD]9.42[/TD]
[/TR]
[TR]
[TD]Thursday, 9 May 2019[/TD]
[TD]333740[/TD]
[TD]333861[/TD]
[TD]121[/TD]
[TD]16159[/TD]
[TD]7.49[/TD]
[/TR]
[/TABLE]
 
Upvote 0
What formula do you have in F2?
This
=(D6*1000)/E6.
Or this:
=(D3*1000)/E3
?
 
Upvote 0
Try this


Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D2:D24, E2:E24")) Is Nothing Then
        If Target.Count > 1 Then Exit Sub
        If Cells(Target.Row, "F").Value > 10.5 Then
            Call Mail_small_Text_Outlook
        End If
    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
 
Upvote 0
You have to modify a cell in the range D2:D24 or E2:E24. For example, if you modify cell D6 and the result in F6 is greater than 10.5, then an email is generated.
But you have to change a data, either in D or in E.
 
Upvote 0
You have to modify a cell in the range D2:D24 or E2:E24. For example, if you modify cell D6 and the result in F6 is greater than 10.5, then an email is generated.
But you have to change a data, either in D or in E.

Thank you so much!!
 
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top