Automation Error when trying to protect VB project and email it as attachment

Question

I am getting an automation error when I try to protect a VBProject and email the workbook as attachment. What am I missing here?

Here is code to Protect VB project:

Sub ProtectVBProject(WB As Workbook, ByVal strPassWord As String)

Dim vbProj As Object

Set vbProj = WB.VBProject

'Is it already locked!    
If vbProj.Protection = 1 Then Exit Sub

Set Application.VBE.ActiveVBProject = vbProj

'SendKeys to set the project password    
SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & strPassWord & "{TAB}" & strPassWord & "~"

Application.VBE.CommandBars(1).FindControl(ID:=2578, recursive:=True).Execute

   'Close and Save    
    WB.Close True    

    End Sub

Here is code that calls ProtectVBProject to Protect VB project and attach the workbook and mail it

 TempFilePath = Environ$("temp") & "\"
    TempFileName = "Email Test " & Sourcewb.Name & " " _
                 & Format(Now, "dd-mmm-yy h-mm-ss")

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

With Destwb
    .SaveAs TempFilePath & TempFileName & FileExtStr, _
            FileFormat:=FileFormatNum
    On Error Resume Next
    With OutMail
        .To = "aab2323@example.com"
        .CC = ""
        .BCC = ""
        .Subject = "Test Subject"               
        Call ProtectVBProject.ProtectVBProject(Destwb, "pa$$w0rd!")                
        .Attachments.Add Destwb.FullName
        .Send
    End With
    On Error GoTo 0
    .Close SaveChanges:=False
    Destwb.Close SaveChanges:=False
End With

Kill TempFilePath & TempFileName & FileExtStr

Set OutMail = Nothing
Set OutApp = Nothing

A quick look reveals no bad coding style. Where exactly does the error occur and what (if possible) is the error type and description? — WGS, Nov 06 '13 at 01:43
@BK201 I resolved the Automation Error, I was closing the book twice and the second .close was throwing the error. — user793468, Nov 06 '13 at 18:36

score 1 · Answer 1 · edited May 23 '17 at 11:50

In my experience, SendKeys (for locking/unlocking projects) is not very trustworthy and many/most people will advise against using them.

Take a look at this blog for an alternative way to accomplish what you're trying. (This SO post is also helpful.)

While those links deal with unprotecting a project, I'm sure the methods can also be used to protect the project.

If you'd rather not use that route, try using a "template," where the "template" workbook already has the proper code and the project is locked. Then you just open the template, paste whatever content you need into the workbook, and then SaveAs. (This is the method I used in a similar situation.) Once saved, you can send your file via email.

score 0 · Answer 2 · answered Nov 06 '13 at 03:01

0

Try removing the extra reference to ProtectVBProject.

Your code says Call ProtectVBProject.ProtectVBProject(Destwb, "pa$$w0rd!") yet I think it should only say Call ProtectVBProject(Destwb, "pa$$w0rd!")

answered Nov 06 '13 at 03:01

Instant Breakfast

1,383
2
14
28

My module is named ProtectVBProject and my sub is named ProtectVBProject. This is how to call a sub from a module. ModuleName.SubName() – user793468 Nov 06 '13 at 05:50
1

Hm, even though that may work, it's really bad practice to give different things identical names... – Jean-François Corbett Nov 06 '13 at 07:59

score 0 · Answer 3 · answered Nov 06 '13 at 03:06

0

I Think it may be because you are trying to send the workbook while it is still open. You can do this manually without issue but when I try to do this through SMTP it fails.

I guess you only want to send (as opposed to saving) the protected workbook so your options are either save a separate copy that is protected, close it and then send the email and kill the file. Or you could save it, close, send, re-open and un-protect using similar code to how you protected.

answered Nov 06 '13 at 03:06

Simon1979

2,110
1
12
15

I am not trying to send the workbook which is open, I save a temp copy and send that copy – user793468 Nov 06 '13 at 05:52
1

Then that may be your problem, I don't think you can access the VBProject property without the workbook being open - you would get an automation error if you tried to. BK201 asked where the error happens, could you let us know? And also maybe post a little more of you code, the setting of DestWb and SourceWb would be the primary requirements to help further. – Simon1979 Nov 06 '13 at 07:02

Automation Error when trying to protect VB project and email it as attachment

3 Answers3