Sending email based on cell value

[Saeva]

Hello,
First of all, I am still new to VBA and more complex excel coding, so I'm learning by doing, searching forums and tutorials, stitching together bits and pieces of code I need. However, I ran into a dead end and would need help. Basically, what I'm trying to do: I have a project to sort documents based on certain criteria, and then decide to which department the document should be directed. Based on data in Column A, column F Decides if it goes after Quality, Logistics, Finances, or if it should be decided manually by someone. The command in the cell looks like this :

=IF(OR(A15="Late delivery",A15="Missing delivery",A15="Damaged packaging",A15="Wrong delivery note",A15="Wrong product"),"Logistics",IF(OR(A15="Damaged product",A15="Wrong description",A15="Different specifications"),"Quality",IF(OR(A15="Missing invoice",A15="Wrong price",A15="Late payment",A15="Wrong invoice"),"Finances","Manual")))

This works just fine. The other thing that I'm trying to do is, that every time the department is decided, email is generated to relevant person in that department. I figured that this should be based on the word in the column, and stitched together this code in VBA:

Dim R As Range
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
Set R = Intersect(Range("F2:F25"), Target)
If R Is Nothing Then Exit Sub
If InStr(1, (Range("f2:f25").Value), "Quality") > 0 Then
Call send_mail_outlook
End If
End Sub
Sub send_mail_outlook()
Dim x As Object
Dim y As Object
Dim z As String
Set x = CreateObject("Outlook.Application")
Set y = x.CreateItem(0)
z = "Hello!" & vbNewLine & vbNewLine & _
"This is a reminder that the database was updated and is waiting for your evaluation." & vbNewLine & _
""
On Error Resume Next
With y
.To = "XYZ@mail.com"
.cc = ""
.BCC = ""
.Subject = "Database update"
.Body = z
.Display
End With
On Error GoTo 0
Set y = Nothing
Set x = Nothing
End Sub

This code works, but only when I type the word "Quality" in the cell manually, hence missing the point of making it as automatic as possible.
I would like to ask if it's possible to make the macro read what the user sees, rather then what is physically in the cell.
Thank you very much for any help.

 

[Saeva]

Hello,
First of all, I am still new to VBA and more complex excel coding, so I'm learning by doing, searching forums and tutorials, stitching together bits and pieces of code I need. However, I ran into a dead end and would need help. Basically, what I'm trying to do: I have a project to sort documents based on certain criteria, and then decide to which department the document should be directed. Based on data in Column A, column F Decides if it goes after Quality, Logistics, Finances, or if it should be decided manually by someone. The command in the cell looks like this :

=IF(OR(A15="Late delivery",A15="Missing delivery",A15="Damaged packaging",A15="Wrong delivery note",A15="Wrong product"),"Logistics",IF(OR(A15="Damaged product",A15="Wrong description",A15="Different specifications"),"Quality",IF(OR(A15="Missing invoice",A15="Wrong price",A15="Late payment",A15="Wrong invoice"),"Finances","Manual")))

This works just fine. The other thing that I'm trying to do is, that every time the department is decided, email is generated to relevant person in that department. I figured that this should be based on the word in the column, and stitched together this code in VBA:

Dim R As Range
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
Set R = Intersect(Range("F2:F25"), Target)
If R Is Nothing Then Exit Sub
If InStr(1, (Range("f2:f25").Value), "Quality") > 0 Then
Call send_mail_outlook
End If
End Sub
Sub send_mail_outlook()
Dim x As Object
Dim y As Object
Dim z As String
Set x = CreateObject("Outlook.Application")
Set y = x.CreateItem(0)
z = "Hello!" & vbNewLine & vbNewLine & _
"This is a reminder that the database was updated and is waiting for your evaluation." & vbNewLine & _
""
On Error Resume Next
With y
.To = "XYZ@mail.com"
.cc = ""
.BCC = ""
.Subject = "Database update"
.Body = z
.Display
End With
On Error GoTo 0
Set y = Nothing
Set x = Nothing
End Sub

This code works, but only when I type the word "Quality" in the cell manually, hence missing the point of making it as automatic as possible.
I would like to ask if it's possible to make the macro read what the user sees, rather then what is physically in the cell.
Thank you very much for any help.

Now I have an additional problem. I'm trying to stitch together three macros, so different email is created if the cell its based on is for quality, finances or logistics. And every time I put in "Logistics", it created the email for quality. At this point I became desperate and started goofing around, turns out the macro creates an email every time the cell is changed. Even when the contents are deleted, new email is created. Which I honestly don't understand, since I thought I have defined the Quality Value in the code.
I would appreciate any help, thank you very much.

 

[Saeva]

Now I have an additional problem. I'm trying to stitch together three macros, so different email is created if the cell its based on is for quality, finances or logistics. And every time I put in "Logistics", it created the email for quality. At this point I became desperate and started goofing around, turns out the macro creates an email every time the cell is changed. Even when the contents are deleted, new email is created. Which I honestly don't understand, since I thought I have defined the Quality Value in the code.
I would appreciate any help, thank you very much.

Nvm, I fixed this one, turns out it was somewhat simple problem. I still have the problem with the macro reading the formula rather then the outcome of it. I'm thinking about maybe walking around it somehow, some type of VBA condition formatting, but I haven't found anything that could solve it yet.

 

[Saeva]

Nvm, I fixed this one, turns out it was somewhat simple problem. I still have the problem with the macro reading the formula rather then the outcome of it. I'm thinking about maybe walking around it somehow, some type of VBA condition formatting, but I haven't found anything that could solve it yet.

Hello, so,
While I was trying to achieve the things I have described above, I must have messed up the code somehow, and I don't see how. Now the code looks like this:

Sub worksheet_change(ByVal target As Range)
Set R = Intersect(Range("K15"), target)
If target.Value = "Quality" Then
 Call send_mail_outlook
End If
If target.Value = "Logistics" Then
Call send_mail_outlook_Log
End If
End Sub

Sub send_mail_outlook()
Dim x As Object
Dim y As Object
Dim z As String
Set x = CreateObject("Outlook.Application")
Set y = x.CreateItem(0)
z = "Hello!" & vbNewLine & vbNewLine & _
"This is a reminder that the database was updated and is waiting for your evaluation." & vbNewLine & _
""
On Error Resume Next
With y
.To = (Range("H5").Value)
.cc = ""
.BCC = ""
.Subject = "Database update"
.Body = z
.Display
End With
On Error GoTo 0
Set y = Nothing
Set x = Nothing
    
    End Sub


Sub send_mail_outlook_Log()
Dim x As Object
Dim y As Object
Dim z As String
Set x = CreateObject("Outlook.Application")
Set y = x.CreateItem(0)
z = "Hello!" & vbNewLine & vbNewLine & _
"This is a reminder that the database was updated and is waiting for your evaluation." & vbNewLine & _
""
On Error Resume Next
With y
.To = (Range("H9").Value)
.cc = ""
.BCC = ""
.Subject = "Database update"
.Body = z
.Display
End With
On Error GoTo 0
Set y = Nothing
Set x = Nothing


End Sub




    

Private Sub Worksheet_SelectionChange(ByVal target As Range)

End Sub

I made it more simple, because ultimately it turned out the less complicated the code is, the easier it is to keep track of what is happening. Who would have guessed. However, now for some reason the code work on the whole sheet, even though I believe I have specified that it should read the cell K15. Or maybe not, as I said, I am new to this, and therefore I'm probably missing something pretty important. Either way, can anyone help me spot what it is that I am missing?
Thank you very much