Send email based on cell value on open

[nrosmarin]

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

 

[Micron]

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

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.