BULK MAIL FROM EXCEL

[aabid qureshii]

Good Afternoon guys.
I need a help regarding to send the mails with attachment by excel to multiple recipient.
like

TO	CC	BCC	SUBJECT	BODY	ATTACHMENT
test1@mail.com, test2@mail.com	cc1@mail.com, cc2@mail.com	bcc@mail.com	test mail	hi	c:\users\desktop\abc.xlsx

i am not well versed about the macros, i am in need to send bulk mails with attachment. Please help me out.

 

[aabid qureshii]

Good Afternoon guys.
I need a help regarding to send the mails with attachment by excel to multiple recipient.
like

TO	CC	BCC	SUBJECT	BODY	ATTACHMENT
test1@mail.com, test2@mail.com	cc1@mail.com, cc2@mail.com	bcc@mail.com	test mail	hi	c:\users\desktop\abc.xlsx

i am not well versed about the macros, i am in need to send bulk mails with attachment. Please help me out.

i have this code, but its not working if i am putting mail id in cell by separating through comma

Option Explicit

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

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

 

[BSALV]

if you replace "msg.send" by "msg.display" then do you get those emails on your screen or don't you even get there ?

 

[KOKOSEK]

emails have to be separated by semicolon not a comma, so try:

msg.to = replace(sh.Range("A" & i).Value,",",";")
msg.cc = replace(sh.Range("B" & i).Value,",",";")

 

[aabid qureshii]

if you replace "msg.send" by "msg.display" then do you get those emails on your screen or don't you even get there ?

yeh its working,
but its opening multiple window of outlook, where i have to click one by one on send button.
It will be ok for less recipient, but if i will send it to 5oo recipient then ?

 

[aabid qureshii]

emails have to be separated by semicolon not a comma, so try:

msg.to = replace(sh.Range("A" & i).Value,",",";")
msg.cc = replace(sh.Range("B" & i).Value,",",";")

Thanks sir,
Now it is working.

thank you so much.

 

[KOKOSEK]

Happy to help.