How to attach protected part of worksheet as screenshot to the body of email

[Villalobos]

Hello,

I use the following code to attach a screenshot about the part of worksheet into the email body and it is working perfectly. But if I protect the worksheet and after I would like to send it then the code is stop in the "createJpg" session (With ThisWorkbook.Worksheets("Dashboard").ChartObjects.Add(Plage.Left, Plage.Top, Plage.Width, Plage.Height).

Do you have any idea how is it possible attach to email body a part of protected worksheet as screenshot?

Thanks in advance the advice!

Sub sendMail()
Dim appOutlook As Variant
Dim olMailItem As Variant
Dim Message As Variant
Dim olByValue As Variant
If Application.UserName = "x, y" Then
  If MsgBox("Would you like to send  to recipients?", vbYesNo + vbQuestion, "Question for you") = vbYes Then
  Application.Calculation = xlManual
  With Application
  .ScreenUpdating = False
  .EnableEvents = False
  End With
  Dim TempFilePath As String
  Set appOutlook = CreateObject("outlook.application")
  Set Message = appOutlook.CreateItem(olMailItem)
 
  With Message
  .Subject = "My project"
  .HTMLBody = "<span LANG=EN>" _
  & "<p class=style2><span LANG=EN><font FACE=Calibri SIZE=3>" _
  & "Hello,<br ><br >You can see here the status. <br >"
  Call createJpg("Dashboard", "A1:AA64", "DashboardFile")
 
  TempFilePath = Environ$("temp") & "\"
  .Attachments.Add TempFilePath & "DashboardFile.jpg", olByValue, 0
 
  .HTMLBody = .HTMLBody & "<br><B>Daily report:</B><br>" _
  & "<img src='cid:DashboardFile.jpg'" & "width='800' height='450'><br>" _
  & "<br>All the best,</font></span>" & "<br ><br >" & "<br ><br >" & Application.UserName
  .To = ""
  .Cc = ""
  .Send
  .Display
  End With
  With Application
  .ScreenUpdating = True
  .EnableEvents = True
  End With
  Application.Calculation = xlCalculationAutomatic
  End If
  Else
  MsgBox ("You do not have authorization, access denied! The file will be closed withing 5 seconds.")
  Application.Wait (Time + TimeValue("00:00:05"))
  ActiveWorkbook.Close savechanges:=False
  Application.DisplayAlerts = False
  End If
End Sub
Sub createJpg(Namesheet As String, nameRange As String, nameFile As String)
Dim Plage As Variant
  ThisWorkbook.Activate
  Worksheets("Dashboard").Activate
  Set Plage = ThisWorkbook.Worksheets("Dashboard").Range("A1:AA64")
  Plage.CopyPicture
  With ThisWorkbook.Worksheets("Dashboard").ChartObjects.Add(Plage.Left, Plage.Top, Plage.Width, Plage.Height)
  .Activate
  .Chart.Paste
  .Chart.Export Environ$("temp") & "\" & nameFile & ".jpg", "JPG"
  End With
  Worksheets("Dashboard").ChartObjects(Worksheets("Dashboard").ChartObjects.Count).Delete
  Set Plage = Nothing
End Sub

 

[Luke M]

You need to add the ChartObject to an unprotected sheet. Either use a different sheet in your workbook that isn't protected, or create a new sheet, create the chart there, paste image into your email, and then delete the worksheet.

 

[Villalobos]

Hi Luke M,

Thanks the tip!