title: How to add CC with Lotus Notes email in Excel VBA
body: <p>I have a macro which sends an email to recipients automatically from Excel VBA, I have different columns in my Excel file such as "recipient email address" and "cc", my macro will retrieve data from worksheet and then format accordingly. Now I need to add a "CC" field with two email addresses to my email format and I couldn't figure out how to do that, can anyone help me with that?</p>
  
  <p>Here's how my worksheet looks like:
  <img src="https://i.stack.imgur.com/HNDMR.png" alt="enter image description here"></p>
  
  <p>Here's the entire code for macro:</p>
  
  <pre><code>Sub Send_Unformatted_Rangedata(i As Integer)
  Dim noSession As Object, noDatabase As Object, noDocument As Object
  Dim vaRecipient As Variant
  
  Dim rnBody As Range
  Dim Data As DataObject
  Dim rngGen As Range
  Dim rngApp As Range
  Dim rngspc As Range
  
  y:
  
  Dim stSubject As String
  stSubject = "Change Request " + (Sheets("Summary").Cells(i, "AA").Value) + (Sheets("Summary").Cells(i, "AB").Value) + (Sheets("Summary").Cells(i, "AC").Value) + (Sheets("Summary").Cells(i, "AD").Value) + (Sheets("Summary").Cells(i, "AE").Value) + (Sheets("Summary").Cells(i, "AF").Value) + (Sheets("Summary").Cells(i, "AG").Value) + (Sheets("Summary").Cells(i, "AH").Value) + (Sheets("Summary").Cells(i, "AI").Value)
  'Const stMsg As String = "Data as part of the e-mail's body."
  'Const stPrompt As String = "Please select the range:"
  
  'This is one technique to send an e-mail to many recipients but for larger
  'number of recipients it's more convenient to read the recipient-list from
  'a range in the workbook.
  vaRecipient = VBA.Array(Sheets("Summary").Cells(i, "U").Value, Sheets("Summary").Cells(i, "V").Value)
  
  On Error Resume Next
  'Set rnBody = Application.InputBox(Prompt:=stPrompt, _
       Default:=Selection.Address, Type:=8)
  'The user canceled the operation.
  'If rnBody Is Nothing Then Exit Sub
  Set rngGen = Nothing
  'Set rngApp = Nothing
  'Set rngspc = Nothing
  
  Set rngGen = Sheets("General Overview").Range("A1:C30").SpecialCells(xlCellTypeVisible)
  'Set rngApp = Sheets("Application").Range("A1:E13").SpecialCells(xlCellTypeVisible)
  
  'Set rngspc = Sheets(Sheets("Summary").Cells(i, "P").Value).Range(Sheets("Summary").Cells(i, "Q").Value).SpecialCells(xlCellTypeVisible)
  'Set rngspc = Union(rngspc, Sheets(Sheets("Summary").Cells(i, "P").Value).Range(Sheets("Summary").Cells(i, "R").Value).SpecialCells(xlCellTypeVisible))
  
  On Error GoTo 0
  
  If rngGen Is Nothing And rngApp Is Nothing And rngspc 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
  
  
  'Instantiate Lotus Notes COM's objects.
  Set noSession = CreateObject("Notes.NotesSession")
  Set noDatabase = noSession.GETDATABASE("", "")
  
  'Make sure Lotus Notes is open and available.
  If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
  
  'Create the document for the e-mail.
  Set noDocument = noDatabase.CreateDocument
  
  'Copy the selected range into memory.
  'The clipboard will get replaced by the multiple copies.
  'rngApp.Copy
  'rngspc.Copy
  rngGen.Copy
  
  'To be able to see the email and manually send it add this below
   'Call oUIDoc.Save(True, False, False)
   'CreateObject("Notes.NotesUIWorkspace").EDITDOCUMENT True, oUIDoc
   'AppActivate "&gt; " &amp; oUIDoc.Subject
  
  'Retrieve the data from then copied range.
  Set Data = New DataObject
  Data.GetFromClipboard
  
  'Add data to the mainproperties of the e-mail's document.
  With noDocument
    .Form = "Memo"
    .SendTo = vaRecipient
  
    .Subject = stSubject
    'Retrieve the data from the clipboard.
    .Body = Data.GetText &amp; " " &amp; stMsg
    .SAVEMESSAGEONSEND = True
  End With
  
  'Send the e-mail.
  'changed by Xu Ying to make the email being sent from automatically to manually
  
      Dim uiMemo As Object
      Dim ws As Object
      Set ws = CreateObject("Notes.NotesUIWorkspace")
      noDocument.Save True, True, False
      Set uiMemo = ws.EDITDOCUMENT(True, noDocument)
  
  'Release objects from memory.
  Set noDocument = Nothing
  Set noDatabase = Nothing
  Set noSession = Nothing
  
  
  'Activate Excel for the user.
  
  'AppActivate "Excel"
  
  'Empty the clipboard.
  Application.CutCopyMode = False
  
  i = i + 1
  If Sheets("Summary").Cells(i, "U").Value &lt;&gt; "" Then
  GoTo y:
  End If
  
  MsgBox "The e-mail has successfully been created and distributed.", vbInformation
  
  End Sub
  
  Sub Send_Formatted_Range_Data(i As Integer)
  Dim oWorkSpace As Object, oUIDoc As Object
  Dim rnBody As Range
  Dim lnRetVal As Long
  Dim stTo As String
  
  Dim stSubject As String
  Const stMsg As String = "An e-mail has been succesfully created and saved."
  
  Dim rngGen As Range
  Dim rngApp As Range
  Dim rngspc As Range
  
  stTo = Sheets("Summary").Cells(i, "U").Value
  
  stSubject = "E-Mail For Approval for " + (Sheets("Summary").Cells(i, "A").Value) + "  for the Project  " + Replace(ActiveWorkbook.Name, ".xls", "")
  
  'Check if Lotus Notes is open or not.
  lnRetVal = FindWindow("NOTES", vbNullString)
  
  If lnRetVal = 0 Then
    MsgBox "Please make sure that Lotus Notes is open!", vbExclamation
    Exit Sub
  End If
  
  Application.ScreenUpdating = False
  
  Set rngGen = Sheets("General Overview").Range("A1:C30").SpecialCells(xlCellTypeVisible)
  Set rngApp = Sheets("Application").Range("A1:E13").SpecialCells(xlCellTypeVisible)
  
  Set rngspc = Sheets(Sheets("Summary").Cells(i, "P").Value).Range(Sheets("Summary").Cells(i, "Q").Value).SpecialCells(xlCellTypeVisible)
  Set rngspc = Union(rngspc, Sheets(Sheets("Summary").Cells(i, "P").Value).Range(Sheets("Summary").Cells(i, "R").Value).SpecialCells(xlCellTypeVisible))
  On Error GoTo 0
  
  If rngGen Is Nothing And rngApp Is Nothing And rngspc 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
  
  rngGen.Copy
  rngApp.Copy
  rngspc.Copy
  
  'Instantiate the Lotus Notes COM's objects.
  Set oWorkSpace = CreateObject("Notes.NotesUIWorkspace")
  
  On Error Resume Next
  
  Set oUIDoc = oWorkSpace.ComposeDocument("", "mail\xldennis.nsf", "Memo")
  On Error GoTo 0
  
  Set oUIDoc = oWorkSpace.CurrentDocument
  
  'Using LotusScript to create the e-mail.
  Call oUIDoc.FieldSetText("EnterSendTo", stTo)
  Call oUIDoc.FieldSetText("EnterCopyTo", stCC)
  Call oUIDoc.FieldSetText("Subject", stSubject)
  
  'The can be used if You want to add a message into the created document.
  Call oUIDoc.FieldAppendText("Body", vbNewLine &amp; stBody)
  
  'Here the selected range is pasted into the body of the outgoing e-mail.
  Call oUIDoc.GoToField("Body")
  Call oUIDoc.Paste
  
  'Save the created document.
  Call oUIDoc.Save(True, False, False)
  'If the e-mail also should be sent then add the following line.
  'Call oUIDoc.Send(True)
  
  'Release objects from memory.
  Set oWorkSpace = Nothing
  Set oUIDoc = Nothing
  
  With Application
    .CutCopyMode = False
    .ScreenUpdating = True
  End With
  
  MsgBox stMsg, vbInformation
  
  'Activate Lotus Notes.
  AppActivate ("Notes")
  'Last edited Feb 11, 2015 by Peter Moncera
  
  End Sub
  </code></pre>