Solved

Sending email from Excel 2016 VBA and Outlook 2016

Posted on 2016-10-11
4
1,347 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 70

Assisted Solution

by:Qlemo
Qlemo earned 500 total points
ID: 41839217
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
ID: 41839810
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 70

Accepted Solution

by:
Qlemo earned 500 total points
ID: 41839815
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
ID: 41839819
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

690 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