[botello2008]

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]

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]

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

[Zack Barresse]

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]

Originally Posted by botello2008

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]

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]

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 = "8322983517@messaging.nextel.com"
    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]

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

[Zack Barresse]

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

[botello2008]

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]

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

[Zack Barresse]

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]

Originally Posted by slurpee55

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

ROFL!! You're too much slurp.

[botello2008]

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.

[slurpee55]

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