Search code examples
vbaemailexcelexcel-2013

Cycle through cells, adding those that fit criteria to a list/string using VBA?


I am new to VBA (and have only a bit of training in java), and have hit a wall.

The ultimate goal is to cycle through a range of cells containing names, comparing the value of an adjacent cell to a constant. If conditions are met, the original cells should be added to a list that will be ingested by another formula responsible for drafting an email. This will be repeated daily, and the values coinciding with each name will change.

I have figured out how to cycle through the range using a variable that adds EVERY name to the "TO" line of the email draft, but don't know how to add conditions to the cycle so it only adds the desired ones. Essentially, I believe the only thing I need help with is the "sTo" variable. Thanks!

Sub Draft_Email()

Dim emailRng As Range, cl As Range
Dim adjCellRng As Range, cmp As Range

Dim sTo As String
Dim day As String
Dim EmailBody As String
Dim i As Integer
Dim k As String

k = 85

day = Format(Date, "dddd mmmm d")
EmailBody = "Blah Blah"

Set emailRng = Worksheets("Briefing Order").Range("D4:D31,G4:G31,J4:J31,M4:M31")

For Each cl In emailRng
    **cmp = cl.Offset(0, -1)**
    If cmp >= k Then
        sTo = sTo & ";" & cl.Value & "[email protected]"
    End If
Next cl

sTo = Mid(sTo, 2)

'..Draft Email where .BCC = sTo

End Sub

Solution

  • Set emailRng = Worksheets("Briefing Order").Range("D4:D31,G4:G31,J4:J31,M4:M31")
    Set adjCellRng = Worksheets("Briefing Order").Range("E4:E31,H4:H31,K4:K31,L4:L31")
    
    'something like the following... 
    For i = 1 to emailRng.size 'whatever the limit variable is 
        cl = emailRng(i)
        cmp = adjCellRng(i)
        if cmp==k then
             sTo = sTo & ";" & cl.Value & "[email protected]"
        end if
    Next
    
    sTo = Mid(sTo, 2)