title: Sending Outlook Email with multiple recipients from Excel file
body: <p>I am currently doing a VBA-macro that would send a SINGLE outlook email having the following criteria:</p>
  
  <p>A. The recipients are listed in column D of Sheet1 and all I want is to concatenate each sender in TO field of outlook. However, these recipients are dynamic and could be different in terms of number. Cases may lead to adding or subtracting email addresses from these column.</p>
  
  <p>B. I need to paste whatever the content of Sheet2 in the BODY field of outlook.
  C. I need to generate an email with signature. </p>
  
  <p>So far, I have this code but it's not working though:</p>
  
  <pre><code>Option Explicit
  
  Sub SendEmail()
  
  Dim OutlookApplication As Outlook.Application
  Dim OutlookMailItem As Outlook.MailItem
  Dim outlookInspector As Outlook.Inspector
  Dim wdDoc As Word.Document
  Dim Recipient As Range
  Dim CC As Range
  
  Application.ScreenUpdating = False
  
  Set OutlookApplication = New Outlook.Application
  Set OutlookMailItem = OutlookApplication.CreateItem(0)
  
  'On Error GoTo cleanup
  
      Workbooks("ConfigFile.xlsm").Sheets("Sheet1").Activate
  
      Range("D2").Select
      Set Recipient = Range(ActiveCell, ActiveCell.End(xlDown))
  
      Range("E2").Select
      Set CC = Range(ActiveCell, ActiveCell.End(xlDown))
  
      With OutlookMailItem
          .Display
          .To = Recipient
          .CC = CC
          .subject = ThisWorkbook.Sheets("Sheet1").Range("F2").Value
          .Body = ThisWorkbook.Sheets("Sheet1").Range("G2").Value
  
          Set outlookInspector = .GetInspector
          Set wdDoc = outlookInspector.WordEditor
  
          wdDoc.Range.InsertBreak
  
          Sheet2.Activate
          Range("A:A").CurrentRegion.Copy
  
          wdDoc.Range.Paste
  
      End With
  
  
  'cleanup:
      'Set OutlookApplication = Nothing
      'Application.ScreenUpdating = True
  
  End Sub
  </code></pre>