[botello2008]

Hello guys, I need some help. I have an Excel spreadsheet that I use to watch financial information about companies that I buy stocks from when certain conditions are met. I use a program called XLQ which works along with Excel to extract the financial information from the internet and displays it on the spreadsheet. The numbers constantly change based on the stock market movement. I have two conditions that need to be met in order for me to buy a stock, when such conditions are met the cells change color, I have set it up that way to let me know when it is time to buy a stock.

So my question is... Is it possible to have Microsoft Excel automatically send me an email to my email inbox or cell phone when those two conditions are met and the cells change color. I would really appreciate any help with this. Let me know if you need more details about this problem I am having.

Thanks,

Eleazar Botello

[slurpee55]

Assuming you are using Outlook (or possibly Outlook Express - but Outlook has more abilities for this).
The most information in one page is here:
http://msdn.microsoft.com/en-us/libr...ffice.11).aspx
Other pages to look at are:
http://www.ozgrid.com/forum/showthread.php?t=20166
http://www.rondebruin.nl/sendmail.htm
http://www.rondebruin.nl/mail/add-in.htm (some nifty add-ins to automate the whole thing)
http://www.dicks-clicks.com/excel/olSending.htm

I'm not much of a coder - if you want something more specific, just wait. Zack or Bomb or Villan or OBP or jimr or....will come along

[Zack Barresse]

You need an event we could use to trigger this type of code. Unfortunately, changing the cells color does not fire any events we have access to. Here are some thing we can use to do this for you...

Cell selection changes (i.e. clicking another cell)
Cell changes (most commonly used, when value changes)
Sheet activate/deactivate
Cell double click
Cell right click
Follow hyperlink (not the formula) from a cell
Calculate a worksheet (also commonly used)

Basically, we can hook these events to test for you condition(s) and dispatch an email. Yes, this can be done, providing you use one of the above. Will this be the case?

[OBP]

My question is what do you currently used to change the Colours of the Cells?
Is it VBA or Conditional Formatting?

[slurpee55]

Rather than changing the color of the cells (or in addition to it) could you have a formula in another cell that would change when the two criteria are met, such as =IF(a1="x" AND b1="y","Send mail","") ?

[Zack Barresse]

Slurpee, while that is a good idea, just make sure the OP understands that if they are looking for a static and uncalculated sheet to send based on this information, if it is not triggered from an event (like those I listed) it's not going to happen. If they understand that when this cell is calculated an event could fire, that would be better.

[slurpee55]

I am not even sure what information the OP wants sent - the worksheet or maybe just a static notice like "Time to buy stock" or "Sell stock"....

[botello2008]

Thanks for all your replies, I like slurpee55's idea of having another formula that triggers the email (an IF-AND formula). And yes I just need a short message such as "Time to buy GOOG" for example. That way I can go to the computer when I receive the message and verify it is true. I don't need the whole spreadsheet sent to me, just a short message. I basically want to leave the computer turned on and that Excel sends me the message when it is time to buy a stock, I don't want to be in front of the computer all the time. Is it possible to do it?

[slurpee55]

Botello, since it will be needed (I think) in order to write the code, what email program are you using?
Oh, and if you want a message that states what to buy (not just a static message "Time to buy") we will need to know what column that name is in, as well as the columns tracking the data, etc.
If you could post a sample sheet, that would help a lot.

[slurpee55]

Also, what version of Excel, etc. are you using? And your OS?

[slurpee55]

This might be of some interest to you
http://www.rondebruin.nl/cdo.htm

[Zack Barresse]

Yes, the CDO method is great. Especially since it bypasses the dreaded Outlook security warnings. It can be difficult to setup and is quite difficult to test, but once setup it is beautiful. I used it full blown only one time on a project. In it's own part, it worked flawlessly.

Personally, I prefer Outlook. That way I can still use all my Outlook rules/alerts and such, plus my tracking, Search Folders and sorting. But I use Outlook by default, so I benefit from it. To bypass the security message in Outlook, I use the [free] ClickYes program - which we can programmatically control (yay!).

Edit: Also, botello2008, understand that if you are looking at the results of a formula, we cannot do anything unless the sheet actually calculates. Does that make sense? A formula can sit there all day long, but so long as it doesn't calculate, you're pretty much done. Here is a workaround:

Use a calculate event to call a standard sub routine (which gives us dynamics in calling the routine anytime/anyplace)
Create a VBS file to open the workbook and call the sub routine
Set the VBS file to Windows Scheduler at x intervals

Doing it this way will not only rely on your calculate event to check on things for you, but if you are not sitting at your computer, Windows Scheduler can run it for you. This is, of course, way over the top of what you've stated, but is a possibility. Look here for a solution which used this type of behavior...

http://forums.techguy.org/business-a...ase-excel.html

[slurpee55]

Yeah - that is what I gathered from Ron de Bruin's page - and that is why I asked what OS (and email) the OP is using. And, since I assume you know what you are talking about (LOL), here is a link to the ClickYes program:
http://www.contextmagic.com/express-clickyes/

[Zack Barresse]

There is also a link in the thread I linked (although it was in an Edit ).

[slurpee55]

well, a link to a link to a link to....grief!