[botello2008]

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]

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]

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]

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]

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]

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]

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]

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...b;en-us;278973

[Zack Barresse]

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 = "your@addressHere.com"
    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]

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

[botello2008]

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]

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]

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

[slurpee55]

Originally Posted by botello2008

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]

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

Regards,
Rollin