PDA

View Full Version : Solved: Emailing CC. recipients



Anomandaris
04-02-2009, 08:04 AM
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




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



Any ideas?

Thanks

nirvana_1
04-02-2009, 08:16 AM
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
04-03-2009, 06:40 AM
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
04-03-2009, 06:52 AM
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.

Anomandaris
04-03-2009, 08:52 AM
yup it sure works great XLD!
you got it again, thanksssssssssssssssss