Conditional statement emailing in Excel (w/ a twist) 1

[abzurd]

I have a spreadsheet that has DDE links to a live NYMEX (New York Merchantile Exchange)feed that has pricing for natural gas. In the spreadsheet I enter customers and the prices they would like to buy their gas. When that price is met we want an email to be sent to whomever we choose that alerts us that a price has been met. We have figured this part out.....BUT,

The kicker is that the email continues to send each time the value in the cell changes, but still meets the pricing criteria. I need to get the email to STOP after sending once. Some kind of function that says "OK, the condition has been met and I've done the email sending thing, now stop processing". The only way we can think to do it ends in the dredded circular reference. We have been trying to get the macro to write to more than 1 cell, but have been unsuccesful.

BTW - I am not the programmer, but the user and requester. I understand what I want, but not how to do it. We have an on site programmer, but she is is more familiar with the other kind of programming needs we have (ASP, SQL, etc) so we're looking for a little help. Thank you in advance.

 

[Zathras]

It seems like you should be able to keep track of the price that was sent out (in another column) and only send another e-mail when the ticker price is some percentage below it. (Then remember the new price, and so on.)

What am I missing?

 

[abzurd]

In one column is the target price. In another column is the DDE link with the current price. The DDE link changes constantly. There is a third column that has the variance between the two prices. The current macro function checks this variance column. When the variance crosses 0, meaning the target has been met or exceeded, then an email is sent out.

We have tried something similar to what you suggest in that the function generates a message in yet another column that says "email sent" once the email has been sent. Problem is the email continues to send repeatedly anytime the DDE link changes, but is below the 0 variance. Also, the "email sent" statement goes away when the variance crosses over 0. We can't get the function to write to 2 cells. It cand send an email out, but can't "freeze" the price at which it sent it out and then not send out additional notices.

I'll talk to the programmer tomorrow and make sure I'm actually understanding it myself.

 

[xlbo]

Why not have a small part of the macro that, on sending the email, marks a cell to the right of the variance calc with a 1 or "SENT" etc
Your code BEFORE the email is sent, could then check to see whether that cell has a 1 or SENT or whatever in it and if so, not mail
You could then have another sub to reset any cells so that the mailing can start again

pseudo code

loop variance values
check variance values
If variance value<=0 then
if variancevaluecell.offset(0,1).text <> &quot;SENT&quot; then
send email
variancevaluecell.offset(0,1).value = &quot;SENT&quot;
else
don't send email
end if
else
end if
next Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[abzurd]

Thanks for the replies. The problem is an excel function can write value ONLY to the cell which was called from, excel won't let a User Defined function to change anything but the value of the cell which called the function. In macros we can do anything but you cannot run a macro continuously(my understanding) and if we call the macro from a function it behaves the same... any help for this suituation..

Thanks.

 

[xlbo]

Ok - I know that about functions but you didn't mention that you were using a function in the worksheet to send the email.
What does the function return then ??
If the function returns a result once the email has been sent, you can use the cell next to it with an If formula to determine whether the mail has been sent or not and apply the same methodology as above but without writing to the cell

eg in D2 =IF(C2=1,1,0)
Within the function you can check for the value of the adjacent cell Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[abzurd]

&quot;What does the function return then ??&quot;

- The function returns &quot;email sent&quot;


&quot;eg in D2 =IF(C2=1,1,0)
Within the function you can check for the value of the adjacent cell&quot;

- If the function checks for the value of D2, which is referring to C2, you have created a circular reference. So the function fails. We have tried this solution.

Can the function overwrite the formula so the function will not be called again? All the ways we can think of require the function to check data within the cell the function is written in. This doesn't work.

 

[Zathras]

Try to have the function return &quot;email sent - $45.888&quot;
and then you can include that price in you logic to see whether or not to send another.

You can extract the price from the string with this formula that looks for the dollar sign as an indicator of where the amount begins (for example if the function is in cell A1):

  =VALUE(MID(A1,FIND("$",A1)+1,99))

No circular reference, no double e-mailings.

 

[abzurd]

I'm not sure how there is no circular reference in your example. The function seems to still be referring to a value that is written in the same cell as the function.


This is the current formula:
=IF(H5<=0,SendEmail(ROW()),&quot;&quot

H5= the variance that changes constantly via the DDE link

This is the current function:

Function SendEmail(rowNum As Integer)
Set ObjOL = CreateObject(&quot;OutLook.application&quot
Set NS = ObjOL.GetNamespace(&quot;MAPI&quot
Set objMail = ObjOL.CreateItem(OlMailItem)
Dim strBody As String

strBody = Cells(rowNum, 2) & &quot; &quot; & Cells(rowNum, 1) & &quot; &quot; & formatterm(Cells(rowNum, 3)) & &quot; - &quot; & formatterm(Cells(rowNum, 4)) & &quot; Hit $&quot; & FormatNumber(Cells(rowNum, 7), 2) & &quot; &quot; & Now()
' MsgBox strBody
With objMail
.To = &quot;nnnnn@aaaa.com&quot;
.Subject = strBody
.HTMLBody = &quot;<html><head></head><body><p> &quot; & strBody & &quot;</p></body></html>&quot;
.send
End With
Set objMail = Nothing
Set ObjOL = Nothing
SendEmail = &quot;email sent&quot;
End Function

 

[Zathras]

Sorry, you are right. It is still circular. I'll have to think about this some more.

 

[xlbo]

You may want to have alook here.

http://www.bmsltd.co.uk/Excel/Default.htm

Circular references are not ALWAYS bad and can be utilised. there are 2 example downloads on this site - specifically designed for working with stock price downloads - ie a similar situation to yours......might be useful Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[Zathras]

The problem is that user-defined functions weren't designed for this sort of thing.

What you really want is a way to respond to the event when new prices are posted.

The way out of the dilemma would is to use the Worksheet_Calculate( ) event. As I imagine your worksheet, you have a user-defined formula that changes it's value because some other cell has changed. If you replace that formula with a simple one (e.g. =A1+1), the Worksheet_Calculate event fires anytime the value in A1 changes (in this example). This allows you to use macro code to do anything you want. Ideally, if there is something that always changes (time?) you only have to have one formula in the entire sheet.

I can picture a three columns alongside everything else:

  CUSTOMER   NYMEX    LAST NYMEX    LAST EMAIL
  ========   =====    ==========    ==========
    123      4.50       4.50          4.40
    447      5.62       5.67          5.60
    601      3.01       3.01          3.01
    700      5.22       5.60          5.40

The macro would loop thru the rows and find where NYMEX is different from LAST NYMEX. Then it can update LAST NYMEX and compare with LAST EMAIL, and generate the e-mail where appropriate.

In this example, you wouldn't report the 5.62 to customer 447 since the last report was 5.60, but you would e-mail the 5.22 to customer 700 since the last report was 5.40.

Put this code in the sheet code module:

Option Explicit

Private Sub Worksheet_Calculate()
  AnalyzeAndReport
End Sub

and put this code in a new code module:

Option Explicit

Sub AnalyzeAndReport()
  MsgBox "Analyzing and reporting..."
End Sub

Most of the code from your function can be moved to the AnalyzeAndReport subroutine.

Hope this helps.

 

[abzurd]

Thanks, Zathras (and you too xlbo - that link that shows circular references being useful is pretty cool)

We unaware of the Worksheet_Calculate() function. We implemented this logic into our solution and got it working.

Thanks again