this is typically what i use to send an email. try it and see what happens. i have it set
to display for testing purposes with the send line commented out. i just used your calc
event.

Option Explicit
Private Sub Worksheet_Calculate()
Dim OutApp As Object
Dim OutMail As Object
Dim strBody As String
If Range("HK2").Value = "YES" Then

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
strBody = Range("A2").Value

With OutMail
.To = "
.CC = ""
.BCC = ""
.Importance = 2
.Subject = "TEST NOC AGING CALL NUMBER"
.Body = strBody
.display
'.Send
End With

Set OutMail = Nothing
Set OutApp = Nothing
End If
End Sub

--

Gary Keramidas
Excel 2003


"Gee" wrote in message
...
OK, this is where I am.
Every thing works except it sends 3 emails instead of just one.
It is a spreadsheet that is unmonitored and linked to an Access database and
refreshes every minute.

Maybe some kind of hack/crack that moves down one cell to "fake" a
SelectionChange? Anyone know how I can do that?

Private Sub Worksheet_Calculate()
If Range("HK2").Value = "YES" Then
Set aOutlook = GetObject(, "Outlook.Application")
Set aEmail = aOutlook.CreateItem(0)
aEmail.Importance = 2
aEmail.Subject = "TEST NOC AGING CALL NUMBER"
aEmail.Body = Range("A2")
aEmail.Recipients.Add "
aEmail.Send
End If
End Sub