CODEWITHSUNDEEP
vbaexcel
krib
krib

Reputation: 569

failes to send mail after first in loop

Got a macro running through columns with mail addresses using most parts from Ron de Bruin. The macro runs through just fine, but only sends the first hit in column B and doesn't display any of the other hits when i try to watch it? What could be the issue?

The code is so that I can get the default signature from outlook, thats why it's .Display first in the code.

Sub mail_HTML()
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
'Working in Office 2000-2016
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim strbody As String

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

On Error GoTo cleanup
For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
    If cell.Value Like "?*@?*.?*" And _
       LCase(Cells(cell.Row, "C").Value) = "yes" Then

strbody = "<H3>Hei " & Cells(cell.Row, "E").Value & "</H3>" _
             & "<p>" & Range("k4") & "<p>"

         On Error Resume Next
        With OutMail
            .Display
            .To = cell.Value
            .Subject = Range("K12").Value
            .HTMLBody = strbody & .HTMLBody
            'You can add files also like this
            '.Attachments.Add Range("O1").Value
            .Send
        End With
        On Error GoTo 0
        Set OutMail = Nothing
    End If
Next cell

cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

Upvotes: 1

Views: 195

Answers (2)

Richard Hewitt
Richard Hewitt

Reputation: 1

Tried that but it is not working for me, here is my code:
Do Until in_file.EOF
Email_To = in_file!email_address
Email_Bcc = ""
Email_Body = in_file!email_salut & " " & in_file!email_name & ", test this."
Email_Subject = "Email Subject"
Set mail_object = CreateObject("Outlook.Application")
Set mail_single = mail_object.CreateItem(0)

With mail_single
    .Subject = Email_Subject
    .To = Email_To
    .cc = Email_Cc
    .BCC = Email_Bcc
    .Body = Email_Body
    .send
End With

Set mail_object = Nothing
Set mail_single = Nothing

in_file.MoveNext

Loop

Upvotes: 0

Mono
Mono

Reputation: 742

When you set 

Set OutMail = Nothing

You dont have any access to the object anymore (because it is destroyed). But you set it before the loop. You need to set it in every loop then like this:

    On Error Resume Next
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .Display
        .To = cell.Value
        .Subject = Range("K12").Value
        .HTMLBody = strbody & .HTMLBody
        'You can add files also like this
        '.Attachments.Add Range("O1").Value
        .Send
    End With
    On Error GoTo 0
    Set OutMail = Nothing

So after 1 Email the oject is destroyed, but you are not aware because of the on error resume next

Upvotes: 1

Related Questions