I have a macro that works fine in Excel 2010 and 2013.
It copies a range from excel, saves it as a jpeg on my c drive and then emails the save file using outlook.
I have recently upgraded to excel 2016 and the macro no longer works and i don't know why.
I would be extremely grateful if anyone can tell me why it no longer works properly.
currently in Excel 2016 it sends a blank white image, the process works, the email sends, but it has actually stopped copying the content in the range.
regards Len
see macro below
Sub EmailDatasheetrange()
'
' EmailDatasheetrange Macro
'
' =========================================
' Code to save selected Excel Range as Image
' =========================================
Dim oRange As Range
Dim oCht As Chart
Dim oImg As Picture
Set oRange = Sheets("Data Sheet").Range("a4:r80")
Set oCht = Charts.Add
oRange.CopyPicture xlScreen, xlPicture
oCht.Paste
oCht.Export Filename:="C:\temp\SavedRange.jpg", Filtername:="JPG"
Application.DisplayAlerts = False
Application.ScreenUpdating = True
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = "MYEMAILADDRESS"
.Subject = "Data Sheet Curve Comps"
.Attachments.Add ("C:\temp\SavedRange.jpg")
.Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
'Sub DeleteActiveSheet()
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
'
'
End Sub
It copies a range from excel, saves it as a jpeg on my c drive and then emails the save file using outlook.
I have recently upgraded to excel 2016 and the macro no longer works and i don't know why.
I would be extremely grateful if anyone can tell me why it no longer works properly.
currently in Excel 2016 it sends a blank white image, the process works, the email sends, but it has actually stopped copying the content in the range.
regards Len
see macro below
Sub EmailDatasheetrange()
'
' EmailDatasheetrange Macro
'
' =========================================
' Code to save selected Excel Range as Image
' =========================================
Dim oRange As Range
Dim oCht As Chart
Dim oImg As Picture
Set oRange = Sheets("Data Sheet").Range("a4:r80")
Set oCht = Charts.Add
oRange.CopyPicture xlScreen, xlPicture
oCht.Paste
oCht.Export Filename:="C:\temp\SavedRange.jpg", Filtername:="JPG"
Application.DisplayAlerts = False
Application.ScreenUpdating = True
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = "MYEMAILADDRESS"
.Subject = "Data Sheet Curve Comps"
.Attachments.Add ("C:\temp\SavedRange.jpg")
.Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
'Sub DeleteActiveSheet()
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
'
'
End Sub