0

I am trying to send the mails to multiple people using below code. It contain recipients for To and CC field. I am facing one issue in the below code. While running the macro all the CC recipients also getting merged with To recipients list. Please help to improve the code. I have also attached the table for reference.

Table for reference 

Option Explicit
Public Sub Example()
   Dim olApp As Object
   Dim olMail As Object
   Dim olRecip As Object
   Dim olCopy As Object
   Dim olAtmt As Object
   Dim iRow As Long
   Dim Recip As String
   Dim Copy As String
   Dim Subject As String
   Dim Body As String
   Dim Atmt As String

   iRow = 2

   Set olApp = CreateObject("Outlook.Application")

   Dim Sht As Worksheet
   Set Sht = ThisWorkbook.Worksheets("Sheet1")

   Do Until IsEmpty(Sht.Cells(iRow, 1))

      Recip = Sht.Cells(iRow, 1).Value
      Copy = Sht.Cells(iRow, 2).Value
      Subject = Sht.Cells(iRow, 3).Value
      Body = Sht.Cells(iRow, 4).Value
      Atmt = Sht.Cells(iRow, 5).Value ' Attachment Path

      Set olMail = olApp.CreateItem(0)

      With olMail
            .To = .Recipients.Add(Recip)
            .CC = .Recipients.Add(Copy)
            .Subject = Subject
            .Body = Body
            .Display
        Set olAtmt = .Attachments.Add(Atmt)
            olCopy.Resolve
      End With

      iRow = iRow + 1

   Loop

   Set olApp = Nothing
End Sub
Pankaj Kumar
  • 1
  • 1
  • 1
    You are setting the To and CC string properties to an object (Recipient) returned by Recipients.Add,c which makes no sense. If you set the CC property, it must be set to a string of ";" separated addresses. If you are calling Recipeints.Add, set the Recipient.Type property on the returned object to olCC. – Dmitry Streblechenko Jul 22 '21 at 18:16

0 Answers0