HI Friends, I have been using a VBA formula in excel to send a part of the sheet as an image over an email. I tried to implement the same on a new workbook but this new workbook is password protected.
If I unlock the book the macro is working, but if the book is locked the macro throws an error. Please help me out
>>> use code - tags <<<
If I unlock the book the macro is working, but if the book is locked the macro throws an error. Please help me out
>>> use code - tags <<<
Code:
Sub sendMail()
Application.Calculation = xlManual
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Dim TempFilePath As String
'Create a new Microsoft Outlook session
Set appOutlook = CreateObject("outlook.application")
'create a new message
Set Message = appOutlook.CreateItem(olMailItem)
With Message
.Subject = ":: Daily Dashboard ::"
'.HTMLBody = "<span LANG=EN>" _
' & "<p class=style2><span LANG=EN><font FACE=Calibri SIZE=3>" _
' & "Hello,<br ><br >The weekly dashboard is available " _
' & "<br>Find below an overview :<BR>"
'first we create the image as a JPG file
Call createJpg("Associate Dashboard", "A13:AG86", "DashboardFile")
'we attached the embedded image with a Position at 0 (makes the attachment hidden)
TempFilePath = Environ$("temp") & "\"
.Attachments.Add TempFilePath & "DashboardFile.jpg", olByValue, 0
'Then we add an html <img src=''> link to this image
'Note than you can customize width and height - not mandatory
.HTMLBody = .HTMLBody _
& "<img src='cid:DashboardFile.jpg'" & "width='1514' height='1400'><br>" _
& "</font></span>"
.To = Range("H13")
.Display
'.Send
End With
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
Application.Calculation = xlCalculationAutomatic
End Sub
Sub createJpg(Namesheet As String, nameRange As String, nameFile As String)
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
Last edited by a moderator: