Sending Specific emails if a specific cell changes

JON_ROCKS

New Member
Joined
Jan 18, 2021
Messages
17
Office Version
  1. 2019
Hi All, I am fairly new to Excel, I need to make the below work , it does trigger email but only for cell M6 then nothing? Can anybody help?

HTML:
Option Explicit

Private Sub Worksheet_Calculate()
 If Me.Range("M6").Value < 3 Then
        Call Mail_small_Text_Outlook
    End If
End Sub

Sub Worksheet_Calculate7()
 If Me.Range("M7").Value < 3 Then
        Call Mail_small_Text_Outlook7
    End If
End Sub

Private Sub Worksheet_Calculate_8()
 If Me.Range("M8").Value < 3 Then
        Call Mail_small_Text_Outlook_8
    End If
End Sub
Private Sub Worksheet_Calculate_9()
 If Me.Range("M9").Value < 3 Then
        Call Mail_small_Text_Outlook_9
    End If
End Sub

HTML:
Option Explicit

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 Laura" & vbNewLine & vbNewLine & _
              "The current stock of Accusence Cameras Part Number: DS-2CD2346G2-I is at 3 units or below. New stock should be ordered." & vbNewLine & _
              "Kind Regards" & vbNewLine & vbNewLine & _
              "Stock Keeper"
    On Error Resume Next
    With xOutMail
        .To = "jon@test.co.uk"
        .CC = ""
        .BCC = ""
        .Subject = "Low Stock Alert - Acusense Cameras"
        .Body = xMailBody
        .Display   'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub


Sub Mail_small_Text_Outlook_7()
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Hi Laura" & vbNewLine & vbNewLine & _
              "The current stock of POE Hubs is at 3 units or below. New stock should be ordered." & vbNewLine & _
              "Kind Regards" & vbNewLine & vbNewLine & _
              "Stock Keeper"
    On Error Resume Next
    With xOutMail
        .To = "jon@test.co.uk"
        .CC = ""
        .BCC = ""
        .Subject = "Low Stock Alert - POE HUB"
        .Body = xMailBody
        .Display   'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub

Sub Mail_small_Text_Outlook_8()
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Hi Laura" & vbNewLine & vbNewLine & _
              "The current stock of Stock 99 is at 3 units or below. New stock should be ordered." & vbNewLine & _
              "Kind Regards" & vbNewLine & vbNewLine & _
              "Stock Keeper"
    On Error Resume Next
    With xOutMail
        .To = "jon@test.co.uk"
        .CC = ""
        .BCC = ""
        .Subject = "Low Stock Alert - Stock99"
        .Body = xMailBody
        .Display   'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub

Sub Mail_small_Text_Outlook_9()
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Hi Laura" & vbNewLine & vbNewLine & _
              "The current stock of STOCK 100 is at 3 units or below. New stock should be ordered." & vbNewLine & _
              "Kind Regards" & vbNewLine & vbNewLine & _
              "Stock Keeper"
    On Error Resume Next
    With xOutMail
        .To = "jon@test.co.uk"
        .CC = ""
        .BCC = ""
        .Subject = "Low Stock Alert - STOCK 100"
        .Body = xMailBody
        .Display   'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub

HTML:
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,123
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Forum!

A worksheet calculate event will trigger Sub Worksheet_Calculate()

The Sub name needs to be spelled exactly this way, no variations.

So any code you want triggered by a worksheet calculate needs to be in this Sub.
 

JON_ROCKS

New Member
Joined
Jan 18, 2021
Messages
17
Office Version
  1. 2019
Thanks i get what you are saying and despite the sub for m7 being spelt wrong i have corrected this and still no joy?

is there an easier way to do this?

Basically all i really need is that when a specific cell is <3 there is an individual email sent?
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,123
Office Version
  1. 365
Platform
  1. Windows
Perhaps something like this:

VBA Code:
Private Sub Worksheet_Calculate()
 
    Dim sName(6 To 9) As String
    Dim i As Long
    
    sName(6) = "Accusence Cameras Part Number: DS-2CD2346G2-I"
    sName(7) = "POE Hubs"
    sName(8) = "Stock 99"
    sName(9) = "Stock 100"
    
    For i = 6 To 9
        If Range("M" & i).Value < 3 Then Call Mail_small_Text_Outlook(sName(i))
     Next i
 
 End Sub
Sub Mail_small_Text_Outlook(sName As String)
    
    '....
    xMailBody = "Hi Laura" & vbNewLine & vbNewLine & _
              "The current stock of " & sName & " is at 3 units or below. New stock should be ordered." & vbNewLine & _
              "Kind Regards" & vbNewLine & vbNewLine & _
              "Stock Keeper"
    '....
    With xOutMail
        '....
        .Subject = "Low Stock Alert - " & sName
        '....
    End With
    '....

End Sub

I have hard-coded the item names here, but presumably we can find these in the worksheet?
 

JON_ROCKS

New Member
Joined
Jan 18, 2021
Messages
17
Office Version
  1. 2019

ADVERTISEMENT

