failes to send mail after first in loop

Question

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

It fails because you set `OutMail` to `Nothing` within the loop but never insantiate it again within the loop. The code won't fail because of your `On Error Resume Next` line. Try setting `OutMail` within the loop. — Ambie, Jul 27 '16 at 11:06
Could you also give me a hint on why the attachment wont load (and yes I'm aware that I have removed it;)) — krib, Jul 27 '16 at 11:31
The root of the problem is misuse of on Error Resume Next. Restrict use to where it is appropriate so you see errors to fix. https://stackoverflow.com/a/31753321/1571407 — niton, Nov 13 '18 at 18:50

score 1 · Accepted Answer · answered Jul 27 '16 at 11:06

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

score 0 · Answer 2 · answered Nov 12 '18 at 20:49

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

This adds an EOF idea that is not applicable to the question. If you use this consider moving `Set mail_object = CreateObject("Outlook.Application")` before `Do Until in_file.EOF` and moving `Set mail_object = Nothing` after `Loop`. — niton, Nov 13 '18 at 15:03

failes to send mail after first in loop

2 Answers2