Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #109916 > unrolled thread

need help with this send email micro

Back to article view | Back to microsoft.public.excel.programming

Contents

  need help with this send email micro eltyar <eltyar.130eedb8@excelbanter.com> - 2017-03-15 11:51 +0000
    Re: need help with this send email micro Living the Dream <noodnutt@gmail.com> - 2017-03-28 03:14 -0700
      Re: need help with this send email micro Living the Dream <noodnutt@gmail.com> - 2017-03-28 03:17 -0700

#109916 — 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.


+-------------------------------------------------------------------+
|Filename: Send Mail.zip                                            |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=1048|
+-------------------------------------------------------------------+



-- 
eltyar

[toc] | [next] | [standalone]

#109946

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

[toc] | [prev] | [next] | [standalone]

#109947

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.

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.excel.programming

csiph-web