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