vba script to copy modules from one book to another

[JannetteChristie]

Hi,

I am hoping that someone can help with this query. I have a worksheet that get populated with information and then I have some vb code that when requested, it copies the sheets and attaches it to an email ready for sending. The original sheet has a number of modules, what I am trying to achieve is that the file attached in the email also has one of the modules available for the user to use via a button. I have attached the code that I am using but the module is not being created in the new file.

Hoping that I make some sense.

[COLOR=#a52a2a]Sub SendEmail()[/COLOR]
[COLOR=#a52a2a]    Dim EmailAddress As String[/COLOR]
[COLOR=#a52a2a]    Dim JobName As String[/COLOR]
[COLOR=#a52a2a]    Dim TempFileName As String[/COLOR]
[COLOR=#a52a2a]    Dim Destwb As Workbook[/COLOR]
[COLOR=#a52a2a]    Dim Fname As String[/COLOR]
[COLOR=#a52a2a]        [/COLOR]
[COLOR=#a52a2a]        [/COLOR]
[COLOR=#a52a2a]    On Error GoTo ErrorHandler1[/COLOR]
[COLOR=#a52a2a]    EmailAddress = Sheets("Sheet2").Range("HB2").Value[/COLOR]
[COLOR=#a52a2a]    On Error Resume Next[/COLOR]
[COLOR=#a52a2a]    Appointment = Range("AB55").Value[/COLOR]
[COLOR=#a52a2a]    JobName = "Dutypoint Commissioning Report " & Range("AB21").Value & " " & Range("F21").Value & " " & Range("AB23").Value[/COLOR]
[COLOR=#a52a2a]    TempFileName = Environ$("temp") & "" & JobName & ".xlsm"[/COLOR]
[COLOR=#a52a2a]    'With Application[/COLOR]
[COLOR=#a52a2a]        '.ScreenUpdating = False[/COLOR]
[COLOR=#a52a2a]        '.EnableEvents = False[/COLOR]
[COLOR=#a52a2a]    'End With[/COLOR]
[COLOR=#a52a2a]    [/COLOR]
[COLOR=#a52a2a]    [/COLOR]
[COLOR=#a52a2a]    ActiveSheet.Copy[/COLOR]
[COLOR=#a52a2a]    Set Destwb = ActiveWorkbook[/COLOR]
[COLOR=#a52a2a]    Fname = Environ$("temp") & "\code.txt"[/COLOR]
[COLOR=#a52a2a]    ThisWorkbook.VBProject.VBComponents("SubmitDataModule").Export Fname[/COLOR]
[COLOR=#a52a2a]        [/COLOR]
[COLOR=#a52a2a]                [/COLOR]
[COLOR=#a52a2a]    With Destwb.Sheets(1).UsedRange[/COLOR]
[COLOR=#a52a2a]            .Cells.Copy[/COLOR]
[COLOR=#a52a2a]            .Cells.PasteSpecial xlPasteValues[/COLOR]
[COLOR=#a52a2a]           .Cells(1).Select[/COLOR]
[COLOR=#a52a2a]    End With[/COLOR]
[COLOR=#a52a2a]    Application.CutCopyMode = False[/COLOR]
[COLOR=#a52a2a]   [/COLOR]
[COLOR=#a52a2a]    'JC added 02.05.18[/COLOR]
[COLOR=#a52a2a]   ' Application.DisplayAlerts = False[/COLOR]
[COLOR=#a52a2a]   ' ActiveWorkbook.SaveAs Filename:=TempFileName, FileFormat:=53[/COLOR]
[COLOR=#a52a2a]   ' ActiveWorkbook.Close SaveChanges:=False[/COLOR]
[COLOR=#a52a2a]   ' Application.DisplayAlerts = True[/COLOR]
[COLOR=#a52a2a]    'JC added 02.05.18[/COLOR]
[COLOR=#a52a2a]    [/COLOR]
[COLOR=#a52a2a]    [/COLOR]
[COLOR=#a52a2a]    [/COLOR]
[COLOR=#a52a2a]   [/COLOR]
[COLOR=#a52a2a]    [/COLOR]
[COLOR=#a52a2a]    'JC added 06.02.18[/COLOR]
[COLOR=#a52a2a]    Set OutApp = CreateObject("Outlook.Application")[/COLOR]
[COLOR=#a52a2a]    Set OutMail = OutApp.CreateItem(0)[/COLOR]
[COLOR=#a52a2a]    On Error Resume Next[/COLOR]
[COLOR=#a52a2a]    With OutMail[/COLOR]
[COLOR=#a52a2a]    'JC added 06.02.18[/COLOR]
[COLOR=#a52a2a]        [/COLOR]
[COLOR=#a52a2a]    With Destwb[/COLOR]
[COLOR=#a52a2a]    [/COLOR]
[COLOR=#a52a2a]    Workbooks(TempFileName).VBProject.VBComponents.Import Fname[/COLOR]
[COLOR=#a52a2a]    [/COLOR]
[COLOR=#a52a2a]         .SaveAs TempFileName, FileFormat:=52[/COLOR]
[COLOR=#a52a2a]    '    .SendMail EmailAddress, JobName & Format(Appointment, "")[/COLOR]
[COLOR=#a52a2a]    [/COLOR]
[COLOR=#a52a2a]        .Close SaveChanges:=False[/COLOR]
[COLOR=#a52a2a]        [/COLOR]
[COLOR=#a52a2a]    End With[/COLOR]
[COLOR=#a52a2a]   [/COLOR]
[COLOR=#a52a2a]     'JC added 06.02.18[/COLOR]
[COLOR=#a52a2a]    .to = ""[/COLOR]
[COLOR=#a52a2a]    .Subject = JobName & Format(Appointment, "")[/COLOR]
[COLOR=#a52a2a]    .Attachments.Add TempFileName[/COLOR]
[COLOR=#a52a2a]    .Display[/COLOR]
[COLOR=#a52a2a]    End With[/COLOR]
[COLOR=#a52a2a]      'JC added 06.02.18[/COLOR]
[COLOR=#a52a2a]      [/COLOR]
[COLOR=#a52a2a]     Kill TempFileName[/COLOR]
[COLOR=#a52a2a]    On Error GoTo 0[/COLOR]
[COLOR=#a52a2a]    [/COLOR]
[COLOR=#a52a2a]    [/COLOR]
[COLOR=#a52a2a]    [/COLOR]
[COLOR=#a52a2a]    'With Application[/COLOR]
[COLOR=#a52a2a]        '.ScreenUpdating = True[/COLOR]
[COLOR=#a52a2a]        '.EnableEvents = True[/COLOR]
[COLOR=#a52a2a]    'End With[/COLOR]
[COLOR=#a52a2a]Exit Sub[/COLOR]
[COLOR=#a52a2a]
[/COLOR]
[COLOR=#a52a2a]ErrorHandler1:[/COLOR]
[COLOR=#a52a2a]EmailAddress = "serviceadmin@dutypoint.net"[/COLOR]
[COLOR=#a52a2a]Resume Next[/COLOR]
[COLOR=#a52a2a]
[/COLOR]
[COLOR=#a52a2a]End Sub[/COLOR]

 

[Jerry Sullivan]

Hi Jannette,

When this part of the code is being executed, Workbooks(TempFileName) doesn't exist yet, the new workbook has a default name like Book1.xlsm until the workbook is saved on the subsequent line.

 With Destwb
[COLOR="#0000CD"]    Workbooks(TempFileName).[/COLOR]VBProject.VBComponents.Import Fname
 
   .SaveAs TempFileName, FileFormat:=52
    ' .SendMail EmailAddress, JobName & Format(Appointment, "")

    .Close SaveChanges:=False
 End With

One way to fix that is to reference Destwb like this...

 With Destwb
[COLOR="#0000CD"]    .[/COLOR]VBProject.VBComponents.Import Fname

    .SaveAs TempFileName, FileFormat:=52
    ' .SendMail EmailAddress, JobName & Format(Appointment, "")

    .Close SaveChanges:=False
 End With

Another possible problem is that this code will only work if the user has set their macro settings to trust access to the VBA project object. You can check or set that here:

Trust Center > Macro Settings > check "Trust access to the VBA project object model"

If the code is going to be used by anyone but yourself, I'd recommend you not impose the need to change security settings on the users.

Two alternatives to consider are:

1. Use a template file that already has the code module saved in it. You would modify your code to create a new workbook from the template, then copy your worksheet into it in lieu of a blank workbook.

2. Store the VBA code in the Worksheet Code Module of the worksheet you are copying. Typically it's a better design practice to only include code in this module that is dedicated to this worksheet, but in this case I think it would be the lesser of evils to do this instead of having the user change their trust settings.

 

[JannetteChristie]

Thankyou so much, this now works perfectly.