What's wrong with this VBA code string to email out a picture of a range from Excel? It used to work before

[Lytleeric]

Sub createJpg(Namesheet As String, nameRange As String, nameFile As String)

Dim plage As Object

ThisWorkbook.Activate
Worksheets(Namesheet).Activate

Set plage = ThisWorkbook.Worksheets(Namesheet).Range(nameRange)
plage.CopyPicture

With ThisWorkbook.Worksheets(Namesheet).ChartObjects.Add(plage.Left, plage.Top, plage.Width, plage.Height)
.Activate
.Chart.Paste
.Chart.Export Environ$("temp") & "\" & nameFile & ".jpg", "JPG"
End With

Worksheets(Namesheet).ChartObjects(Worksheets(Namesheet).ChartObjects.Count).Delete
Set plage = Nothing


End Sub


Sub sendMail()

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

Dim TempFilePath As String 'location of temp image
Dim imgRNG As String 'area for image
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As Variant

imgRNG = "A3:L59"" 'change this for range"


'Create a new Microsoft Outlook session
Set OutApp = CreateObject("outlook.application")

'create a new message
Set OutMail = OutApp.CreateItem(0)

With OutMail

.Subject = "Facility Checklist"

'following bit is to setup the image
Call createJpg("Checklist", imgRNG, "MailAttach") 'Worksheet name
TempFilePath = Environ$("temp") & "\"
.Attachments.Add TempFilePath & "MailAttach.jpg", 0, 0

'Then we add an html <img src=''> link to this image
'<br> = line break
'
strbody = "<span LANG=EN>" & "<p class=style2><span LANG=EN><font FACE=Calibri SIZE=3>" _
& "Hi,<br><br>Please see Facility Checklist Below" & _
"<br><B></B><br><br><img src='cid:MailAttach.jpg'<br>"

.Display 'display email to grab signature
.htmlbody = strbody & "<br>" & .htmlbody ' pass body of text then line break then insert signature

.To = "test@test.com"
.Cc = ""
'.Send 'if you want to autosend enable this

End With

With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With


End Sub

 

[Logit]

This version works here :

Option Explicit

Sub createJpg(Namesheet As String, nameRange As String, nameFile As String)

Dim plage As Object

ThisWorkbook.Activate
Worksheets("Sheet1").Activate

Set plage = ThisWorkbook.Worksheets("Sheet1").Range("A3:L59")
plage.CopyPicture

With ThisWorkbook.Worksheets("Sheet1").ChartObjects.Add(plage.Left, plage.Top, plage.Width, plage.Height)
.Activate
.Chart.Paste
.Chart.Export Environ$("temp") & "\" & nameFile & ".jpg", "JPG"
End With

Worksheets("Sheet1").ChartObjects(Worksheets("Sheet1").ChartObjects.Count).Delete
Set plage = Nothing


End Sub


Sub sendMail()

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

Dim TempFilePath As String 'location of temp image
Dim imgRNG As String 'area for image
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As Variant

imgRNG = "A3:L59" 'change this for range"


'Create a new Microsoft Outlook session
Set OutApp = CreateObject("outlook.application")

'create a new message
Set OutMail = OutApp.CreateItem(0)

With OutMail

.Subject = "Facility Checklist"

'following bit is to setup the image
Call createJpg("Checklist", imgRNG, "MailAttach") 'Worksheet name
TempFilePath = Environ$("temp") & "\"
.Attachments.Add TempFilePath & "MailAttach.jpg", 0, 0

'Then we add an html <img src=''> link to this image
'<br> = line break
'
strbody = "<span LANG=EN>" & "<p class=style2><span LANG=EN><font FACE=Calibri SIZE=3>" _
& "Hi,<br><br>Please see Facility Checklist Below" & _
"<br><B></B><br><br><img src='cid:MailAttach.jpg'<br>"

.Display 'display email to grab signature
.htmlbody = strbody & "<br>" & .htmlbody ' pass body of text then line break then insert signature

.To = "test@test.com"
.Cc = ""
'.Send 'if you want to autosend enable this

End With

With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With


End Sub

This line of code has an extra QUOTE symbol that needed removing : imgRNG = "A3:L59"" 'change this for range"
It is the red colored quote symbol after: A3:L59" "

You will need to edit the sheet name in the code to match your project.

 

[Lytleeric]

This version works here :

Option Explicit

Sub createJpg(Namesheet As String, nameRange As String, nameFile As String)

Dim plage As Object

ThisWorkbook.Activate
Worksheets("Sheet1").Activate

Set plage = ThisWorkbook.Worksheets("Sheet1").Range("A3:L59")
plage.CopyPicture

With ThisWorkbook.Worksheets("Sheet1").ChartObjects.Add(plage.Left, plage.Top, plage.Width, plage.Height)
.Activate
.Chart.Paste
.Chart.Export Environ$("temp") & "\" & nameFile & ".jpg", "JPG"
End With

Worksheets("Sheet1").ChartObjects(Worksheets("Sheet1").ChartObjects.Count).Delete
Set plage = Nothing


End Sub


Sub sendMail()

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

Dim TempFilePath As String 'location of temp image
Dim imgRNG As String 'area for image
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As Variant

imgRNG = "A3:L59" 'change this for range"


'Create a new Microsoft Outlook session
Set OutApp = CreateObject("outlook.application")

'create a new message
Set OutMail = OutApp.CreateItem(0)

With OutMail

.Subject = "Facility Checklist"

'following bit is to setup the image
Call createJpg("Checklist", imgRNG, "MailAttach") 'Worksheet name
TempFilePath = Environ$("temp") & "\"
.Attachments.Add TempFilePath & "MailAttach.jpg", 0, 0

'Then we add an html <img src=''> link to this image
'<br> = line break
'
strbody = "<span LANG=EN>" & "<p class=style2><span LANG=EN><font FACE=Calibri SIZE=3>" _
& "Hi,<br><br>Please see Facility Checklist Below" & _
"<br><B></B><br><br><img src='cid:MailAttach.jpg'<br>"

.Display 'display email to grab signature
.htmlbody = strbody & "<br>" & .htmlbody ' pass body of text then line break then insert signature

.To = "test@test.com"
.Cc = ""
'.Send 'if you want to autosend enable this

End With

With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With


End Sub

This line of code has an extra QUOTE symbol that needed removing : imgRNG = "A3:L59"" 'change this for range"
It is the red colored quote symbol after: A3:L59" "

You will need to edit the sheet name in the code to match your project.

It worked, thank you so much!

 

[Logit]

You are welcome.