Hi "Gee"

--Why do you use WorksheetCalculate event? Since you havent mentioned what
you are looking for I assume you are trying to send an email when
Range("HK2") is changed to 'YES'. If so try the below code

--Right click the sheet tabview code and paste the below code. Please
remove any exitsing code

--The below code will trigger when you change the cell HK2 to YES.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 And Target.Address = "$HK$2" Then
If UCase(Trim(Range("HK2"))) = "YES" Then
Set aOutlook = GetObject(, "Outlook.Application")
Set aEmail = aOutlook.CreateItem(0)
aEmail.Importance = 2
aEmail.Subject = "NOC AGING CALL NUMBER"
aEmail.Body = Range("A2")
aEmail.Recipients.Add "
aEmail.Send
End If
End If
End Sub

Try and feedback..

If this post helps click Yes
---------------
Jacob Skaria


"Gee" wrote:

Mr Skaria,
No, it didn't work...it didn't send any emails at all...thanks for the
suggestion, any other ideas?


"Jacob Skaria" wrote:

Disable the events as below and enable after your code..

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
'your code

'/your code
Application.EnableEvents = True
End If


End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Gee" wrote:

This works!!
I've been working on it forever and finally figured it out, with all of your
help, of course.
The only problem NOW is that it sends 3 emails to the address instead of
just one. I tried changing it to "OnRefresh", "BeforeUpdate", "AfterUpdate",
"OnChange" ect.

What am I missing?

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 = "NOC AGING CALL NUMBER"
aEmail.Body = Range("A2")
aEmail.Recipients.Add "
aEmail.Send

End If

End Sub