title: excel: reference cell value to get email recipient for selected row?
body: <p>I am trying to populate my email to field with the email address stored as a value in cells AF.</p>
  
  <pre><code>AF8 = info@email.com
  AF9 = help@email.com
  AF10 = hello@email.com
  </code></pre>
  
  <p>I am trying to use select range as each AF cell in every row contains a different email address, so when a user has a row selected is should get the appropriate cell value from AF for that row. </p>
  
  <pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
  
      If Target.Column = Range("CA8").Column Then
  
          Dim Email_Subject, Email_Send_From, Email_Send_To, _
            Email_Cc, Email_Bcc, Email_Body As String
          Dim Mail_Object, Mail_Single As Variant
  
          Email_Subject = "New Supplier Set-Up Confirmation"
          Email_Send_From = "purchasing@hewden.co.uk"
          Email_Send_To = Range("AF8").Select
          Email_Cc = "databison@gmail.com"
          Email_Bcc = "databison@gmail.com"
          Email_Body = "Congratulations!!!! You have successfully sent an e-mail using VBA !!!!"
  
          On Error GoTo debugs
          Set Mail_Object = CreateObject("Outlook.Application")
          Set Mail_Single = Mail_Object.CreateItem(0)
          With Mail_Single
              .Subject = Email_Subject
              .To = Range("AF8").Select
              .cc = Email_Cc
              .BCC = Email_Bcc
              .Body = Email_Body
              .send
          End With
  
  debugs:
          If Err.Description &lt;&gt; "" Then MsgBox Err.Description
  
      End If
  End Sub
  </code></pre>
  
  <p>However when I try this code it comes up with the error, 
  "outlook does not recognise one or more names"</p>