title: E-mail Excel range with VBA
body: <p>This code in Excel (Office 365) would add an Excel range or table to a mail body.</p>
  <p>The crucial part is for it to work in the background w/o the mail client (Outlook) being open.</p>
  <p>I have working code (<em>with</em> Outlook in the background). It can only link specific or merged cells to mail body (which essentially means text and not even a simple table).</p>
  <p>When linking a proper range (with let's say three columns and five rows), I get</p>
  <blockquote>
  <p>&quot;Run-time error '13': Type mismatch&quot;</p>
  </blockquote>
  <pre class="lang-vb prettyprint-override"><code>Sub Email()
  
      Dim xOutApp As Object
      Dim xOutMail As Object
      Dim xMailBody As String
  
      Set xOutApp = CreateObject(&quot;Outlook.Application&quot;)
      Set xOutMail = xOutApp.CreateItem(0)
      xMailBody = &quot;E-MAIL BODY TEXT&quot; &amp; vbNewLine &amp; _
      Range(&quot;ARRAY&quot;)
      
      On Error Resume Next
      
      With xOutMail
          .To = &quot;test@mail.com&quot;
          .CC = &quot;&quot;
          .BCC = &quot;&quot;
          .Subject = &quot;Mail subject&quot;
          .Body = xMailBody
          .Send
      End With
      On Error GoTo 0
  
      Set xOutMail = Nothing
      Set xOutApp = Nothing
  
  End Sub
  </code></pre>
  <p>I have other code which is able to grab a range (sort of) from the sheet and send it as a table. It only runs when Outlook is open and running &amp; producing an error message otherwise.</p>
  <pre class="lang-vb prettyprint-override"><code>Sub Email2()
  
      Dim sh As Worksheet
      Set sh = ThisWorkbook.Sheets(&quot;Sheet1&quot;)
  
      Dim lr As Integer
  
      lr = sh.Range(&quot;D&quot; &amp; Application.Rows.Count).End(xlUp).Row
  
      sh.Range(&quot;D5:F&quot; &amp; lr).Select
  
      With Selection.Parent.MailEnvelope.Item
      .to = &quot;test@mail.com&quot;
      .bcc = &quot;&quot;
      .Subject = &quot;&quot;
      .send
      
      End With
  End Sub
  </code></pre>