how do i make the formula so that if the other cell is changed then send an email also with a different recipient

asint

New Member
Joined
Mar 9, 2019
Messages
1
Hi
I just learned VBA, I made code like this


Dim xRg As Range
'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 ("c4"), 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 = "Dear Mr. Tian," & vbNewLine & vbNewLine & _
"order duct tape, 10 pcs remaining duct tape, the stock is not safe" & vbNewLine & _
"thank you"
On Error Resume Next
With xOutMail
.To = "toni@email.com"
.CC = "marko@email.com"
.BCC = ""
.Subject = "order duct tape"
.Body = xMailBody
.Display 'or use .Send
End with
On GoTo Error 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub


the above code goes well, the code above is only for one cell (C4), how do i make the formula so that if the other cell is changed then send an email also with a different recipient
Please help
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,423
You can change the range

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
Set xRg = Intersect(Range("C4:D8"), Target)
If xRg Is Nothing Then Exit Sub
If IsNumeric(Target.Value) And Target.Value < 1 Then
End If
End Sub

How is the recipient decided?
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,325
Messages
5,528,023
Members
409,799
Latest member
mlewan_ca

This Week's Hot Topics

Top