Copying Macros To A New Workbook

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,564
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
In my VBA project, a worksheet is created, copied to a new book and saved. This worksheet is like an internal user order form and has a few macro embedded shapes on it for use by the user using the form which is emailed to them.

The macros are stored in the workbook that created the initial worksheet. The users receiving the email with the form in it with the macro embedded shapes do not have access to the original creation workbook, so the macros embedded on the attachment they receive are not executable.

How can I send out that document with the macros available to the user? (I know this is can be an unsafe practice from the recipient standpoint, but no harm is intended with these macros.)

My email distribution code is below ...

Code:
Sub Mail_workbook_Outlook_3()
    'Working in Excel 2000-2016
    'Mail a changed copy of the ActiveWorkbook with another file name
    'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
    
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim OutApp As Object
    Dim OutMail As Object
    Dim Shp As Shape

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    'Make a copy of the file/Open it/Edit it/Mail it/Delete it

    TempFilePath = Environ$("temp") & "\"
    FileExtStr = "." & LCase(Right(wb_tsrf.Name, Len(wb_tsrf.Name) - InStrRev(wb_tsrf.Name, ".", , 1)))
    fnlen = Len(wb_tsrf.Name)
    fnlen = fnlen - 5
    fn = Left(wb_tsrf.Name, fnlen)
    sn = Worksheets(1).Name
    
    TempFileName = fn & "SR"
    
    wb_tsrf.SaveCopyAs TempFilePath & TempFileName & FileExtStr
    Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)


    '**************Add code to edit the file here********************

    With wb2.Worksheets(1)
        .Unprotect
        For Each Shp In ActiveSheet.Shapes
            'MsgBox Shp.Name
            If Shp.Name = "Group 12" Then
                Shp.Delete
            ElseIf Shp.Name = "Group 3" Then
                Shp.Delete
            End If
        Next Shp
        .Protect
    End With
    
    'Save the file after we changed it with the code above
    wb2.Save

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

    On Error Resume Next
    With OutMail
        .to = "recipient@email.ca"
        .CC = ""
        .BCC = ""
        .Subject = "### ##### ## SRF (" & sn & ")"
        .Body = "The embedded macros are safe. Choosing to not enable them will not affect the document."
        .Attachments.Add wb2.FullName
        .Send   'or use .Display
    End With
    On Error GoTo 0
    wb2.Close savechanges:=False

    'Delete the file
    Kill TempFilePath & TempFileName & FileExtStr

    Set OutMail = Nothing
    Set OutApp = Nothing

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    MsgBox "Message has been sent."
    Stop
End Sub

"wb_tsrf" is the original worksheet created by my project that has the embedded macro shapes on it. It gets copied as a temporary file in which some of the shapes are removed (redundant to end user). It is this file that gets attached to the email. The remaining macros on the copied and emailed version don't work at the user end and I kinda hope they can be. I can use the macros on both the original and copied version as they can easily access the document holding the macros.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Re: Copying Macros To A New Document

Your end-users likely do not have the necessary References (libraries) active to run the macro.
I think when you create an add-in, there is no problem.
In the VBE, TOOLS\REFERENCES... select the necessary Libraries.

And while I don't know the answer to what I think is the right question.... How can you have a macro run or call for the necessary References or libraries so the macro will work?
 
Last edited:
Upvote 0
Re: Copying Macros To A New Document

Thanks Spiller for contributing to a solution.

On further research, I found that what I simply (really? simply??) need to do is import the module holding the macros for the buttons from the master workbook to the target. The code in the macros is not written with any public variables or direct references to specific source worksheets etc. (all references are to activesheet or activeworkbook in the macros).

I have changed my original code and added a procedure to import the module between workbooks. But it doesn't seem to be working.

My updated code (changes in blue)
Rich (BB code):
Sub Mail_workbook_Outlook_3()
    'Working in Excel 2000-2016
    'Mail a changed copy of the ActiveWorkbook with another file name
    'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
    
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim OutApp As Object
    Dim OutMail As Object
    Dim Shp As Shape

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    'Make a copy of the file/Open it/Edit it/Mail it/Delete it

    TempFilePath = Environ$("temp") & "\"
    FileExtStr = "." & LCase(Right(wb_tsrf.Name, Len(wb_tsrf.Name) - InStrRev(wb_tsrf.Name, ".", , 1)))
    fnlen = Len(wb_tsrf.Name)
    fnlen = fnlen - 5
    fn = Left(wb_tsrf.Name, fnlen)
    sn = Worksheets(1).Name
    
    TempFileName = fn & "SR"
    
    wb_tsrf.SaveCopyAs TempFilePath & TempFileName & FileExtStr
    Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)


    '**************Add code to edit the file here********************

    With wb2.Worksheets(1)
        .Unprotect
        For Each Shp In ActiveSheet.Shapes
            'MsgBox Shp.Name
            If Shp.Name = "Group 12" Then
                Shp.Delete
            ElseIf Shp.Name = "Group 3" Then
                Shp.Delete
            End If
        Next Shp
        .Protect
        Set wb_tar = Workbooks(TempFileName & ".xlsx")
        Debug.Print wb_tar.Name
        CopyModule    
    End With
    
    'Save the file after we changed it with the code above
    'wb2.Save
    wb2.SaveAs TempFilePath & TempFileName & ".xlsm"

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

    On Error Resume Next
    With OutMail
        .to = "recipient@email.ca"
        .CC = ""
        .BCC = ""
        .Subject = "### ##### ## SRF (" & sn & ")"
        .Body = "The embedded macros are safe. Choosing to not enable them will not affect the document."
        .Attachments.Add wb2.FullName
        .Send   'or use .Display
    End With
    On Error GoTo 0
    wb2.Close savechanges:=False

    'Delete the file
    Kill TempFilePath & TempFileName & FileExtStr

    Set OutMail = Nothing
    Set OutApp = Nothing

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    MsgBox "Message has been sent."
    Stop
End Sub

Here is the code (thanks to ExcelTips)to copy the module (which doesn't seem to be working)
Rich (BB code):
Sub CopyModule() '(SourceWB As Workbook, strModuleName As String, _
    TargetWB As Workbook)

Dim strFolder As String, strTempFile As String
    strFolder = wb_mat.Path 'source workbook
    strModuleName = "Module4.bas"
    If Len(strFolder) = 0 Then strFolder = CurDir
    strFolder = strFolder & "\"
    strTempFile = strFolder & "Module4.bas"
    On Error Resume Next
    wb_mat.VBProject.VBComponents(strModuleName).Export strTempFile
    wb_tar.VBProject.VBComponents.Import strTempFile
    Kill strTempFile
    On Error GoTo 0
End Sub

Questions ....

1) Can anyone provide a solution as to why the exporting of the module isn't happening? There are no errors, it just doesn't happen.
2) The line is giving me an error ("Method 'SaveAs' of object '_Workbook' failed.") . It's trying the save the workbook with the newly imported module as an .xlsm file (which I believe if you have macros has to be saved as). I'm assuming it's failing because there are no macros associated with that workbook since the import failed. If issue #1 is taken care of, will that eliminate this error? Or will I still have this error? What could I do then?
 
Last edited:
Upvote 0
Re: Copying Macros To A New Document

Cross posted https://www.excelforum.com/excel-general/1260711-copying-vba-modules-between-workbooks.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 
Upvote 0
Re: Copying Macros To A New Document

A solution has been found. It did not involve anything as comnplex as having to copy and paste macros between workbooks.

Cross posted.
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top