Application - Excel 2016 & Outlook 2016
My OLD MACRO code worked fine in Excel 2010, now we have been upgraded to Office 365 (2016), and my coding debugs at the ".send" command. Run-time error '287': Application-defined or object-defined error" and the .send is highlighted in yellow.
I was able to find updated coding at" https://www.rondebruin.nl/win/s1/outlook/mail.htm". Using Rondebruin coding guidance (New macro), it kinda works, meaning if I end with .display, the email appears and we can manually hit send. But if I change the coding to .send, the macro finishes but NO email sends and it does NOT debug. We have to many emails and to many reports to manually hit send after each email. I can use this macro as a TEMPORARY fix, but can not have permanently. We run multiple reports by running macro's. The last part of EVERY macro sends an email to a set group of staff one report sends to over 100+ individual emails, to alert them the report is ready to work, and the reports link.
In my research it sounds like a security setting in our company's 2016 outlook. I do not have access to deactivate this setting, so if this is true, I need it built into the coding. I need help with a simplified code.
OLD Macro
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 = hco-pps-p-opc.insurance@work.life
.Subject = ActiveWorkbook.Name & " " & "report is ready for your review"
.Body = "The PP" & " " & FName & " " & "A1-280 report is available Please research your accounts take action." _
& vbNewLine & vbNewLine & _
"<\\VP0SENTSHRCMN03\Common2\PPS\PP\ODN\BRANCH\Insurance Unit\Reports\FEGLI A1 280 Yearly Reports>"
.Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
NEW macro
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 & " " & "Comp NOA report is now available in the shared directory. Please take action. " & _
"Click on this link to open the file : " & _
"file://VP0SENTSHRCMN03\Common2\PPS\PP\ODN\BRANCH\Insurance Unit\Yearly Reports" & _
""">//VP0SENTSHRCMN03\Common2\PPS\PP\ODN\BRANCH\Insurance Unit\Yearly Reports"
On Error Resume Next
With OutMail
.To = "mickey.mouse@work.life; Donald.duck@work.life"
.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
My OLD MACRO code worked fine in Excel 2010, now we have been upgraded to Office 365 (2016), and my coding debugs at the ".send" command. Run-time error '287': Application-defined or object-defined error" and the .send is highlighted in yellow.
I was able to find updated coding at" https://www.rondebruin.nl/win/s1/outlook/mail.htm". Using Rondebruin coding guidance (New macro), it kinda works, meaning if I end with .display, the email appears and we can manually hit send. But if I change the coding to .send, the macro finishes but NO email sends and it does NOT debug. We have to many emails and to many reports to manually hit send after each email. I can use this macro as a TEMPORARY fix, but can not have permanently. We run multiple reports by running macro's. The last part of EVERY macro sends an email to a set group of staff one report sends to over 100+ individual emails, to alert them the report is ready to work, and the reports link.
In my research it sounds like a security setting in our company's 2016 outlook. I do not have access to deactivate this setting, so if this is true, I need it built into the coding. I need help with a simplified code.
OLD Macro
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 = hco-pps-p-opc.insurance@work.life
.Subject = ActiveWorkbook.Name & " " & "report is ready for your review"
.Body = "The PP" & " " & FName & " " & "A1-280 report is available Please research your accounts take action." _
& vbNewLine & vbNewLine & _
"<\\VP0SENTSHRCMN03\Common2\PPS\PP\ODN\BRANCH\Insurance Unit\Reports\FEGLI A1 280 Yearly Reports>"
.Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
NEW macro
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 & " " & "Comp NOA report is now available in the shared directory. Please take action. " & _
"Click on this link to open the file : " & _
"file://VP0SENTSHRCMN03\Common2\PPS\PP\ODN\BRANCH\Insurance Unit\Yearly Reports" & _
""">//VP0SENTSHRCMN03\Common2\PPS\PP\ODN\BRANCH\Insurance Unit\Yearly Reports"
On Error Resume Next
With OutMail
.To = "mickey.mouse@work.life; Donald.duck@work.life"
.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
