title: VBA Excel: Insert an extract of a spreadsheet into an Outlook email
body: <p>I want to be able to run a macro in Excel that copies specific areas of my excel spreadsheet, opens a new email, and pastes the areas in. So far I have got the following code:</p>
  
  <pre><code>Sub Macro2()
  '
  'Macro2 Macro
  Dim objOutlook As Object
  Dim objMail As Object
  Dim TempFilePath As String
  Set objOutlook = CreateObject("Outlook.Application")
  Set objMail = objOutlook.CreateItem(0)
  
  Dim rng As Range
  'Set rng = Selection.SpecialCells(xlCellTypeVisible)
  Set rng = ActiveSheet.Range("A4:E200").Rows.SpecialCells(xlCellTypeVisible)
  
  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 objMail
  
      ActiveSheet.Range("$A$3:$P$197").AutoFilter Field:=4, Criteria1:="10"
      Range("B171:F184,I171:J184").Select
      Range("I171").Activate
      Range("B171:F184,I171:J184,M171:N184").Select
      Range("M171").Activate
      Selection.Copy
  
  
      .To = ""
      .CC = ""
      .Subject = ""
      .HTMLBody = RangetoHTML(rng)
      .Display
  End With
  
  Set objOutlook = Nothing
  Set objMail = Nothing
  
  End Sub
  
  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>
  
  <p>This code almost does what I want, it copies filters on criteria 10 in column D and copies and pastes this into an email.</p>
  
  <p>But what I want is for the code to go through the filtered cells (could be criteria 1, 2, 3... etc. rather than a hard coded criteria as it is at the minute), and copy &amp; paste specific columns into the email.</p>
  
  <p>For example, I want to filter on Queen in the Band column below and copy &amp; paste the columns C (Full Name) and D (Band) into an email.</p>
  
  <pre><code>     A            B              C               D             
   1   First name:  Last name:     Full Name:      Band:
   2   Freddie      Mercury        Freddie Mercury Queen
   3   Brian        May            Brian May       Queen
   4   Kurt         Cobain         Kurt Cobain     Nirvana
   5   Roger        Taylor         Roger Taylor    Queen
   6   Dave         Grohl          Dave Grohl      Nirvana
   7   John         Deacon         John Deacon     Queen
   8   Kris         Novoselic      Kris Novoselic  Nirvana
  </code></pre>
  
  <p>Is there a way of doing this? Any help would be appreciated.</p>