title: Improve process of taking email address from spreadsheet
body: <p>I have a spreadsheet that I have set up to automatically pdf and email nightly based on email addresses I have listed out on a hidden worksheet.  I currently have to dim seperate variable for each address and then specify which cell each variable equals.  This works but I feel like there must be a better way to do this.  Specifically, I would like to not have to add or delete dim'ed variables if I delete or add additional addresses to the list.  Here is the code I am using:</p>
  
  <pre><code>Sub PDF_Email()
  
  
  Dim OutLookApp As Object
  Dim OutLookMailItem As Object
  Dim myAttachment As Object
  Dim MDir As String
  Dim MName As String
  Dim Address1 As String
  Dim Address2 As String
  Dim Address3 As String
  Dim Address4 As String
  Dim Address5 As String
  Dim Address6 As String
  Dim Address7 As String
  Dim Address8 As String
  Dim Address9 As String
  Dim Address10 As String
  Dim Address11 As String
  
  
  Set OutLookApp = CreateObject("Outlook.application")
  Set OutLookMailItem = OutLookApp.CreateItem(0)
  Set myAttachment = OutLookMailItem.Attachments
  
  Address1 = Worksheets("EmailList").Cells(1, 1).Value
  Address2 = Worksheets("EmailList").Cells(2, 1).Value
  
  
  'Prevent Macro from running if different user
  Const AllowedName As String = "nbelair"
  
  If Environ("username") &lt;&gt; AllowedName Then
      Exit Sub
  End If
  
  
  MName = ActiveSheet.Name &amp; " " &amp; Format(Now() - 1, "dddd, mmmm, d, yyyy")
  MDir = ActiveWorkbook.Path
  
  ChDir "Y:\SMHC Management Team\Daily Labor Management\Dashboard\Archived 
  Dashboards" 'Update to
  ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
  "Y:\SMHC Management Team\Daily Labor Management\Dashboard\Archived  
  Dashboards\" &amp; MName &amp; ".pdf", OpenAfterPublish:=True 'Update
  
  
  
  With OutLookMailItem
  .To = Address1 &amp; ";" &amp; Address2
  .Subject = "SMHC Daily Labor Management Dashboard - " &amp; Format(Now() - 1, 
  "dddd, mmmm, d, yyyy")
  .Body = "Attached please find the SMHC Daily Labor Management Dashboard for 
  " _
      &amp; Format(Now() - 1, "dddd, mmmm, d, yyyy") &amp; ".  You are receiving this 
  email because you are currently " _
      &amp; "on the distribution list for this report.  If you have any questions 
  " _
      &amp; "or concerns regarding this email or report please let me know by 
  responding to this email or contacting me at 207 467 6983."
  
  myAttachment.Add "Y:\SMHC Management Team\Daily Labor 
  Management\Dashboard\Archived Dashboards\" &amp; MName &amp; ".pdf"
  .Display
  .Send
  End With
  
  'Clear Outlook Variables
  Set OutLookMailItem = Nothing
  Set OutLookApp = Nothing
  
  'Quit
  ThisWorkbook.Saved = True
  Application.Quit
  
  End Sub
  </code></pre>
  
  <p>Being new to coding, I would greatly appreciate any thoughts or suggestions someone might have.  I am quickly falling in love with coding and welcome the chance to learn something new!</p>
  
  <p>Thank You</p>