Send email when one or multiple cells change to specific name

[djonik1234]

I am trying to set up my inventory worksheet to automatically send emails. when items in the inventory get low. I found a code that I modified and everything work but the code is based on one specific cell "G13". Couple things that I am trying to change and achieve in that code.
1. Change the code to look for multiple cells in column H, I and J that have words typed in those cells and not numbers. Specifically send email when those cells in column H, I and J say "2 Tools Worth"
2. In the body of the email to include all caster type that have changed their status to "2 Tools Worth".

See attachments for reference and Here is my code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 6 Then Exit Sub
If Not Application.Intersect(Range("G13"), Target) Is Nothing Then
If IsNumeric(Target.Value) And Target.Value = 5 Then
Call Mail_small_Text_Outlook
End If
End If
End Sub



Sub Mail_small_Text_Outlook()
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strbody = "This is automated email to inform you that inventory status for casters/fixtures described below has change to '2 Tools Worth':" & vbNewLine & vbNewLine & _
"MPB/STB: Docking Casters" & vbNewLine & _
"Confirm there are enough for tools that are on schedule to be moved out."

On Error Resume Next
With OutMail
.To = "email goes here"
.CC = ""
.BCC = ""
.Subject = "Inventory low on some Casters/Fixtures!"
.Body = strbody
.Attachments.Add ("My Attachment link")
'You can add a file like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[gordsky]

would each line dropping below 2 tools worth being going to a different recipient?

 

[djonik1234]

would each line dropping below 2 tools worth being going to a different recipient?

Hi gordsky and thank you for helping out with this one. It would always be the same recipient for all lines that will show 2 tools worth.

Also something that I think is worth mentioning that the cell that says "2 tools worth" is an actual formula. Would that cause issues when writing code to look for that text? Maybe it would be easier to stick with Quantity left column and those numbest (this is what my code is currently based off).

 

[gordsky]

Ok so from what i understand then if Quantity left drops below 5 you want an email sending to a recipient with the caster/fixtures name below 5.
You want this done for any cell in col G. is that correct? If so how will you differentiate between a cell that has previously had an email sent and a new cell dropping below 5.? or are you happy for the recipient to receive another email?

 

[djonik1234]

Ok so from what i understand then if Quantity left drops below 5 you want an email sending to a recipient with the caster/fixtures name below 5.
You want this done for any cell in col G. is that correct? If so how will you differentiate between a cell that has previously had an email sent and a new cell dropping below 5.? or are you happy for the recipient to receive another email?

I am trying to avoid using Column G as a reference or a trigger to send an email for casters/fixtures that changed their status to 2 tools worth and instead use column H, I and J to specificly focus on text "2 Tools Worth" as a trigger to send email instead. The reason are:
1. The email will be send out only once when using Wording "2 tools worth" as a reference (Column H, I and J) for email sends vs column G and using number. Because if someone will check out that same item later in the day the recipient will get another unnecessary notification.

2. Caster/fixture count required is different per each tool type (See column H, I J cells 11). One tool type requires 5 casters and the other might require 10 to have 2 tools worth of the same caster/fixture. In my opinion it seems that it would be easier to complete the required code with what I am trying to accomplish.

Hope this clarification helps. Let me know if this make sense.

 

[djonik1234]

Ok so from what i understand then if Quantity left drops below 5 you want an email sending to a recipient with the caster/fixtures name below 5.
You want this done for any cell in col G. is that correct? If so how will you differentiate between a cell that has previously had an email sent and a new cell dropping below 5.? or are you happy for the recipient to receive another email?

sorry did not mean to be picky with my last reply. I'll be happy with any help. and if that mean working with code based on column G I'll be more than happy if you can help. Maybe I can modify the code as needed to make sure it takes in account different tool types that require different amount of casters/fixtures.

 

[gordsky]

Ok so you have two options. Either use column G with a worksheet change event as you have been but tweaked OR you will need to use a worksheet calculate event as far as I know worksheet change doesnt pick up formula changed cells. If you go down that route then you will need a way to stop repeat emails being sent.

Working with column G you could use something like

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.count > 1 Then Exit Sub

   If Not Application.Intersect(Range("G:G"), Target) Is Nothing And Target.Value <= 5 Then
   
      Dim OutApp As Object
      Dim OutMail As Object
      Dim strbody As String
      
      Set OutApp = CreateObject("Outlook.Application")
      Set OutMail = OutApp.CreateItem(0)
      
      strbody = "This is automated email to inform you that inventory status for " & Cells(Target.row, 2) & " has change to '2 Tools Worth':" & vbNewLine & vbNewLine & _
                "MPB/STB: Docking Casters" & vbNewLine & _
                "Confirm there are enough for tools that are on schedule to be moved out."

      On Error Resume Next
         
         With OutMail
           .To = "email goes here"
           .cc = ""
           .Bcc = ""
           .subject = "Inventory low on some Casters/Fixtures!"
           .Body = strbody
          ' .Attachments.Add ("My Attachment link")
           .Display
         End With
      On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End If
End Sub

 

[djonik1234]

Ok so you have two options. Either use column G with a worksheet change event as you have been but tweaked OR you will need to use a worksheet calculate event as far as I know worksheet change doesnt pick up formula changed cells. If you go down that route then you will need a way to stop repeat emails being sent.

Working with column G you could use something like

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.count > 1 Then Exit Sub

   If Not Application.Intersect(Range("G:G"), Target) Is Nothing And Target.Value <= 5 Then
  
      Dim OutApp As Object
      Dim OutMail As Object
      Dim strbody As String
     
      Set OutApp = CreateObject("Outlook.Application")
      Set OutMail = OutApp.CreateItem(0)
     
      strbody = "This is automated email to inform you that inventory status for " & Cells(Target.row, 2) & " has change to '2 Tools Worth':" & vbNewLine & vbNewLine & _
                "MPB/STB: Docking Casters" & vbNewLine & _
                "Confirm there are enough for tools that are on schedule to be moved out."

      On Error Resume Next
        
         With OutMail
           .To = "email goes here"
           .cc = ""
           .Bcc = ""
           .subject = "Inventory low on some Casters/Fixtures!"
           .Body = strbody
          ' .Attachments.Add ("My Attachment link")
           .Display
         End With
      On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End If
End Sub

This really helps and adding "& Cells(Target.row, 2) & " in the body of the email does exactly what I wanted . Have a question on how to possibly change portion of the code below. Currently it is looking for <=5 on column G for all Casters and Fixtures. This statement is true for only some casters/fixtures but not all of casters/fixtures require the same amount to represent 2 tools worth. Basically I need to somehow have a code for each caster (row) in column G. Below is the code portion I am talking about.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.count > 1 Then Exit Sub

   If Not Application.Intersect(Range("G:G"), Target) Is Nothing And Target.Value <= 5 Then

 

[gordsky]

What formula are you using in column h to generate your "2 tools worth"

 

[djonik1234]

This is the formula for one of the fixtures. The formula is the same on all the caster/fixtures and tool types just numbers changed based on how many are used per tool type :

=IF(G13=0,"Out of Stock",IF(G13<=1,"Limited Stock",IF(AND(G13<4,G13>=2),"1 Tool Worth",IF(AND(G13<6,G13>=4),"2 Tools Worth",IF(AND(G13<8,G13>=6),"3 Tools Worth",IF(AND(G13<10,G13>=8),"4 Tools Worth","5 or more"))))))