title: How can I send 2 pivot tables (from two sheets) in an Email?
body: <p>I have a excel with 2 sheets, each one has a pivot table.</p>
  
  <p>I want to create a button which automatically send both pivot tables into <strong>one</strong> mail out.</p>
  
  <p>I'm able to write the VBA code to send out one pivot but dont know how to send 2 together.</p>
  
  <p>Here is my code, appreciate the help.</p>
  
  <pre><code>Sub Mail_Selection_Range_Outlook_Body()
  ' You need to use this module with the RangetoHTML subroutine.
  ' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, and Outlook 2010.
      Dim rng As Range
      Dim OutApp As Object
      Dim OutMail As Object
  
      Set rng = Nothing
      On Error Resume Next
      ' Only send the visible cells in the selection.
      Set rng = Sheets("Supporttab").PivotTables(1).TableRange1
  
  
  
      If rng Is Nothing Then
          MsgBox "The selection is not a range or the sheet is protected. " &amp; _
                 vbNewLine &amp; "Please correct and try again.", vbOKOnly
          Exit Sub
      End If
  
      With Application
          .EnableEvents = False
          .ScreenUpdating = False
      End With
  
      Set OutApp = CreateObject("Outlook.Application")
      Set OutMail = OutApp.CreateItem(0)
  
      On Error Resume Next
      With OutMail
          .To = "abc@gmail.com"
          .CC = ""
          .BCC = ""
          .Subject = "report"
          .HTMLBody = RangetoHTML(rng)
          ' In place of the following statement, you can use ".Display" to
          ' display the e-mail message.
          .display
      End With
      On Error GoTo 0
  
      With Application
          .EnableEvents = True
          .ScreenUpdating = True
      End With
  
      Set OutMail = Nothing
      Set OutApp = Nothing
  End Sub
  </code></pre>
  
  <p><strong>RangetoHTML</strong>:</p>
  
  <pre><code>Function RangetoHTML(rng As Range)
  ' Changed by Ron de Bruin 28-Oct-2006
  ' Working in Office 2000-2013
      Dim fso As Object
      Dim ts As Object
      Dim TempFile As String
      Dim TempWB As Workbook
  
      TempFile = Environ$("temp") &amp; "\" &amp; Format(Now, "dd-mm-yy h-mm-ss") &amp; ".htm"
  
      'Copy the range and create a new workbook to past the data in
      rng.Copy
      Set TempWB = Workbooks.Add(1)
      With TempWB.Sheets(1)
          .Cells(1).PasteSpecial Paste:=8
          .Cells(1).PasteSpecial xlPasteValues, , False, False
          .Cells(1).PasteSpecial xlPasteFormats, , False, False
          .Cells(1).Select
          Application.CutCopyMode = False
          On Error Resume Next
          .DrawingObjects.Visible = True
          .DrawingObjects.Delete
          On Error GoTo 0
      End With
  
      'Publish the sheet to a htm file
      With TempWB.PublishObjects.Add( _
           SourceType:=xlSourceRange, _
           Filename:=TempFile, _
           Sheet:=TempWB.Sheets(1).Name, _
           Source:=TempWB.Sheets(1).UsedRange.Address, _
           HtmlType:=xlHtmlStatic)
          .Publish (True)
      End With
  
      'Read all data from the htm file into RangetoHTML
      Set fso = CreateObject("Scripting.FileSystemObject")
      Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
      RangetoHTML = ts.readall
      ts.Close
      RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                            "align=left x:publishsource=")
  
      'Close TempWB
      TempWB.Close savechanges:=False
  
      'Delete the htm file we used in this function
      Kill TempFile
  
      Set ts = Nothing
      Set fso = Nothing
      Set TempWB = Nothing
  End Function
  </code></pre>