title: Checking a string value to return a range from another worksheet then add to .To line in email
body: <p>I am trying to get email addresses from a worksheet(Sheet1) into the .To line for an outlook email based on the specific string value in the main worksheet.</p>
  <p>I have managed to play with it several ways but none have given the results I need. The idea is that it checks a cell on the main worksheet for a specific string value, then would reference a specific range of cells in one column from another worksheet based on the string value and include these emails in the .To line, separated by a&quot;;&quot;.</p>
  <p>I also noticed it removed data from the cells when it pulled it in testing, replacing some cells with &quot;Column1&quot;</p>
  <pre><code>   Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  
      Dim emailRng As Range, cl As Range
      Dim sTo As String
  
    Set emailRng = Worksheets(&quot;SHEET1&quot;).Range(&quot;D3:D20&quot;)
      
      For Each cl In emailRng
          sTo = sTo &amp; &quot;;&quot; &amp; cl.Value
      Next
      
      sTo = Mid(sTo, 2)
     
      If InStr(ActiveCell.Value, &quot;ABC&quot;) &gt; 0 Then
          emailRng = ThisWorkbook.Sheets(&quot;SHEET1&quot;).Range(&quot;D3:D5&quot;)
             
      ElseIf InStr(ActiveCell.Value, &quot;XYZ&quot;) &gt; 0 Then
          emailRng = ThisWorkbook.Sheets(&quot;SHEET1&quot;).Range(&quot;D11:D15&quot;)
            
      End If
  
      If Target.CountLarge &gt; 1 Then Exit Sub
      Application.ScreenUpdating = False
      Set OutApp = CreateObject(&quot;Outlook.Application&quot;)
      Select Case Target.Column
          Case Is = 15
              Set OutMail = OutApp.CreateItem(0)
              With OutMail
                  .To = sTo
                  .CC = &quot;REQ@EMAIL.COM&quot;
                  .Subject = &quot;&quot;
                  .HTMLBody = &quot;Please attend &quot;
                  .Display
              End With
      End Select
      Application.ScreenUpdating = False
  End Sub
  
  </code></pre>