title: Excel VBA: Copy Two Separate Ranges with Union
body: <p>I'm trying to copy the header row and a specific row of a table with my loop. I then paste these two rows into Outlook, all using VBA. I'm trying to always copy Row 1, then copy Row 2 and use a union to combine them. Then, copy row 1 and row 3, then row 1 and row 4, etc. The problem is that Excel is copying the range of rows between as well. So it will copy Row 1 and Row 2, then Row 1, Row 2, and Row 3, etc.</p>
  
  <pre><code>Desired Result (each of these being a separate email):
  
  Header Row
  Row 2
  
  Header Row
  Row 3
  
  Header Row
  Row 4
  
  Actual Result:
  
  Header Row
  Row 2
  
  Header Row
  Row 2
  Row 3
  
  Header Row
  Row 2
  Row 3
  Row 4
  </code></pre>
  
  <p>I have tried doing two separate copy pastes to completely avoid the union, but this destroys the formatting.</p>
  
  <p>Code</p>
  
  <pre><code>Sub email2()
  
  Dim outlook As Object
  Dim newEmail As Object
  Dim xInspect As Object
  Dim pageEditor As Object
  
  Dim tbl As ListObject
  Set tbl = ActiveSheet.ListObjects("Table2")
  TableRows = tbl.ListRows.Count
  MsgBox TableRows
  
  For x = 1 To tbl.Range.Rows.Count
  
      Set outlook = CreateObject("Outlook.Application")
      Set newEmail = outlook.CreateItem(0)
  
      With newEmail
          .To = Cells(x + 1, 36)
          .CC = ""
          .BCC = ""
          .Subject = "Data"
          .Body = "Please find the requested information" &amp; vbCrLf &amp; "Best Regards"
          .Display
  
          Set xInspect = newEmail.GetInspector
          Set pageEditor = xInspect.WordEditor
  
          'Table header'
          Set range1 = Sheet2.Range(Cells(1, 8), Cells(1, 36))
          Set range2 = Sheet2.Range(Cells(x + 1, 8), Cells(x + 1, 36))
  
          dblRange = Union(range1, range2).Copy
  
          'Sheet2.Range(Cells(x + 1, 8), Cells(x + 1, 36)).Copy'
  
          pageEditor.Application.Selection.Start = Len(.Body)
          pageEditor.Application.Selection.End = pageEditor.Application.Selection.Start
          pageEditor.Application.Selection.PasteAndFormat (wdFormatPlainText)
  
          .Display
          '.Send'
          Set pageEditor = Nothing
          Set xInspect = Nothing
  
      End With
  
  
  Next x
  
  End Sub
  </code></pre>
  
  <p>Thank you very much for any help,</p>
  
  <p>Nash</p>