Sending email from Excel 2016 VBA and Outlook 2016

I'm using this sample code to send a simple text email. Outlook is on my pc.
When I send it to the email address that I receive into my Outlook it works fine.
But when I send it to a Hotmail account it does not arrive.

What am I doing wrong please? Thanks.

Sub Mail_small_Text_Outlook()
'For Tips see:
'Working in Office 2000-2016
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

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

    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "This is line 1" & vbNewLine & _
              "This is line 2" & vbNewLine & _
              "This is line 3" & vbNewLine & _
              "This is line 4"

    On Error Resume Next
    With OutMail
        .To = ""
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = strbody
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Open in new window

Who is Participating?
QlemoConnect With a Mentor Batchelor and DeveloperCommented:
You'll have to monitor (i.e. debug in VBA) what exactly happens, as I have no clue what is going on from what I see, sorry. It might also help to comment out the On Error to see if an error is thrown.

Either the mail is created - then it is in Drafts, Outbox or Sent (or the folder which is defined as default Sent folder)
or it is not created and an error is thrown, but ignored because the code says so.
QlemoConnect With a Mentor Batchelor and DeveloperCommented:
Are you able to locate the missing mail in the Sent or Outbox folder of Outlook?
Do you get an Non Delivery Report mail back?
hindersalivaAuthor Commented:
Hi, the emails that didn't go (to Hotmail) are not in the Sent folder. So they never went.

Actually the problem is a different one! The emails DO go to Hotmail or any other I expect.

However, they didn't go when I put them in a loop to send several serially. So, only the first one went. I know the loop looped through the items as normal, but somehow the subsequent emails did not get sent off.

So the question is now different.

I shall adjust the loop (to include create/destroy object) and report back.
hindersalivaAuthor Commented:
Qlemo, I learned a lot there!
I copied the code from elsewhere and should have taken out the On Error. Need to brush up on debug in VBA ('Watch'?) also.

Solved. I put the loop around the create objects and destroy objects. And it works as expected.

Thanks for staying with me on this.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.