Hi , I really appreciate your help here, and sorry if i sound stupid, im still learning lol

so tried your code but im hit with a 424 error "object required"

on debug it highlights the following line:

.Subject = "Low Stock Alert - " & sName
 

JON_ROCKS

New Member
Joined
Jan 18, 2021
Messages
17
Office Version
  1. 2019

ADVERTISEMENT

Hi , I really appreciate your help, it was literally a copy and paste of what you had above into the stock tab VBA
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,123
Office Version
  1. 365
Platform
  1. Windows
Sorry. my post was probably a bit cryptic - for Sub Mail_small_Text_Outlook I only showed the lines you needed to change on your original.

Here's the full code. I just tested and it works OK for me:

VBA Code:
Private Sub Worksheet_Calculate()
 
    Dim sName(6 To 9) As String
    Dim i As Long
    
    sName(6) = "Accusence Cameras Part Number: DS-2CD2346G2-I"
    sName(7) = "POE Hubs"
    sName(8) = "Stock 99"
    sName(9) = "Stock 100"
    
    For i = 6 To 9
        If Range("M" & i).Value < 3 Then Call Mail_small_Text_Outlook(sName(i))
     Next i
 
 End Sub
Sub Mail_small_Text_Outlook(sName As String)
    
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Hi Laura" & vbNewLine & vbNewLine & _
        "The current stock of " & sName & " is at 3 units or below. New stock should be ordered." & vbNewLine & _
        "Kind Regards" & vbNewLine & vbNewLine & _
        "Stock Keeper"
    
    With xOutMail
        .To = "jon@test.co.uk"
        .CC = ""
        .BCC = ""
        .Subject = "Low Stock Alert - " & sName
        .Body = xMailBody
        .Display   'or use .Send
    End With
    
    Set xOutMail = Nothing
    Set xOutApp = Nothing

End Sub
 

JON_ROCKS

New Member
Joined
Jan 18, 2021
Messages
17
Office Version
  1. 2019
lol sorry no it was me i am new to this and thick as 2 short planks sometimes, after rereading your post i got it, Thanks for all your help it works perfectly!!
 

JON_ROCKS

New Member
Joined
Jan 18, 2021
Messages
17
Office Version
  1. 2019
Ok, Minor Problem, Based on your code i expanded this to cover al the cellss i need this to work with, for the sake of argument i have just copied and pasted the same names here.

The problem i have is that now if i reduce the amount to less than 3 on 1 cell it just opens up email windows indefinitely until i break the cycle? this is obviously something i have missed? i have pasted the code exactly as i have it and just changed all the content names to stock 100 for ease.

Any ideas where i have gone wrong?

VBA Code:
Private Sub Worksheet_Calculate()
 
    Dim sName(6 To 58) As String
    Dim i As Long
    
    sName(6) = "Stock 100"
    sName(7) = "Stock 100"
    sName(8) = "Stock 100"
    sName(9) = "Stock 100"
    sName(10) = "Stock 100"
    sName(11) = "Stock 100"
    sName(12) = "Stock 100"
    sName(13) = "Stock 100"
    sName(14) = "Stock 100"
    sName(15) = "Stock 100"
    sName(16) = "Stock 100"
    sName(17) = "Stock 100"
    
    sName(19) = "Stock 100"
    sName(20) = "Stock 99"
    
    sName(24) = "Stock 100"
    sName(25) = "Stock 100"
    sName(26) = "Stock 100"
    sName(27) = "Stock 100"
    sName(28) = "Stock 100"
    sName(29) = "Stock 100"
    sName(30) = "Stock 99"
    sName(31) = "Stock 100"
    sName(32) = "Stock 100"
    sName(33) = "Stock 100"

    sName(37) = "Stock 100"
    
    sName(46) = "Stock 100"
    sName(47) = "Stock 100"
    sName(48) = "Stock 99"
    sName(49) = "Stock 100"
    sName(50) = "Stock 100"
    sName(51) = "Stock 100"
    sName(52) = "Stock 100"
    sName(53) = "Stock 100"
    
    sName(55) = "Stock 100"
    sName(56) = "Stock 99"
    sName(57) = "Stock 100"
    sName(58) = "Stock 100"
    
    For i = 6 To 58
        If Range("M" & i).Value < 3 Then Call Mail_small_Text_Outlook(sName(i))
     Next i
 
 End Sub
Sub Mail_small_Text_Outlook(sName As String)
    
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Hi Laura" & vbNewLine & vbNewLine & _
        "The current stock of " & sName & " is at 3 units or below. New stock should be ordered." & vbNewLine & _
        "Kind Regards" & vbNewLine & vbNewLine & _
        "Stock Keeper"
    
    With xOutMail
        .To = "jon@test.co.uk"
        .CC = ""
        .BCC = ""
        .Subject = "Low Stock Alert - " & sName
        .Body = xMailBody
        .Display   'or use .Send
    End With
    
    Set xOutMail = Nothing
    Set xOutApp = Nothing

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,128,122
Messages
5,628,818
Members
416,342
Latest member
BlueDevil12

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
Top