Solved

Sending email from Excel 2016 VBA and Outlook 2016

Posted on 2016-10-11
4
146 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
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 68

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

911 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

22 Experts available now in Live!

Get 1:1 Help Now