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

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

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

works here, you might have some other code somewhere doing something.

--

Gary Keramidas
Excel 2003


"Gee" wrote in message
...
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

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

No, didn't work, didn't send any email.

I believe it's not working for me because mine doesn't actually have a
"change" made. If I manually type "YES" into the cell, it sends the email,
but the spreadsheet is refreshed every minute from a database. It is
unmonitored and sits and updates it's self. That is why I tried "Calculate",
it's an independent process that runs with the refresh.
Any other ideas?
I think we're on the right track...only 1 email sent!

"Gary Keramidas" wrote:

works here, you might have some other code somewhere doing something.

--

Gary Keramidas
Excel 2003


"Gee" wrote in message
...
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

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

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

It worked, but still send 3 emails instead of just one.
It's got to be the "Calculate"...the sheet runs 3 calculations and sends 3
emails.
If I could get it to "fake" a change or a cell selection?

"Gary Keramidas" wrote:

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

I got it to only send ONE!!

What I did was change the email routine to "SelectionChange" and put this
code under "Calculate"

Private Sub Worksheet_Calculate()
Set Target = Range("HK2")
If Target.Value = "YES" Then
Application.Goto Range("HL2")
End If

Now, when it calculates and there is a YES in HK2 then it goes to HL2 and
that makes the SelectionChange fire!

Thanks so much for all your help!