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]

ok so if I have understood you correctly then what you are after is an email to be triggered when H changes to 2 tools worth. As column G causes the formula change then we can stick with the worksheet change event and "Look" at column H afterwards. It will only work on single cell changes

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

If Not Application.Intersect(Range("G:G"), Target) Is Nothing And Target.Offset(0, 1) = "2 Tools Worth" Then

.

So Col G will trigger the code and then the code will look to see if H has changed to 2 Tools Worth. Hopefully that should work and if you need to adapt it give you a base to work from.

 

[djonik1234]

ok so if I have understood you correctly then what you are after is an email to be triggered when H changes to 2 tools worth. As column G causes the formula change then we can stick with the worksheet change event and "Look" at column H afterwards. It will only work on single cell changes

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

If Not Application.Intersect(Range("G:G"), Target) Is Nothing And Target.Offset(0, 1) = "2 Tools Worth" Then

.

So Col G will trigger the code and then the code will look to see if H has changed to 2 Tools Worth. Hopefully that should work and if you need to adapt it give you a base to work from.

Thank you so much! You are very helpful. and everything so far works. How can I get the code to look not only if column H has changed to 2 Tools Worth but and/or Column H, I and J. I was trying to make some changes to the offset portion of the code but I was unsuccessful in adding column I and J to that code to work properly.

 

[gordsky]

Thank you so much! You are very helpful. and everything so far works. How can I get the code to look not only if column H has changed to 2 Tools Worth but and/or Column H, I and J. I was trying to make some changes to the offset portion of the code but I was unsuccessful in adding column I and J to that code to work properly.

do all columns H:J change to 2 tools at the same time. If not suppose an email was already generated by col H changing to 2 tools. Would you want another email generated if col I or J changed. If so then try changing the line to below which should trigger an email if ANY of the three columns become 2 tools worth

If Not Application.Intersect(Range("G:G"), Target) Is Nothing And (Target.Offset(0, 1) = "2 Tools Worth" or Target.Offset(0, 2) = "2 Tools Worth" or Target.Offset(0, 3) = "2 Tools Worth") Then

 

[djonik1234]

do all columns H:J change to 2 tools at the same time. If not suppose an email was already generated by col H changing to 2 tools. Would you want another email generated if col I or J changed. If so then try changing the line to below which should trigger an email if ANY of the three columns become 2 tools worth

If Not Application.Intersect(Range("G13:G15"), Target) Is Nothing And (Target.Offset(0, 1) = "2 Tools Worth" or Target.Offset(0, 2) = "2 Tools Worth" or Target.Offset(0, 3) = "2 Tools Worth") Then

Thank you again. The code you provided works. I was not adding and/or to the code when adding more offsets.

I have one more questions/clarifications and thank you for your patience with me.

