Send email based on cell value on open

nrosmarin

New Member
Joined
May 11, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
I am trying to have Excel send an email automatically when the workbook is open if one of more cells = 30. I have copied code from other forums (I'm very new to this) and none seem to work. From what I have gathered you cant have a sub in a sub (right?) Below is the code I have been using. Can anyone help me get this working? Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRg As Range
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
Set xRg = Intersect(Range("AF2:AF1000"), Target)
If xRg Is Nothing Then Exit Sub
If IsNumeric(Target.Value) And Target.Value = 30 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 & _
"You have pending quotation which its number"
On Error Resume Next
With xOutMail
.To = "email"
.CC = "email"
.BCC = ""
.Subject = "Motor Inspection"
.Body = xMailBody
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
So what does 'doesn't work' mean?
No you can't have a sub within a sub if that means writing one within another, but you can call one from another and that is what you're doing.
I don't see anything wrong with the email code, albeit you don't really need a variable for the Body text. I'm no Excel vba expert, but I don't see the sense in this:

Set xRg = Intersect(Range("AF2:AF1000"), Target)
If xRg Is Nothing Then Exit Sub

partly because after creating the object xRng you don't do anything with it; partly because AFAIK, just using Target returns the value in the cell, not its address, thus xRng is likely always Nothing. Then the whole idea of AF2:AF1000 intersecting doesn't make sense to me, but I confess I'm new to a lot of Excel concepts (much better at Access vba so maybe someone will comment on that for us. Yes, Target returns a Range object, but like every (most?) objects it has one or more properties. In many cases the default property is .Value, so when you don't specify the property, you get the default. You might want to use Target.Address if you think the intersect is needed.
I think another mistake there is using On Error Resume Next - it doesn't seem appropriate.

Maybe try
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If IsNumeric(Target) And Target = 30 Then Call Mail_small_Text_Outlook
End Sub

Please use code tags (vba button on posting toolbar) and maintain indentation for more than a few lines of code to make it easier to read.
EDIT
The email body seems to be a work in progress? If you need help with that, post back about the email code.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,908
Members
448,532
Latest member
9Kimo3

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