Send email to multiple recipients

[RIYADBANK50]

dear all
i have files excel i want to send email to multiple recipients .
because i have more than persons to put them in TO & CC .

if there is way to put all columns below in each email will be great .
wait your kindly replay and support as usual .

regards

Sub Send_Mails()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Send_Mails")
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("A:A"))

For i = 2 To last_row
    Set msg = OA.createitem(0)
    msg.to = sh.Range("A" & i).Value
    msg.cc = sh.Range("B" & i).Value
    msg.Subject = sh.Range("C" & i).Value
    msg.body = sh.Range("D" & i).Value
   
    With msg
    .HTMLBody = GetHTMLBody(Name, "document", phone, documentName, inq)
    End With

    If sh.Range("E" & i).Value <> "" Then
        msg.attachments.Add sh.Range("E" & i).Value
    End If
   
    msg.send
   
    sh.Range("F" & i).Value = "Sent"

Next i

MsgBox "All the mails have been sent successfully"


End Sub

Send Multiple Mails2.xlsm
	A	B	C	D	E		G	H	I	J	K	L	M	N
1	To	CC	CC	Subject	Body		Status	CIC	NAME	DUE_DATE	PRODUCT_TYPE	AMOUNT	TICKET
2	Bader T. Al Rujaib	Bader T. Al Rujaib	Omar M. Al-Mabouth	URGENT /// Rollover or settlement	"Dears, Kindly note that you have maturity as shown below, so please provide us with your respond to rollover the loan or settlement. And fill the data schedule below when you want to rollover it Regards"			4117	DHL	20221025	Murabaha Pure Bai Ajel	8,321,411.10	T0082101280
3	Bader T. Al Rujaib	Bader T. Al Rujaib	Omar M. Al-Mabouth	TEST -2	"Dears, Kindly note that you have maturity as shown below, so please provide us with your respond to rollover the loan or settlement. And fill the data schedule below when you want to rollover it Regards"			5222222	SALMON	20221025	Murabaha Pure Bai Ajel	8,321,411.10	T0082101280
4
5
6
7
Send_Mails

 

[Amir Wisal]

Hi Riyad,

i am fairly new to VBA but this code might solve your problem if you keep the same format. you can obviously add new lines below but start inputting data from A2.

Further, also update the name of the excel sheet to Send_Emails for this code to work. i have added comments in vba below, go through it.

I have removed extra code written above (signature and status part) as no information was given by yourself.

Hope it helps.

PS: This is first time I am responding to a thread.


Sub Send_Mails()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Send_Emails") ' Update the name of the sheet in the quotation marks

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("A:A"))

For i = 2 To last_row
Set msg = OA.createitem(0)
msg.to = sh.Range("A" & i).Value
msg.cc = sh.Range("B" & i).Value & ";" & sh.Range("C" & i).Value
msg.Subject = sh.Range("D" & i).Value
msg.body = sh.Range("E" & i).Value _
& vbNewLine & vbNewLine _
& Range("H1").Value & " " & "-" & " " & sh.Range("H" & i).Value & vbNewLine _
& Range("i1").Value & " " & "-" & " " & sh.Range("I" & i).Value & vbNewLine _
& Range("J1").Value & " " & "-" & " " & sh.Range("J" & i).Value & vbNewLine _
& Range("K1").Value & " " & "-" & " " & sh.Range("K" & i).Value & vbNewLine _
& Range("L1").Value & " " & "-" & " " & sh.Range("L" & i).Value & vbNewLine _
& Range("M1").Value & " " & "-" & " " & sh.Range("M" & i).Value & vbNewLine

msg.display ' Change it to .send if you want to send emails without displaying

Next i

End Sub

 

[RIYADBANK50]

sorry for very late replay , but i was have issue family, this your code wonderful easy and can understand , good job my brother .

But can I specify the email address from which it was sent,
as I would like to send it from the group email and not the personal email?

Hi Riyad,

i am fairly new to VBA but this code might solve your problem if you keep the same format. you can obviously add new lines below but start inputting data from A2.

Further, also update the name of the excel sheet to Send_Emails for this code to work. i have added comments in vba below, go through it.

I have removed extra code written above (signature and status part) as no information was given by yourself.

Hope it helps.

PS: This is first time I am responding to a thread.


Sub Send_Mails()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Send_Emails") ' Update the name of the sheet in the quotation marks

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("A:A"))

For i = 2 To last_row
Set msg = OA.createitem(0)
msg.to = sh.Range("A" & i).Value
msg.cc = sh.Range("B" & i).Value & ";" & sh.Range("C" & i).Value
msg.Subject = sh.Range("D" & i).Value
msg.body = sh.Range("E" & i).Value _
& vbNewLine & vbNewLine _
& Range("H1").Value & " " & "-" & " " & sh.Range("H" & i).Value & vbNewLine _
& Range("i1").Value & " " & "-" & " " & sh.Range("I" & i).Value & vbNewLine _
& Range("J1").Value & " " & "-" & " " & sh.Range("J" & i).Value & vbNewLine _
& Range("K1").Value & " " & "-" & " " & sh.Range("K" & i).Value & vbNewLine _
& Range("L1").Value & " " & "-" & " " & sh.Range("L" & i).Value & vbNewLine _
& Range("M1").Value & " " & "-" & " " & sh.Range("M" & i).Value & vbNewLine

msg.display ' Change it to .send if you want to send emails without displaying

Next i

End Sub