I've set this VBA to range instead of a single cell. However, I'd like to specify which cell value has triggered this email to be sent. Let say, Value on D3 is greater than 200, that triggered this email message from range (D1:E20). How can I embed this code into VBA, so I can add this to email subject? any help would be greatly appreciated!
-
To post as a guest, your comment is unpublished.Hi Kayden,
If you want to specify a range of cells rather than single cell, the below VBA code can help. But I don't really understand your last question about "so I can add this to email subject". Thanks for your comment.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("D1:E20")) = Target And Target.Value > 200 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 = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2"
On Error Resume Next
With xOutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "send by cell value test"
.Body = xMailBody
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub-
To post as a guest, your comment is unpublished.Hello Crystal,
Thank you very much for your response! I'd like to elaborate an issue I have. I have a range of cells as "target". When email is generated, on Subject line, I'd like to add a cell that triggered this email. For instance, Qty of Mango fell below target value of 200, then I'd like to add Please order "Mango" on the subject line. Since the target is a range, I'd want some type of formula that can refer to cell B2 (Mango - name of the fruit column). I hope you could help me out on this! Thanks once again!-
To post as a guest, your comment is unpublished.HI KAYDEN,
Do you mean the subject line is dynamically changed based on the target value of the Qty column? When Qty of Mango fell below target value of 200, send email with subject line "Please order Mango (refers to cell B2)"; When Qty of another fruit fell below target value of 200, send email with subject line of the corresponding fruit name?-
To post as a guest, your comment is unpublished.Hi Crystal,When targeting a range of cells... how do you get the automated email to call out "identify" the particular cell that generated the email. I am working on a usage based forklift PM spreadsheet that changes column E to read PM NEEDED after 300hrs. We currently have 27 forklifts (cells E3;E49), your code has worked for me in triggering the email but it would be ideal for the email to identify which cell was the email trigger.
-
To post as a guest, your comment is unpublished.Hi,Supposing cell D7 is the email trigger, you just need to include this line Range("D7") & vbNewLine & _ in the xMailBody line of the code as follows:
xMailBody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
Range("D7") & vbNewLine & _
"This is line 2"
-
-
To post as a guest, your comment is unpublished.I think what he's also trying to say (beside the dynamics in the subject of the email) is that by setting a range of cells that can trigger the email, you wont know what that change was and where.
With a single cell instead of a range you'll know exactly where to look once you get the email or you can even add the value that triggered the email in the email body, as I read above by specifying the single cell you're trying to track.
But when you specify a range, how can you add in the body of the email the value and cell or column that started the whole action? How do I know what and where the change happened? -
To post as a guest, your comment is unpublished.
-
-
-