mardi 16 janvier 2018

Sending specific email based on cell values in Excel 2016

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! :)

Aucun commentaire:

Enregistrer un commentaire