Question: If I want to limit what cells in column G (basically I don't want some of the inventory to be triggered send an email. What is the correct code for range portion. For example if I am trying to Include G13 to G15 and another section would be G17 to G18

 If Not Application.Intersect(Range("G:G"), Target) Is Nothing And (Target.Offset(0, 1) = "2 Tools Worth" Or Target.Offset(0, 2) = "2 Tools Worth" Or Target.Offset(0, 3) = "2 Tools Worth") Then


Thank you in advance!

 

[gordsky]

just combine the ranges you want as normal
so for the example you gave

I

f Not Application.Intersect(Range("G13:G15, G17:G18"), Target) Is Nothing And (Target.Offset(0, 1) = "2 Tools Worth" Or Target.Offset(0, 2) = "2 Tools Worth" Or Target.Offset(0, 3) = "2 Tools Worth") Then

 

[djonik1234]

just combine the ranges you want as normal
so for the example you gave

I

f Not Application.Intersect(Range("G13:G15, G17:G18"), Target) Is Nothing And (Target.Offset(0, 1) = "2 Tools Worth" Or Target.Offset(0, 2) = "2 Tools Worth" Or Target.Offset(0, 3) = "2 Tools Worth") Then

Thank you.

Last question. do you think it is possible in my scenario/worksheet with the code that I have and it's function to only send or trigger sending emails after the "2 Tools Worth" was triggered/changed for the first/initial time? The "2 Tools Worth" is based on the range on numbers ex 2-4. Right now if number in column G for a specific caster changes to 4 and then to 3 and then to 2 I get 3 emails stating that the inventory for that caster is "2 Tools Worth".

I am just trying see if we can limit to first initial email the "2 Tools Worth" is registered on the worksheet. It's not that big of a deal but it would decently make it more easier for the person who is receiving the email not to be overloaded with many emails because for some of other fixtures the "2 Tools Worth" range is from 12-24. So if someone is checking out 8 items for example that would be 8 emails saying that you have "2 Tools Worth" of whatever the casters they are checking out from the inventory.

With your help. This is what the current final working code code is. not sure if what was mentioned above can be added and integrated into this code.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count >= 6 Then Exit Sub

   If Not Application.Intersect(Range("G:G"), Target) Is Nothing And (Target.Offset(0, 1) = "2 Tools Worth" Or Target.Offset(0, 1) = "1 Tool Worth" Or Target.Offset(0, 1) = "Limited Stock" Or Target.Offset(0, 1) = "Out of Stock" Or Target.Offset(0, 2) = "2 Tools Worth" Or Target.Offset(0, 2) = "1 Tool Worth" Or Target.Offset(0, 2) = "Limited Stock" Or Target.Offset(0, 2) = "Out of Stock" Or Target.Offset(0, 3) = "2 Tools Worth" Or Target.Offset(0, 3) = "1 Tool Worth" Or Target.Offset(0, 3) = "Limited Stock" Or Target.Offset(0, 3) = "Out of Stock") 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' or less." & vbNewLine & vbNewLine & _
                "Confirm there are enough " & Cells(Target.Row, 2) & " for tools that are on schedule to be moved out."

      On Error Resume Next
         
         With OutMail
           .To = "djon.podvalnii@us.tel.com"
           .cc = ""
           .Bcc = ""
           .Importance = 2
           .Subject = "Low Casters/Fixture Inventory!"
           .Body = strbody
          ' .Attachments.Add ("My Attachment link")
          .Send '.Display
         End With
      On Error GoTo 0

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

 

[gordsky]

I believe i made the point about repeat emails in post#4. It is possible to stop it but you would need a helper column that was populated when an email was sent. I dont know what is after column J but for example you could add helper columns to K L M which would relate to H I J respectively. Once an email is triggered by H I or J a Y is placed in the respetive column of K L M. Next time around the code could look at K LM to see if blank and only send the email if blank. The other option you would need to consider is if H I or J change at the same time. Only 1 email would be generated. If thats what you are after then fine.Perhaps if you attach a sample workbook rather than a picture i could write a more specific code.

 

[djonik1234]

I believe i made the point about repeat emails in post#4. It is possible to stop it but you would need a helper column that was populated when an email was sent. I dont know what is after column J but for example you could add helper columns to K L M which would relate to H I J respectively. Once an email is triggered by H I or J a Y is placed in the respetive column of K L M. Next time around the code could look at K LM to see if blank and only send the email if blank. The other option you would need to consider is if H I or J change at the same time. Only 1 email would be generated. If thats what you are after then fine.Perhaps if you attach a sample workbook rather than a picture i could write a more specific code.

Would you be kind and help with needed code for placing Y for example in respective column of K L M and then the code that would look at K L and M to see if blank and only send email if blank? If there is a way I can thank you for you helping me with this please let me know. If am I am asking to much I understand.

 

[gordsky]

Would you be kind and help with needed code for placing Y for example in respective column of K L M and then the code that would look at K L and M to see if blank and only send email if blank? If there is a way I can thank you for you helping me with this please let me know. If am I am asking to much I understand.

you would need to set helper columns at K L M with header of something like "Email Sent"
can you give me an example of the formulas in columns I and in columns J

 

[djonik1234]

Would you be kind and help with needed code for placing Y for example in respective column of K L M and then the code that would look at K L and M to see if blank and only send email if blank? If there is a way I can thank you for you helping me with this please let me know. If am I am asking to much I underst

you would need to set helper columns at K L M with header of something like "Email Sent"
can you give me an example of the formulas in columns I and in columns J

I and J are the same formula as H. Numbers sometimes change based on what caster fixture that formula is under


=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"))))))