title: How do I send Excel data (multiple individual ranges) seperated by carriage returns to an Outlook mail body
body: <p>I'm trying to send certain sections of an excel file into an Outlook mail body.</p>
  
  <p>I need the formatting of the data, as I am working with data inside tables and with different cell fill colours and font colours, so it can't be stored in a string AFAIK.</p>
  
  <p>I need carriage returns to separate the tables being pasted into outlook so that other text can be manually added to the email body in between the tables without distorting the table formatting.</p>
  
  <p>The code below, shows what needs to be done but wont work as it returns a runtime error 13, type mismatch on the ".HTMLBody" line. I've spend a long time trying different ways to do this, but this is the way I need it to work I just don't know which data types to use and how to properly do it.</p>
  
  <p>Keep in mind that in both examples of my code below I have cut out most of the data range pasting because it would be redundant code.</p>
  
  <pre><code>Sub sendToOutlook()
      Dim OutApp As Object
      Dim OutMail As Object
  
      Dim bodyFieldA As Range
      Dim bodyFieldB As Range
  
      On Error Resume Next
      Set OutApp = GetObject(, "Outlook.Application")
      If Err &lt;&gt; 0 Then Set OutApp = CreateObject("Outlook.Application")
          On Error GoTo 0
  
      Set OutMail = OutApp.CreateItem(0)
  
      With OutMail
          .To = Range("L18").Value
          .CC = Range("L19").Value
          .BCC = Range("L20").Value
          .Subject = Range("L1") &amp; " " &amp; Range("N1").Text _
                     &amp; " " &amp; Range("O1") &amp; " " &amp; Range("R1").Text _
                     &amp; " " &amp; Range("S1")
  
          Set bodyFieldA = Range("A26:I33")
          Set bodyFieldB = Range("A34:I34")
  
          .HTMLBody = bodyFieldA + vbCrLf + bodyFieldB + "&lt;HTML&gt;&lt;body&gt;&lt;body&gt;&lt;/HTML&gt;"
          .display
      End With
  
      Application.CutCopyMode = False
      Range("A1").Select
  
      Set OutMail = Nothing
      Set OutApp = Nothing
  End Sub
  </code></pre>
  
  <p>My old version only works if Outlook has already been given focus by the user once, otherwise the "sendkeys" which I use instead of carriage returns are sent to excel instead, ruining the worksheet data. </p>
  
  <p>Also, if the ".TO" field is left blank the "sendkeys" are sent there instead of the email body.</p>
  
  <p>I need to fix this problem, so the above code is my attempt at a solution for it, while the code below is my old code that does the job but with a lot of Band-Aid work and problems less experienced users who will be using the macro will not be able to deal with.</p>
  
  <pre><code>Sub sendToOutlook()
      Dim OutApp As Object
      Dim OutMail As Object
      Dim olInsp As Object
      Dim wdDoc As Object
      Dim oRng As Object
  
      On Error Resume Next
      Set OutApp = GetObject(, "Outlook.Application")
      If Err &lt;&gt; 0 Then Set OutApp = CreateObject("Outlook.Application")
          On Error GoTo 0
  
      Set OutMail = OutApp.CreateItem(0)
  
      With OutMail
          .To = Range("L18").Value
          .CC = Range("L19").Value
          .BCC = Range("L20").Value
          .Subject = _
              Range("L1") &amp; " " &amp; Range("N1").Text _
              &amp; " " &amp; Range("O1") &amp; " " &amp; Range("R1").Text _
              &amp; " " &amp; Range("S1")
  
          Set olInsp = .GetInspector
          Set wdDoc = olInsp.WordEditor
          Set oRng = wdDoc.Range
  
          'force html format
          .HTMLBody = "&lt;HTML&gt;&lt;body&gt;&lt;body&gt;&lt;/HTML&gt;"
          .display
  
          oRng.collapse 1
          Range("A26:I33").Select
          Selection.Copy
          oRng.Paste
          SendKeys "{ENTER}", True
  
          oRng.collapse 1
          Range("A34:I34").Select
          Selection.Copy
          oRng.Paste
          SendKeys "{ENTER}", True
      End With
  
      'deselect cell range
      Application.CutCopyMode = False
      Range("A1").Select
  
      Set OutMail = Nothing
      Set OutApp = Nothing
      Set olInsp = Nothing
      Set wdDoc = Nothing
      Set oRng = Nothing
  End Sub
  </code></pre>