Solved

Sending email from Excel 2016 VBA and Outlook 2016

Posted on 2016-10-11
4
67 Views
Last Modified: 2016-10-12
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: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
'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 = "myemail@myemailserver.com"
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = strbody
        .Send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Open in new window

0
Comment
Question by:hindersaliva
  • 2
  • 2
4 Comments
 
LVL 68

Assisted Solution

by:Qlemo
Qlemo earned 500 total points
Comment Utility
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?
0
 

Author Comment

by:hindersaliva
Comment Utility
Hi, the emails that didn't go (to Hotmail) are not in the Sent folder. So they never went.

GOT MORE INFO!!!!!
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.
0
 
LVL 68

Accepted Solution

by:
Qlemo earned 500 total points
Comment Utility
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.
0
 

Author Comment

by:hindersaliva
Comment Utility
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.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Use email signature images to promote corporate certifications and industry awards.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now