Protected instance of Excel worksheet with email error

Question

I have a protected worksheet that I want to copy to an email using a macro. I am currently using the following code:

Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

Sheets("Ordering").Select
Range("A1:H63").Select
Selection.Copy

With OutMail
    .To = ""
    .Cc = ""
    .BCC = ""
    .Subject = ""
    .Display
End With

SendKeys "^({v})", True

Set OutMail = Nothing
Set OutApp = Nothing

This works most of the time. However, it seems to have an error when a new letter is created, but the sheet is not inserted. Instead, Excel tells me that it cannot do this because the worksheet is protected.

I tried to change the macro so that it removes the protection before it selects and protects after insertion, but this leads to the appearance of a new letter without sticking the sheet.

I tried to add the wait command before the protection command, but it just leads to a new email message without sticking the sheet, and the macro takes longer.

Any ideas?

Answer 1

SendKeys:

OutMail.GetInspector().WordEditor.Range(1,1).Paste

, / :

With Sheets("Ordering")
    .Unprotect
    .Range("A1:E12").Copy

    With OutMail
        .To = ""
        .Cc = ""
        .BCC = ""
        .Subject = ""
        .Display
        .GetInspector().WordEditor.Range(1,1).Paste
    End With

    .Protect
End With

Answer 2

, , - VBA UserInterFaceOnly: = True. . VBA , , workbook_open. , vba .

For Each wsheet In ActiveWorkbook.Worksheets
    Select Case wsheet.Name
        Case "Some Specific Sheet Name 1", "Another Specific Sheet name"
            wsheet.Protect Password:=pw, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
            AllowFiltering:=True, UserInterFaceOnly:=True
            wsheet.EnableSelection = xlNoRestrictions
        Case "Some Sheet I want to hide"
            wsheet.Protect Password:=pw, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
            AllowFiltering:=True, UserInterFaceOnly:=True
            wsheet.EnableSelection = xlNoRestrictions
            wsheet.visible = False
        Case "LookupsSome Sheet I want to hide, but not protect"
            wsheet.visible = False
        Case "Some sheet I really really want to hide"
            wsheet.visible = xlVeryHidden
        Case "Some sheet I dont want to do anything with"
            'Dont do anything
        Case Else 'Every other sheet is hidden and protected
            wsheet.visible = False
            wsheet.Protect Password:=pw, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
            AllowFiltering:=True, UserInterFaceOnly:=True
            wsheet.EnableSelection = xlNoRestrictions
    End Select
Next wsheet