Generate an email when a cell value = 0

[af8k]

Hello,

Thanks to the content on here I have successfully setup a script where an email is being automatically sent out when one cell within a range of cells reads 0.

Within this email I would like to specify which cell (or cells) relating to this has caused this email to be sent out.

To put this in context, I am using this to send an alert when a stock item has reached zero stock in our inventory, the stock levels are manually adjusted as parts are used.

The email is then sent out to those responsible for re-ordering.

Any help on this would be very much appreciated.

Aidan

 

[davesexcel]

Please supply the code you need help with.

 

[Automatrix]

You may be able to do this with Power Automate, if you have access to it.

 

[af8k]

Please supply the code you need help with.

Hello, thanks for taking the time to reply to my post. This is the code I have been working with.

'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("L5:L321"), Target)
If xRg Is Nothing Then Exit Sub
If IsNumeric(Target.Value) And Target.Value < 1 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 = "Hello" & vbNewLine & vbNewLine & _
"An Engineering spare part has reached zero stock" & vbNewLine & _
"Please check Enginering stock and re-order"
On Error Resume Next
With xOutMail
.To = "john.doe@abc.com"
.CC = ""
.BCC = ""
.Subject = "Stock value is 0"
.Body = xMailBody
.Send ' or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub