VBA code to send automated emails to more than one cc

[Reecenorman1996]

Hi need help editing the below code to let me send the email to 2 people in cc in 2 columns at the moment its gets the cc form column k, but i want it to get email addresses from j and k and have 2 people in copy

thanks in advance

Sub Send_Bulk_Mails()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("sheet1")
Dim i As Integer

Dim OA As Object
Dim msg As Object

Set OA = CreateObject("outlook.application")

Dim last_row As Integer
last_row = Application.CountA(sh.Range("c:c"))

For i = 2 To last_row
Set msg = OA.createitem(0)
msg.to = sh.Range("i" & i).Value
msg.cc = sh.Range("k" & i).Value
msg.Subject = sh.Range("m" & i).Value
msg.Body = sh.Range("n" & i).Value

msg.send

sh.Range("p" & i).Value = "Sent"

Next i

MsgBox "All emails have been sent"

End Sub

 

[rpaulson]

change:
msg.cc = sh.Range("k" & i).Value

to:
msg.cc = sh.Range("k" & i).Value & ";" & sh.Range("j" & i).Value

 

[Reecenorman1996]

change:
msg.cc = sh.Range("k" & i).Value

to:
msg.cc = sh.Range("k" & i).Value & ";" & sh.Range("j" & i).Value

doesn't work just uses the email in column any ideas ?

 

[Reecenorman1996]

change:
msg.cc = sh.Range("k" & i).Value

to:
msg.cc = sh.Range("k" & i).Value & ";" & sh.Range("j" & i).Value

it does work i just had wrong column thank you

 

[PugradorTX]

Hello! I am working with that same code and was wondering if the previous line is only meant for one recipient:
msg.cc = sh.Range("k" & i).Value

And if the subsequent code will send to multiple people as long as their addresses are separated by a ";"
msg.cc = sh.Range("k" & i).Value & ";" & sh.Range("j" & i).Value

 

[Reecenorman1996]

Hello! I am working with that same code and was wondering if the previous line is only meant for one recipient:
msg.cc = sh.Range("k" & i).Value

And if the subsequent code will send to multiple people as long as their addresses are separated by a ";"
msg.cc = sh.Range("k" & i).Value & ";" & sh.Range("j" & i).Value

I use in 2 columns so one email in column k and one in J like below

AML Audit.xlsm
	G	H	I	J	K	L	M	N		P
1	Extra info input	Email template	email to	emailcc	3 and over cc	subject	body	Email sent		add email
2						- -	Additional Information - None Many thanks, Reece
3						- -	Additional Information - None Many thanks, Reece
4						- -	Additional Information - None Many thanks, Reece
5						- -	Additional Information - None Many thanks, Reece
6						- -	Additional Information - None Many thanks, Reece
7						- -	Additional Information - None Many thanks, Reece
8						- -	Additional Information - None Many thanks, Reece
9						- -	Additional Information - None Many thanks, Reece
10						- -	Additional Information - None Many thanks, Reece
11						- -	Additional Information - None Many thanks, Reece
12						- -	Additional Information - None Many thanks, Reece
13						- -	Additional Information - None Many thanks, Reece
14						- -	Additional Information - None Many thanks, Reece
15						- -	Additional Information - None Many thanks, Reece
16						- -	Additional Information - None Many thanks, Reece
17						- -	Additional Information - None Many thanks, Reece
sheet1

Cell Formulas
Range	Formula
I2:I17	I2	=IFERROR(VLOOKUP('Report results SA'!A3,Table2,2),)
J2:J17	J2	=IFERROR(VLOOKUP('Report results SA'!A3,Table26,2),)
K2:K17	K2	=IF('Report results SA'!G3>1,"Joanne fox","")
L2:L17	L2	='Report results SA'!F3
M2:M17	M2	=CONCAT([@Column1],CHAR(10),CHAR(10),[@Column3],CHAR(10),CHAR(10),[@Column4])

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H2:H28	Expression	=O2>3	text	NO
H2:H28	Expression	=A2>3	text	NO

Cells with Data Validation
Cell	Allow	Criteria
H2:H17	List	=lookuplists!$I$64:$I$72