Villalobos
Active Member
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!
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!
Code:
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
Last edited: