A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Programming
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

need help with this send email micro

Thread Tools Display Modes
Old March 15th 17, 11:51 AM
eltyar eltyar is offline
Junior Member
First recorded activity by ExcelBanter: Nov 2013
Posts: 8
Lightbulb need help with this send email micro

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.
Attached Files
File Type: zip Send Mail.zip (35.4 KB, 21 views)

Last edited by eltyar : March 15th 17 at 12:32 PM.
Old March 28th 17, 11:14 AM posted to microsoft.public.excel.programming
Living the Dream
external usenet poster
Posts: 119
Default need help with this send email micro

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")
Set OutMail = OutApp.CreateItem(0)

strbody = "Dear " & eName & ""

On Error Resume Next
With OutMail
.To = eAddress
.CC = ""
.BCC = ""
.Subject = eSubject
.Body = strbody
'.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

Old March 28th 17, 11:17 AM posted to microsoft.public.excel.programming
Living the Dream
external usenet poster
Posts: 119
Default need help with this send email micro

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.


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Send email from excel to notes - save email in sent folder Boss Excel Programming 0 March 15th 10 01:52 PM
Excel VBA macro to send email attachment from default email client wifigoo Excel Programming 2 April 12th 08 03:54 PM
Send data from Excel in email from specific email address Erik Excel Programming 5 December 5th 07 05:09 PM
send wkbk as an email attachment with an email address copied from SueInAtl Excel Discussion (Misc queries) 0 May 21st 07 10:53 PM
body of email disappears when I send an email from Excel ~A Excel Discussion (Misc queries) 0 February 25th 05 10:55 PM

All times are GMT +1. The time now is 12:20 AM.

Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.