Changes in Excel 2013 vs 2016 for email macros

spk

New Member
Joined
Jan 19, 2011
Messages
15
The following macro worked perfectly for me while using Excel 2013 and Outlook 2013. I recently upgraded to Excel 2016 and Office 2016 and the macro no longer works. It just leaves me sitting on the worksheet that is supposed to be the content of the email and nothing happens in Outlook. What am I doing wrong??

Many Thanks in Advance,

Steve

Sub Email_Thank_You_Note()
Dim AWorksheet As Worksheet
Dim Sendrng As Range
Dim rng As Range


On Error GoTo StopMacro

Dim Resp As String


Resp = MsgBox("Please confirm ALL POST-PROJECT DATA is COMPLETE and you want to send a THANK YOU email to client.", vbOKCancel + vbExclamation + vbDefaultButton2, "Order Confirmation")
If Resp = vbCancel Then
End ' This ends the macro
End If


With Application
.ScreenUpdating = False
.EnableEvents = False
End With


'Fill in the Worksheet/range you want to mail
'Note: if you use one cell it will send the whole worksheet
Set Sendrng = Worksheets("Thank You Note").Range("A1:C27")


'Remember the activesheet
Set AWorksheet = ActiveSheet


'Create the mail and send it
With Sendrng


' Select the worksheet with the range you want to send
.Parent.Select


'Remember the ActiveCell on that worksheet
Set rng = ActiveCell


'Select the range you want to mail
.Select


' Create the mail and send it
ActiveWorkbook.EnvelopeVisible = True
With .Parent.MailEnvelope


' Set the optional introduction field thats adds
' some header text to the email body.
'.Introduction = "This is a test mail."


' In the "With .Item" part you can add more options
' See the tips on this Outlook example page.
' http://www.rondebruin.nl/mail/tips2.htm
With .Item
.to = Sheets("Painting").Range("J7").Value
' Denis wants to use Foxboroorders@babels.com for now
.Subject = "Thank You"
.DeferredDeliveryTime = DateAdd("d", 7, Now)
.Send
End With


End With


'select the original ActiveCell
rng.Select
End With


'Activate the sheet that was active before you run the macro
AWorksheet.Select


StopMacro:
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
ActiveWorkbook.EnvelopeVisible = False


End Sub
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

spk

New Member
Joined
Jan 19, 2011
Messages
15
I don't get any errors. It just ends sitting on the "thank you note" sheet with nothing obvious done.
 

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,456
If you still have access to a 2013 machine I would step through the code using F8 in order to identify what happens normally in 2013.

Then do the same in 2016 and see where the disconnect is.

If you can't find it there are plenty of other examples on Ron's website that use the Outlook Object Model to send emails. One of them should work without issue.

See Example 1....Ron Says it's working in 2016

Mail Range/Selection in the body of the mail with MailEnvelope
 

spk

New Member
Joined
Jan 19, 2011
Messages
15
I'd be happy to use one of the other methods, but i need to set a 7 day delay on the send i Outlook. I cant find how to do that in the code. Can you help with that?

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,127,870
Messages
5,627,364
Members
416,245
Latest member
Xterminat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top