?
Solved

Sending email from Excel 2016 VBA and Outlook 2016

Posted on 2016-10-11
4
Medium Priority
?
1,953 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 2000 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 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article lists the top 5 free OST to PST Converter Tools. These tools save a lot of time for users when they want to convert OST to PST after their exchange server is no longer available or some other critical issue with exchange server or impor…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

752 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