title: Sending multiple worksheets in same workbook via VBA macro through Outlook Email?
body: <p>I would like to copy multiple worksheets (for example, Sheet71, Sheet76, Sheet60, and Sheet77) that are located within one workbook into another workbook to send in an email to a recipient that is outlined within my email key sheet on Sheet 71.</p>
  
  <p>These emails will be sent to individuals to outline their bonus pay.</p>
  
  <p>Therefore, it is critical that the recipients only receive their own or who they are responsible for.</p>
  
  <p>I have figured out how to send one single worksheet to one recipient, but cannot figure out how to accomplish this with multiple worksheets without using the name on the worksheet (Pierce Group Matrix, Shuff Matrix, Gamble Matrix, and Reed Matrix) versus Sheet71, Sheet76, Sheet60, and Sheet77 in VBA.</p>
  
  <p>I need to be able to reference within the macro to the sheet number rather than the name, because turnover does happen.</p>
  
  <p>Below is the code that I wrote to send an email to one individual in my email key sheet (Sheet81) with one worksheet but it only sends Sheet 71.</p>
  
  <p>I have tried the Array keyword and multiple other keywords but can't seem to get it to work.</p>
  
  <p>I need to reference to the Sheet number rather than the Sheet name because the names are changed when people are replaced.</p>
  
  <p>I would prefer to make a copy like the below code does, but I am open to try a Select command if that will work.</p>
  
  <pre><code>Sub Mail()
  
  Dim OutlookApp As Object
  Dim Mess As Object, Recip
  Recip = Sheet81.[C35].Value
  newDate = MonthName(Month(DateAdd("m", -1, Date)), False)
  
   ' Make a copy of the active worksheet
  ' and save it to a temporary file
  Sheet71.Copy
  Set WB = ActiveWorkbook
  
  Filename = WB.Worksheets(1).Name
  On Error Resume Next
  Kill "C:\" &amp; Filename
  On Error GoTo 0
  WB.SaveAs Filename:="C:\" &amp; Filename
  
  Set OutlookApp = CreateObject("Outlook.Application")
  Set Mess = OutlookApp.CreateItem(olMailItem)
  With Mess
  .Subject = (newDate + " Matrix")
  .Body = ("Attached is your " + newDate + " bonus matrix.  Thanks! Neil")
  .to = Recip
  .Attachments.Add WB.FullName
  .Display
  .Send
  End With
  ActiveWorkbook.Close
  
  Set OutlookApp = Nothing
  Set Mess = Nothing
  End Sub
  </code></pre>