2

Is there a more efficient way to send email reminders to a user based on a cell value that changes frequently? Here's the code of what I'm working on so that you guys could understand the context of the question.

    'This is the main function

Sub notify()          
 Dim rng As Range
 For Each rng In Range("F3:F14")
    If (rng.Value = 1) Then
        Call mymacro
    End If
 Next rng

End Sub
-----------------------------------------------------------------------

  'This is the function that sends an email when called by the main function

  Private Sub mymacro()  
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Hi there" & vbNewLine & vbNewLine & _
              "This is line 1" & vbNewLine & _
              "This is line 2"
    On Error Resume Next
    With xOutMail
        .To = "email address"
        .CC = ""
        .BCC = ""
        .Subject = "test succeeded"
        .Body = xMailBody
        .Display   'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub

Explanation:

Both codes above are located in the same module of my worksheet. The code worked absolutely fine to send an email (through Outlook) to the user. For example, if F3 and F7 evaluate to true, two emails will be sent to the user.

Now, here's the problem, how can I edit my code, such that if the same situation occurs (F3 and F7 evaluate to true), the two email sent to the user would specify which cell evaluates to true. In other words, each email sent would be different in pointing out which specific cell was evaluated to true.

Also, would the code be able to rerun if the data inside the cell ("F3:F14") is updated?

I personally do not have any VBA background, so it would be nice if you guys could explain it in layman's term. Thank you! Really appreciate it! :)

Wen Yao Wen Yao
  • 21

1 Answers1

0

Considering this was asked 2 years ago, I'm sure you've either solved the problem or moved on. But this came to at the top of the unanswered list so... here-goes!

To answer the first question, is there a more efficient way? The portion that you've shared appears to be run manually, meaning the program will not run unless you perform an action. I would try to automate that task with an event driven process. This can be done in a myriad of different ways and I'd need to know about l more about your workflow to gain any significant efficiency.

And the second question, about changing the email. Kinds of an open ended question considering the current state of the code. The short simple explanation is to save the value of the cells to a variable or two and then use that variable in your email.

For example if you had a variable with the horrible name variableX and this variable held the range of F3 then your email could use variableX.

A simple change to add the info:

.Subject = "The cell at " & variableX.address & " is " & variableX. value & " test succeeded"

ProfoundlyOblivious
  • 460