Solved: Emailing CC. recipients

[Anomandaris]

Hi guys

I have a macro thanx to an expert here(xld). It sends emails to multiple recipients, now I need to add a line that will enable it to CC. the email to recipients listed in the first Row (Sheet1 - Row 1).

Here's the code



[VBA]
Sub SendMailNEST()
Const MatchCode As String = "NEST"
Dim wb As Workbook
Dim LastRow As Long
Dim LastCol As Long
Dim OLApp As Object
Dim EmailItem As Object
Dim EmailRecip As Object
Dim j As Long



With Worksheets("Sheet1")
Worksheets("Sheet2").Copy

Set wb = ActiveWorkbook
wb.SaveAs "Part of" & ThisWorkbook.Name & "" & ".xls"
wb.ChangeFileAccess xlReadOnly

Set OLApp = CreateObject("Outlook.Application")
Set EmailItem = OLApp.CreateItem(0)

EmailItem.Subject = "Recap"
LastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
For j = 2 To LastCol

Set EmailRecip = EmailItem.Recipients.Add(.Cells(2, j).Value)
EmailRecip.Type = 1
Next j
EmailItem.Body = "Hi, " & .Cells(2, 1).Value & ", here is today's summary"
EmailItem.Attachments.Add wb.FullName
EmailItem.Display 'Send

Kill wb.FullName

Set wb = Nothing
Set EmailItem = Nothing
Set OLApp = Nothing
End With
End Sub
[/VBA]


Any ideas?

Thanks

[nirvana_1]

just include one more line like this in your code. marked in red. it assumes that the cell is A1 in that existing sheet.

Set EmailRecip = EmailItem.Recipients.Add(.Cells(2, j).Value) 
            EmailRecip.Type = 1 
        Next j 
        EmailIteam.CC=cells(1,1).value
        EmailItem.Body = "Hi, " & .Cells(2, 1).Value & ", here is today's summary" 
        EmailItem.Attachments.Add wb.FullName 
        EmailItem.Display 'Send

[Anomandaris]

Hey thaks nirvana,

I tried it but that doesnt work

I did
EmailItem.CC = cells(1,j).Value

as it has to be CC'd to all the addresses in Row 1.

any suggestions to adjust it?

[Bob Phillips]

[vba]

Set oRecipient = _
oMailItem.Recipients.Add("someone@somewhere.com")
oRecipient.Type = 1 '1 = To, use 2 for cc
'keep repeating these lines with
'your names, adding to the collection.
[/vba]

[Anomandaris]

yup it sure works great XLD!
you got it again, thanksssssssssssssssss