Dear All,

kindly i need help with my excel sheet attached i made it to send mails directly to my customers but when hit the button it just display to send for first customer only..

i need it to display for all customers until it found an empty cell in "send to" column.

thanks in advance.

Hi Eltyar

Try this instead. Using your spreadsheet, it worked for me.

You can also add signatures too if you have their location. HTH.

Sub sendEmail(eAddress As String, eName As String, eSubject As String, eMessage As String, eSend As String)

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

strbody = "Dear " & eName & ""

On Error Resume Next
With OutMail
.To = eAddress
.CC = ""
.BCC = ""
.Subject = eSubject
.Body = strbody
.Display
'.send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Sub sendEmails()

Dim eSend As String
Dim eAddress As String
Dim eName As String
Dim eSubject As String
Dim eMessage As String

For i = 4 To 6

eAddress = Sheets("MRM").Range("I" & i).Value
eName = Sheets("MRM").Range("A" & i).Value
eSubject = Sheets("MRM").Range("K" & i).Value
eMessage = Sheets("MRM").Range("M" & i).Value
eSend = Sheets("MRM").Range("N" & i).Value

If eAddress = "" Then
MsgBox (Sheets("MRM").Range("I" & i).Value & " - does not have a valid email, please change and retry")
Exit For
End If

If eSend = "Y" Then
Call sendEmail(eAddress, eName, eSubject, eMessage, eSend)
Sheets("MRM").Range("O" & i).Value = "Y"
End If

Next i

End Sub

I forgot to mention.

I added an extra condition in Column "N" which you need to have a Capital ( Y ) which tell the code which line to send as you may wish to skip some rows.

I then placed another ( Y ) in the next column to let you know it was sent.

Cheers
Mark.