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

Lytleeric

New Member
Joined
Aug 31, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
This version works here :

VBA Code:
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.
 
Upvote 0
This version works here :

VBA Code:
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!
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top