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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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:
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top