Sending 3 email?

[Gee]

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 "(e-mail address removed)"
aEmail.Send

End If

End Sub

 

[Jacob Skaria]

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

 

[Gee]

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

 

[Gary Keramidas]

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

 

[Jacob Skaria]

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 tab>view 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 "(e-mail address removed)"
aEmail.Send
End If
End If
End Sub

Try and feedback..

If this post helps click Yes

 

[Gee]

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!

 

[Gee]

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 "(e-mail address removed)"
aEmail.Send
End If
End Sub

 

[Gary Keramidas]

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 = "(e-mail address removed)"
.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

 

[Gee]

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?

 

[Gee]

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!