Add .CC to my "Mail a message to each person in a range"

[Victorius]

Hello,

I am working on a big email dispatch.

I am also using as a reference this link below
Mail a message to each person in a range

I can get a new email for every "To" email like "?*@?*.?*" in column B, but I do not find a way to add the CC that is written in column C.

I already defined the range for the "To" from column B field here <dim cell="" as="" range="">, now
I would need to define the range for "CC" field from column C

This is my first post as I am quite new in VBA. I would appreciate a lot any kind of help

This is the code ,I have.

******************************************

Sub Class()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
Sheets("(3) Macro email").Select
On Error GoTo cleanup
For Each cell In Range("B3:B250").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And _
LCase(Cells(cell.Row, "D").Value) = "yes" Then


Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Subject - " & Cells(cell.Row, "E")


.Body = "blahblah" & Cells(cell.Row, "A") & "," _
& vbNewLine & vbNewLine & _
"blahblahblahblah" & Cells(cell.Row, "E") & "blahblahblah" & Cells(cell.Row, "F") & "blah" _

.display
End With
On Error GoTo 0
Set OutMail = Nothing
End If
Next cell


cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

*************************************************</dim>

 

[timfritsch]

Here's how I've done it:

Dim ToContact As Outlook.Recipient

Set ToContact = .Recipients.Add("Doe, John")           'add name in the To field

Set ToContact = .Recipients.Add("Doe, Jane")           'add name in the cc field
ToContact.Type = olCC

Tim

 

[Victorius]

Thank you very much Tim for your help. Sadly I think this suggestion will not work as I have an Excel with 250 emails in the "To" list and another 250 emails on the "CC".

the list will change weekly and it would take me longer to type it the VBA editor.
Thank you again, talk to you soon

 

[timfritsch]

Try changing from "Doe, John" to cells(cell.row,"B"). I haven't tested, but its worth a shot. If the .To name works, then cells(cells.row,"C") should work for the cc.

Hope this helps.


Tim

 

[daverunt]

You can also use the offset to the cell. No need to define another range.

.To = cell.Value 
.cc = cell.Offset(0, 1).Value