Build a String Based on Adjacent Cell Value, in lieu of Checkboxes-CodePudding

I'm trying to build a string for the TO: field of an Outlook email from 2 columns in an Excel sheet. In column A, users may type or select from a dropdown a "yes" or "no" that should indicate which person in column B should receive the email. For now, I did it this way instead of trying checkboxes, which seemed more challenging. I have code below that actually works to build a string for TO:, but I can't figure out how to code the "yes" and "no" for a conditional on/off. Currently, the email is being sent to everyone in col B. Any help on fixing the if statement and row loop would be greatly appreciated. Thanks so much!

colA  |  colB
yes   |  [email protected]
no    |  [email protected]
yes   |  [email protected]
no    |  [email protected]
no    |  [email protected]
no    |  [email protected]

Dim cell As Range, studentCell As Range, ci As Long, str As String 'all used for other code
Dim emailRng As Range, cl As Range, ce As Range
Dim sTo As String
Set emailRng = Worksheets("EmailRecipients").Range("B1:B10")
Dim yesno As Range
Set yesno = Worksheets("EmailRecipients").Range("A1:A10")


    For Each cell In yesno.Cells
        yesnostr = cell.Value
        If (yesnostr = "Yes" Or yesnostr = "yes") Then
        
    For Each cl In emailRng
    sTo = sTo & ";" & cl.Value
    Next
    End If
    Next cell


'VARIOUS OTHER CODE HERE TO BUILD A DIFFERENT STR FOR THE EMAIL BODY
'THANKS TO TIM WILLIAMS, DAVID, SHROTTER FOR CODE
   
'OUTLOOK EMAIL
Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    With OutMail
        .To = sTo 'THE EMAIL RECIPIENTS WILL BE PUT HERE. 
        .CC = ""
        .BCC = ""
        .Subject = "Missing assignments report for lunches and more, " & Date & ", Q2"
        
        '****************************************************************************
        Dim wdDoc As Object
        Dim olinsp As Object
        
        Set wdDoc = CreateObject("Word.Document")
        Set olinsp = .GetInspector
        Set wdDoc = olinsp.WordEditor
        
        If Not IsEmpty(str) Then
            wdDoc.Range.InsertBefore str
        Else
            MsgBox prompt:="No cells meet the criteria"
            GoTo SafeExit
        End If
        '****************************************************************************
        .Display
        .Send
    End With
    
    Set OutMail = Nothing
    Set OutApp = Nothing
    Set wdDoc = Nothing
    Set olinsp = Nothing
    str = Empty
    
SafeExit:
    On Error Resume Next
        
        If Not Application.EnableEvents Then
            Application.EnableEvents = True
            Application.ScreenUpdating = True
        End If
    
    On Error GoTo 0
    Exit Sub

ClearError:
    Debug.Print "Run-time error'" & Err.Number & "': " & Err.Description
    Resume SafeExit
    
End Sub

'A DOUBLE CLICK OF A CELL IN ANOTHER SHEET STARTS THE OUTLOOK EMAIL PROCESS
Public Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Call ThisWorkbook.Check_Outlook
 
    With Target
       Select Case .Address
          Case "$A$1":
          Call Q2_Email_Missing_Assignments
      End Select
   End With

   Cancel = True
End Sub

CodePudding user response：

In the code you provided, for the logic that checks whether yesnostr is a yes or no, the outcome for a yes is then to set sTo to the whole of emailRng (it cycles through every cell during For Each cl In emailRng...) . So if any of them are a yes, all email addresses are added regardless - and this happens every time there's a yes.

A simple fix would just be to get the email address next to the yes and add that to your list as you iterate through it:

For Each cell In yesno.Cells
    If (LCase(cell.Value) = "yes") Then
        sTo = sTo & ";" & cell.Offset(0, 1).Value
    End If
Next cell

Offset(0,1) is referring to the cell 0 rows down and 1 column to the right.
LCase() just takes the lower case value of the cell, so you can check it just the once. It also means it will work for yEs, yES etc..

Further reading:
https://learn.microsoft.com/en-us/office/vba/api/excel.range.offset
https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/lcase-function