Protected Excel sheet copy to email bug

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 a bit of a bug where a new email is created but the sheet isn't pasted. Instead, Excel tells me it can't do it because the sheet is protected.

I have tried changing the macro so that it unprotects before it selects and to protects after pasting but this just results in a new email with no sheet pasted.

I have tried adding a wait command before the protect command but this just results in a new email with no sheet pasted and the macro taking longer to end.

Any ideas?

Try using `DoEvents` for waiting. See http://stackoverflow.com/questions/20963032/excel-vba-sent-outlook-email-does-not-include-pasted-range and http://support.microsoft.com/kb/118468. Plus: 1) try waiting (with `DoEvents` or `Wait ` between creation of email and pasting, 2) use breakpoints and step by step execution to see if the issue is solved with time delays. — sancho.s ReinstateMonicaCellio, Jan 06 '15 at 16:52
The problem is probably because you are using `Select` [You should never use `select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) and you can't Select a protected cell anyways. Try using `Sheets("Ordering").Range("A1:H63").Copy` — Chrismas007, Jan 06 '15 at 16:58
`SendKeys` is notoriously unreliable, too. You can probably use a better method of putting the data in Outlook email. Give me a moment to think about it... — David Zemens, Jan 06 '15 at 17:21

score 3 · Answer 1 · answered Jan 06 '15 at 17:38

3

Use this instead of SendKeys:

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

Putting it all together, make sure to protect/unprotect the sheet:

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

answered Jan 06 '15 at 17:38

David Zemens

53,033
11
81
130

I tried your alternative to SendKeys and I got, "Value out of range". I like the idea of an alternative though. Any more suggestions? – Dan Jan 07 '15 at 10:34
1

You could try `.GetInspector().WordEditor.Range(0,0).Paste` instead ? – David Zemens Jan 07 '15 at 15:35

score 1 · Answer 2 · answered Jan 06 '15 at 22:25

One thing that might also help when working with protected sheets in general is to protect them using VBA and specify the UserInterFaceOnly:=True parameter. This allows macros to perform most functions on protected sheets. However it can only be done by VBA and only persists for the time the workbook is open, so ideally it needs to go in the workbook_open event module. If you are specifying a password, then its also a good idea to password protect your vba module. Some generic examples of a protection routine

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

While this may be good advice, it is hardly a sufficient solution to this question... — David Zemens, Jan 07 '15 at 13:07
@David - I never said it was a solution, I said it was useful, given the problems mentioned with protected sheets. So in context of the question, I feel it is relevant and "usefull" - I am not expecting any "answer" votes, merely adding what I believe is useful information that is relevant to the question, in addition to your answer — Mark Moore, Jan 07 '15 at 14:03

Protected Excel sheet copy to email bug

2 Answers2