# Solved: Automatic Email Alerts using Excel



## botello2008 (May 8, 2008)

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 (Oct 20, 2004)

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/library/aa203718(office.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 (Jul 25, 2004)

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 (Mar 8, 2005)

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


----------



## slurpee55 (Oct 20, 2004)

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 (Jul 25, 2004)

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 (Oct 20, 2004)

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 (May 8, 2008)

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 (Oct 20, 2004)

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 (Oct 20, 2004)

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


----------



## slurpee55 (Oct 20, 2004)

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


----------



## Zack Barresse (Jul 25, 2004)

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-applications/636786-set-email-reminder-base-excel.html


----------



## slurpee55 (Oct 20, 2004)

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 (Jul 25, 2004)

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


----------



## slurpee55 (Oct 20, 2004)

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


----------



## botello2008 (May 8, 2008)

I set up a formula that gives me a message when both conditions I need to be met are true. Basically it just says Buy Stock, but I'd like to see if you can help me to set it up in a way that it also gives me the symbol on Column B along with the message, so that way I can see which stock is giving the message for. In addition to that I want to see if you can help me to set it up so when the Buy "GOOG" Stock message appears Excel can automatically send me the email. I am using Windows XP and Microsoft Excel 2003. I am not using Outlook for my email, but I can use it if that is required to set it up. I am not a programmer, and I just have basic knowledge about Excel so I would really appreciate your help guys. I am attaching the spreadsheet so you can see it and you can work it out. By the way you are going to need to download the demo version of the XLQ program so it can work, you can get the program from www.qmatix.com. If you have any questions please let me know. Again, I would really appreciate your help.

www.qmatix.com

Eleazar Botello


----------



## slurpee55 (Oct 20, 2004)

Well, your conditional formula could say something like (given that the stock ticker id is always in column B)
Have this in M
=IF(AND(F2>=G2,I2>=J2), "Buy", "Do not Buy Stock") (or =IF(AND(F2>=G2,I2>=J2), "Buy", "")
Then this in N
=IF(M2= "Buy",B2)
and finally in O
=IF(M2="Buy",M2&" "&N2,"")
and send the message from O (in this case, it would say "Buy AEHR"


----------



## botello2008 (May 8, 2008)

I did it and it worked. I got the message telling me "Buy UFPT" for example, that stock met the two conditions today. Now how do I set it up so that when Column O shows the message Excel sends the message to my email inbox, or cell phone?


----------



## Zack Barresse (Jul 25, 2004)

You need to answer all of the questions regarding the email(s) we asked earlier. I.e. are you using Outlook, do you want to use ClickYes, do you want to use the CDO method, do you want to use the Calculate method, etc. We can't do anything without that information. With it, however, it'll take about 10 minutes and we'll have something for you.


----------



## slurpee55 (Oct 20, 2004)

Here I have reached my limit - but I know Zack can quickly write up some code (heck, he may have something like it already) in a flash.


----------



## Zack Barresse (Jul 25, 2004)

I don't have anything pre-packaged, but I do enough of the Emailing (via Outlook) through VBA I can do this (almost verbatim off the top of my head) in about 5-10 min - tops. Just need the info from the OP.


----------



## botello2008 (May 8, 2008)

Hey Zack thanks for your replies. I've been doing a little bit of research on all the different options you gave me to make this work and I think the easiest and probably the fastest way for you to do it is by using Outlook. I want to use Microsoft Outlook and use the ClickYes program as well. I have modified the spreadsheet to have an event to trigger the email. Well actually I just added three more columns to my original spreadsheet (thanks to Slurpee's suggestions), and I now I have the message that will be delivered to my email every time the two conditions are met. Hopefully this can work the way I need it to work, let me know if you need any other information.

Eleazar Botello


----------



## slurpee55 (Oct 20, 2004)

Zack will be able to write you the code, I am sure. In the meantime, you might want to look at this about ClickYes
http://www.excelguru.ca/node/44
This provides some information on how to select data to send from Excel
http://support.microsoft.com/default.aspx?scid=kb;en-us;278973


----------



## Zack Barresse (Jul 25, 2004)

Okay, this seems to work for me in testing...

```
Option Explicit

Dim MyWs As Worksheet

Private Sub Worksheet_Calculate()
    
    Dim OL As Object, olMail As Object, oWShell As Object
    Dim i As Long, iLastRow As Long
    Dim strStock As String, blnCreatedOL As Boolean
    Const sDelim As String = ";" 'stock [text] delimiter for multiple stocks
    Const sWsName As String = "TimeTemp"
    Const tWait As Long = 10 'length of time in minutes to wait before another email is dispatched (after calculate)
    
    Call TOGGLEEVENTS(False)
    
    'Create temporary worksheet if not already created
    If SHEETEXISTS(sWsName, ThisWorkbook) = False Then
        Set MyWs = ThisWorkbook.Sheets.Add(after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        MyWs.Name = sWsName
        MyWs.Visible = xlSheetVeryHidden
    Else
        Debug.Print Format(Date + Time - TimeSerial(0, tWait, 0), "h:mm:ss AM/PM ddd, mmm d, yyyy") & " ~ " & MyWs.Range("A1").Value
        If (Date + Time - TimeSerial(0, tWait, 0)) < MyWs.Range("A1").Value Then GoTo ExitHere
    End If
    
    'Find last row of data, loop through and grab those that say "Buy"
    iLastRow = Me.Cells(Me.Rows.Count, "M").End(xlUp).Row
    For i = 2 To iLastRow
        If Me.Cells(i, "M").Value Like "Buy *" Then
            strStock = strStock & Right$(Me.Cells(i, "M").Value, Len(Me.Cells(i, "M").Value) - 4) & sDelim
        End If
    Next i
    If Right$(strStock, 1) = sDelim Then strStock = Left$(strStock, Len(strStock) - 1)
    If Len(strStock) = 0 Then GoTo ExitHere
    strStock = vbTab & Replace(strStock, sDelim, Chr(10) & vbTab)
    
    'Create Outlook object
    Set OL = GetObject(, "Outlook.Application")
    blnCreatedOL = False
    If OL Is Nothing Then
        Set OL = CreateObject("Outlook.Application")
        blnCreatedOL = True
    End If
    
    'Start ClickYes
    Set oWShell = CreateObject("wscript.shell")
    oWShell.Run ("""C:\Program Files\Express ClickYes\ClickYes.exe"" -activate")
    
    'Create a new email message, add particulars
    Set olMail = OL.CreateItem(0)
    olMail.To = "[email protected]"
    olMail.Subject = "Add subject here"
    olMail.Body = "These items were shown as 'Buy' items:" & vbNewLine & vbNewLine & _
                  strStock & vbNewLine & vbNewLine & _
                  "This email was created automatically on: " & Format(Date + Time, "ddd, mmm d, yyyy, h:mm AM/PM")
    olMail.Send
    
    'Set timer so we don't send an email after each cell calculates
    MyWs.Range("A1").Value = Date + Time
    MyWs.Range("A1").NumberFormat = "h:mm AM/PM ddd, mmm d, yyyy"
    
    'Stop ClickYes if you want
    oWShell.Run ("""C:\Program Files\Express ClickYes\ClickYes.exe"" -stop")
    
ExitHere:
    If blnCreatedOL = True Then OL.Quit
    Call TOGGLEEVENTS(True)
    
End Sub

Public Sub TOGGLEEVENTS(ByVal blnState As Boolean)
'Originally written by Zack Barresse
    With Application
        .Calculation = xlCalculationManual ' -4135 (manual)
        .DisplayAlerts = blnState
        .EnableEvents = blnState
        .ScreenUpdating = blnState
        If blnState Then .Calculation = xlCalculationAutomatic ' -4105 (auto)
        If blnState Then .CutCopyMode = False
        If blnState Then .StatusBar = False
    End With
End Sub

Public Function SHEETEXISTS(wsName As String, Optional wkb As Workbook) As Boolean
    If wkb Is Nothing Then
        If ActiveWorkbook Is Nothing Then Exit Function
        Set wkb = ActiveWorkbook
    End If
    On Error Resume Next
    SHEETEXISTS = Len(wkb.Sheets(wsName).Name)
End Function
```
This all goes into your worksheet module. So right click the sheet tab with your data, select View Code, past in there. Please make sure you look through the code because there are things you need to customize. I did put a list of the available stocks set to "Buy" in the body of the email in a delimited list. If this gets very long, it may make the email message lengthy.

There is one thing we didn't talk about although I did add for it. And that is on each cell calculation, if it meets the criteria, an email would be dispatched for each cell which meets your "Buy" criteria. This is not good (at least I'm assuming you don't want this). So in order to counteract that, I added a hidden sheet and set the last time an email was created in cell A1 of that sheet. Then when the routine is run it checks that cell for its value. If the current date/time is not greater than the value found plus the time to wait (set as a constant - which is the big reason you need to read the code!) then an email will not be created. In essence, you can run through one entire calculation and generate an email. Note that you may want to expand that time, which is a whole number and is expressed in minutes in the code. I have it defaulted to 10 minutes.

Set your email address and the subject. Note this does not have a lot of error handling or comments. I can adjust either one of those if you like, you just need to tell me. It assumes you have the ClickYes program installed, as well as Outlook. We can dummy-proof it as much as you want, again just let us know.

Let us know how this works for you.


----------



## webarchitect (May 12, 2008)

So how did it go? I'm really waiting if this works, since I have a slightly similar problem.


----------



## botello2008 (May 8, 2008)

Hey Zack, thanks for writing the code, I am just testing it and apparently it didn't work for me, I'm not sure if I'm doing something wrong, probably I am. I put my email address and subject for the email, but it's not working. I think you set up the code to send the message on column M instead of the one on column O where I have the actual message I want delivered to my email address. Is there a way we can talk on the phone or by instant messenger so you can help me out testing this code. I can send you the program so you can test it. Please let me know if it is possible that we can talk. My email address is botello2006_ at_yahoo.com


----------



## slurpee55 (Oct 20, 2004)

botello, if you can still edit your previous post, do so by replacing the @ in your email with "at" or something like that - spambots troll everywhere.
also, if you set up your extra formulas like I suggested, Zack's code fires when this line becomes true in column M
=IF(AND(F2>=G2,I2>=J2), "Buy", "Do not Buy Stock") (or =IF(AND(F2>=G2,I2>=J2), "Buy", "")

Zack, nice code - however, shouldn't this line
strStock = strStock & Right$(Me.Cells(i, "M").Value, Len(Me.Cells(i, "M").Value) - 4) & sDelim
reference column O in order to get the value of "Buy UFPT" or whatever?


----------



## botello2008 (May 8, 2008)

Thanks for letting me know that slurpee55, I just edited my last post.


----------



## slurpee55 (Oct 20, 2004)

botello2008 said:


> Hey Zack, thanks for writing the code, I am just testing it and apparently it didn't work for me, I'm not sure if I'm doing something wrong, probably I am. I put my email address and subject for the email, but it's not working. I think you set up the code to send the message on column M instead of the one on column O where I have the actual message I want delivered to my email address. Is there a way we can talk on the phone or by instant messenger so you can help me out testing this code. I can send you the program so you can test it. Please let me know if it is possible that we can talk. My email address is botello2006_ at_yahoo.com


Botello, what happens when you run it? Are you getting an error message?
Do you have Outlook installed properly? And did you install ClickYes in the same location as is lists in the code?


----------



## Rollin_Again (Sep 4, 2003)

Also make sure to check the Macro security level in your workbook. It should be set to LOW

Regards,
Rollin


----------



## botello2008 (May 8, 2008)

I don't have too much knowledge about programming and codes but this is what I did. I copied and pasted the code into the "View Code" section in the sheet 1 tab. I then replaced the email address with my own email address, and I also changed the subject line. I then clicked on run and it is giving me an error ( Runtime Error ' 91' ). I went to the macro security window and I changed it to low, but nothing is happening. Supposedly when any cell on column M says "Buy" Excel should send the email by using Outlook right? I have Outlook installed in the computer and I also installed the ClickYes program under C:\Program Files\Express ClickYes\ClickYes.exe
Is there a way we can talk on the messenger or by phone slurpee55, I really need help doing this.


----------



## Zack Barresse (Jul 25, 2004)

Yes, if the "Buy XXXX" is in column O, then it all would need to be changed over. The test file I had for some reason was in column M (the formulas). Must've missed the col O thingy.


----------



## slurpee55 (Oct 20, 2004)

Also, does the worksheet name need to be changed in the code Zack, and if so, in what lines?


----------



## Zack Barresse (Jul 25, 2004)

No, it's self-sustained. Since it is in the worksheet module, I made use of the *Me *object, so it will refer to whatever class object it is held inside. The only sheet the OP'd need to change is if they already have a sheet named "TimeTemp".


----------



## slurpee55 (Oct 20, 2004)

botello2008 said:


> I don't have too much knowledge about programming and codes but this is what I did. I copied and pasted the code into the "View Code" section in the sheet 1 tab.


Zack, should Botello make a module rather than putting it straight into sheet 1?


----------



## Zack Barresse (Jul 25, 2004)

It was designed for the worksheet module, as it makes use of the worksheet calculate event. It could go into the ThisWorkbook module with a little altering. We could put it into a standard module as well with a little altering, but I'm not sure what the benefit would be. The other routines/functions could indeed go into a standard module, all except the calculate event code.


----------



## Zack Barresse (Jul 25, 2004)

Here is the updated code which works for me in the actual file emailed to me...


```
Option Explicit

Dim MyWs As Worksheet

Private Sub Worksheet_Calculate()
    
    Dim OL As Object, olMail As Object, oWShell As Object
    Dim i As Long, iLastRow As Long, strStock As String
    Dim blnCreatedOL As Boolean, blnCY As Boolean
    Const sDelim As String = ";" 'stock [text] delimiter for multiple stocks
    Const sWsName As String = "TimeTemp"
    Const tWait As Long = 10 'length of time in minutes to wait before another email is dispatched (after calculate)
    
    Call TOGGLEEVENTS(False)
    
    'Create temporary worksheet if not already created
    If SHEETEXISTS(sWsName, ThisWorkbook) = False Then
        Set MyWs = ThisWorkbook.Sheets.Add(after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        MyWs.Name = sWsName
        MyWs.Visible = xlSheetVeryHidden
        MyWs.Range("A1").Value = Date + Time - tWait - 1
    Else
        If MyWs Is Nothing Then Set MyWs = ThisWorkbook.Sheets(sWsName)
        If Len(MyWs.Range("A1").Value) = 0 Then MyWs.Range("A1").Value = Date + Time - tWait - 1
'        Debug.Print Format(Date + Time - TimeSerial(0, tWait, 0), "h:mm:ss AM/PM ddd, mmm d, yyyy") & " ~ " & MyWs.Range("A1").Value
        If (Date + Time - TimeSerial(0, tWait, 0)) < MyWs.Range("A1").Value Then GoTo ExitHere
    End If
    
    'Find last row of data, loop through and grab those that say "Buy"
    iLastRow = Me.Cells(Me.Rows.Count, "O").End(xlUp).Row
    For i = 2 To iLastRow
        If IsError(Me.Cells(i, "O").Value) = False Then
            If Me.Cells(i, "O").Value Like "Buy *" Then
                strStock = strStock & Right$(Me.Cells(i, "O").Value, Len(Me.Cells(i, "O").Value) - 4) & sDelim
            End If
        End If
    Next i
    If Right$(strStock, 1) = sDelim Then strStock = Left$(strStock, Len(strStock) - 1)
    If Len(strStock) = 0 Then GoTo ExitHere
    strStock = vbTab & Replace(strStock, sDelim, Chr(10) & vbTab)
    
    'Create Outlook object
    Set OL = GetObject(, "Outlook.Application")
    blnCreatedOL = False
    If OL Is Nothing Then
        Set OL = CreateObject("Outlook.Application")
        blnCreatedOL = True
    End If
    
    'Start ClickYes
    If Dir("C:\Program Files\Express ClickYes\ClickYes.exe", vbNormal) = "" Then
        blnCY = False
    Else
        Set oWShell = CreateObject("wscript.shell")
        oWShell.Run ("""C:\Program Files\Express ClickYes\ClickYes.exe"" -activate")
        blnCY = True
    End If
    
    'Create a new email message, add particulars
    Set olMail = OL.CreateItem(0)
    olMail.To = "[email protected]"
    olMail.Subject = "TIME TO BUY STOCK"
    olMail.Body = "These items were shown as 'Buy' items:" & vbNewLine & vbNewLine & _
                  strStock & vbNewLine & vbNewLine & _
                  "This email was created automatically on: " & Format(Date + Time, "ddd, mmm d, yyyy, h:mm AM/PM")
    If blnCY = True Then
        olMail.Send
    Else
        olMail.display
    End If
    
    'Set timer so we don't send an email after each cell calculates
    MyWs.Range("A1").Value = Date + Time
    MyWs.Range("A1").NumberFormat = "h:mm AM/PM ddd, mmm d, yyyy"
    
    'Stop ClickYes if you want
    If blnCY = True Then
        oWShell.Run ("""C:\Program Files\Express ClickYes\ClickYes.exe"" -stop")
    End If
    
ExitHere:
    If blnCreatedOL = True Then OL.Quit
    Call TOGGLEEVENTS(True)
    
End Sub
```
The other two sub routines were put into a separate module named 'modFunctions'.


----------



## slurpee55 (Oct 20, 2004)

Zack, perhaps it would be best if you posted the file with the code in all the right modules?


----------



## Zack Barresse (Jul 25, 2004)

If I hear back from botello that it is ok, then yes.


----------



## botello2008 (May 8, 2008)

I got it working now. It is working great. Thank you so much for all your help guys. I really appreciate it. Keep up the good work.


----------



## slurpee55 (Oct 20, 2004)

Well, if you make a bundle on the market, buy Zack a gift!


----------



## Zack Barresse (Jul 25, 2004)

Great! Glad to help botello. Don't forget you can mark this thread as Solved from the Thread Tools menu at the top of the thread.


----------



## Zack Barresse (Jul 25, 2004)

slurpee55 said:


> Well, if you make a bundle on the market, buy Zack a gift!


ROFL!! You're too much slurp.


----------



## botello2008 (May 8, 2008)

I'm starting to invest in the market. For now I am just doing paper trading, I am practicing and learning more until I become kind of an expert in this. I basically wanted to make the process automatic so I didn't need to be checking the computer all the time, this way I will get the alert right at the moment the stock gives me the buying signal. But once I start investing for real and if I make money I promise I will buy you both a gift. Thanks again. Good Work. :up:


----------



## slurpee55 (Oct 20, 2004)

Good luck! In today's market, uh, Extra good luck!!!


----------



## Zack Barresse (Jul 25, 2004)

botello2008 said:


> But once I start investing for real and if I make money I promise I will buy you both a gift. Thanks again. Good Work. :up:


Really, no need. Appreciate the offer though. If you kick anything back, fwd mine to slurpee. :up:


----------



## slurpee55 (Oct 20, 2004)

LOL - no need here either...hey, contribute to the site! 
http://www.techguy.org/donate.html


----------



## drazh (May 28, 2008)

Hello everybody,
I have been reading the previous posts in order to find answers to my troubles but since I am completely new to VB - I will just post a question and hopefully someone could give me some directions...
Ok so...I need to have an automated email alert (sent to me and people assigned to a certain project) everytime a cell in column G reaches value 2 or less (for any of the projects). Sent message should also include (in the body text) current status of a certain project - is this possible? thanks a lot


----------



## Zack Barresse (Jul 25, 2004)

Hi there, and welcome to the board!

You need to start your own thread. If this [thread] looks like what you're looking for as far as a solution is concerned, post a link to it in your own thread. Best not to hijack somebody elses thread.


----------

