0

I currently have macros built that will execute when a cell value dips below a certain number. My issue, these are live prices, and that number will tick constantly, prompting the macro to run all day. Ideally this macro calls the email macro once a day, given the parameters are met.

This Excel file will sit on a computer that runs 24/7, so no issue in closing and re-opening.

Here is the code for both:

Private Sub Worksheet_Calculate()

If Range("C15").Value < 72 Then
    Call Send_Email
   
    End If
End Sub


Sub Send_Email()
    Dim pApp As Object
    Dim pMail As Object
    Dim pBody As String
    Dim rng As Range
    Set rng = Range("B6:C16")
    Set pApp = CreateObject("Outlook.Application")
    Set pMail = pApp.CreateItem(0)
    On Error Resume Next
    With pMail
        .To = "gmail.com"
        .CC = ""
        .BCC = ""
        .Subject = "Account Action Price Notification"
        .Body = "Hello, our recommended action price for BLANK of $72 has been hit." & vbNewLine & vbNewLine & _
            "Thank you."
        'Below displays the email and allows it to paste
        .Display
         Dim wdDoc As Object     '## Word.Document
         Dim wdRange As Object   '## Word.Range
        Set wdDoc = pMail.GetInspector.WordEditor
        Set wdRange = wdDoc.Range(0, 0)
        wdRange.InsertAfter vbCrLf & vbCrLf
        'Copy the range in-place
        rng.Copy
        wdRange.Paste
        'Below will auto send the email when apostrophe is removed
        '.Send
    End With
    On Error GoTo 0
    Set pMail = Nothing
    Set pApp = Nothing
End Sub

I have tried working solutions from here: VBA Run Macro Once a Day

However, I am unable to get this to properly run.

HunterN
  • 5
  • 2
  • `Worksheet_Change` only runs for manual changes, not if the cell has a formula. How is your workbook changing if it's unattended? – Tim Williams Feb 14 '23 at 19:14
  • Hey Tim, you are right, I wasn't even aware. Another task to tackle, thanks for pointing that out. I had been changing it manually for the purposes of testing it, and when I do link it, it would send, due to the initial change, so I thought all was well. – HunterN Feb 14 '23 at 19:20
  • You may be able to use the `Worksheet_Calculate` event, depending on how the cell is changing – Tim Williams Feb 14 '23 at 19:25
  • I will give that a look. It is linked to a cell that pulls in real time prices from an excel plug in. No 'calculations' are taking place within the cell, just moving with the calculated cell, if that makes sense. – HunterN Feb 14 '23 at 19:43
  • Tim, thanks again for finding that. I have Worksheet_Calculate working. Still working on getting it to Call only once a day. – HunterN Feb 14 '23 at 21:24
  • All examples of code where `On Error Resume Next` is followed by `With pMail` usually `With outMail` are incorrect. https://stackoverflow.com/questions/31753201/vba-how-long-does-on-error-resume-next-work/31753321#31753321 – niton Feb 18 '23 at 13:25

1 Answers1

1

Consider using a cell that logs/updates if the task has been done for the day. For example, populate the cell with a date and time after which the task may run. Then, as part of the code, include a validation if the time is past or not and escapes if it is not later. Once the time comes and the full code is permitted to run, use an additional line at the end to update the cell to the next date (and time, if desired).

RabbitMT
  • 118
  • 6
  • Thanks for the idea rabbit, this worked. I essentially have the initial sub calling a sub every five minutes to time stamp now(), and have a last run time stamp for when Send_email runs. Next, I have now() minus last run and if the cell is >= 1, run again. – HunterN Feb 19 '23 at 16:40