Excel 2010 VBA send email doesn't work with Excel 2016

[SPewtress]

Hello,

Recently my employer moved from Office 2010 to Office 365 (Office 2016). Now my macro's that contain sending emails no longer work and when ran the error and provide a Run Time error '287': Application-defined or object-defined error. Hit debug and the .send command is highlighted in yellow. Per my research it might have something to do with security settings in outlook 2016 now. But I do not have access to everyone outlook to set the permissions to allow to run the macro.

PLEASE help, I have many macros, and one actually sends out hundreds of emails. The work around macro I located from "https://www.rondebruin.nl/win/s1/outlook/mail.htm" kind of works. If I use the .display the email populates and I can manually hit send. Per his instructions he states to replace the .display with .send and if I do this, NO email generates or sends. And on the report that sends hundreds of emails we do not have time to manually hit send.

OLD CODE
Dim OutApp As Object
Dim OutMail As Object
Const SendTo As String = "your email address"
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
With OutMail

.To = "Mickey.Mouse@love.com"
' .Subject = ActiveWorkbook.Name & " " & "is ready for your review"
.Body = "The file is located at <\\VP0SENTSHRCMN02\Common\ESS\Insufficient Pay FTR\FTR Yearly Reports> ." _
& vbNewLine & vbNewLine & _
"Please review the 3XX Actions and ForReview tabs." _
& vbNewLine & vbNewLine & _
"Once the records have been reviewed notify the clerks when done, and send the emails."
.Send
End With
Set OutMail = Nothing
Set OutApp = Nothing

NEW CODE (need fixed to auto send)

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
If ActiveWorkbook.Path <> "" Then
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strbody = "" & _
"The PP" & " " & FName & " " & "Once the records have been reviewed notify the clerks when done, and send the emails " & _
"Click on this link to open the file : " & _
"file://VP0SENTSHRCMN02\Common\ESS\Insufficient Pay FTR\FTR Yearly Reports" & _
""">//VP0SENTSHRCMN02\Common\ESS\Insufficient Pay FTR\FTR Yearly Reports"
On Error Resume Next
With OutMail
.To = ""Mickey.Mouse@love.com"
.CC = ""
.BCC = ""
.Subject = ActiveWorkbook.Name & " " & "report is ready for your review"
.HTMLBody = strbody
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
Else
MsgBox "The ActiveWorkbook does not have a path, Save the file first."
End If

 

[JLGWhiz]

If you review your old code against your new code, you will see tha thed '.Send' command is omitted in the new code. You need to insert it as shown below:

.Display
[COLOR=#FF8C00].Send[/COLOR]
End With

 

[SPewtress]

I tried replacing the .Display with .Send. Then I added the .send after the .Display and with the new code - NOTHING happens. It (the email with all the information) is just still sitting on my screen. It is making me manually hit send.

 

[JLGWhiz]

I haven't woeked with email in quite a while and have forgotten most of what I once knew about it. I also no longer have Outlook, so I don't have a ready reference to the syntax. But you could try

.SendMail

and see if that works. I all depends on which application is in play at the time the command executes.

 

[SPewtress]

I replaced .Display with .SendMail and nothing happened, no email no nothing. I added .Display back on then added .SendMail. The mail displays but still does not send. I have to manually hit send

 

[JLGWhiz]

I did a little research in the help files and it looks like .Send is the correct syntax for the OutApp.CreateItem method. I don't believe you have to replace .Display with .Send but if you do not need to see the message before it is sent, I suppose it would work. Again, it has been a long time since I worked with Outlook and if I remember correctly, I had some difficulty with it at the time, but that is about all I remember about it. I don't know if the body content would have any affect on the time interval in the code execution (ie. if the code completes execution before the body content is loaded) or not.