• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

eMail MACRO not working on protected worksheet

RDaga

New Member
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 <<<
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:
RDaga
Could that file protection change that You could do above?
Could You unprotect - Your duties - protect that file?
 
RDaga
As You wrote Yourself
It works if You UnProtect that sheet before You'll do run Your code
( means that, You would add one codeline in the beginning Your code )
and
after Your code, You'll protect it again
( means that, You would add again one code line in the end of Your code ).
 
RDaga
As You wrote Yourself
It works if You UnProtect that sheet before You'll do run Your code
( means that, You would add one codeline in the beginning Your code )
and
after Your code, You'll protect it again
( means that, You would add again one code line in the end of Your code ).


I did try that earlier but it was giving an issue. But post your suggestion I tried googling a little more and was able to get this working..... Yapeeeee!!!!
 
Back
Top