How to make this VBA code attach a workbook instead of a worksheet to an email

[ajgcook1983]

Hello all!

Last week I was very kindly assisted with a similar query and once again, I find myself struggling on a Friday evening.

The code below is excellent and works very well - the user clicks a button in the worksheet and the macro generates an email, auto-fills the 'To', 'CC', 'Subject', 'Body' fields and attaches the worksheet in question.

HOWEVER, I now need the code to attach the entire workbook (preferably as a macro-enabled workbook)...

Can somebody please assist - I'm sure it's a very simple edit, but everything I have tried thus far has caused a script error.

Please help!

 Sub Email_Change_Submission_Sheet()

    Dim oApp As Object
    Dim oMail As Object
    Dim LWorkbook As Workbook
    Dim LFileName As String

    'Turn off screen updating
    Application.ScreenUpdating = False

    'Copy the active worksheet and save to a temporary workbook
    ActiveSheet.Copy
    Set LWorkbook = ActiveWorkbook

    'Create a temporary file in your current directory that uses the name
    ' of the sheet as the filename
    LFileName = LWorkbook.Worksheets(1).Name
    On Error Resume Next
    'Delete the file if it already exists
    Kill LFileName
    On Error GoTo 0
    'Save temporary file
    LWorkbook.SaveAs Filename:=LFileName

    'Create an Outlook object and new mail message
    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(0)
    
    'Set mail attributes (uncomment lines to enter attributes)
    ' In this example, only the attachment is being added to the mail message
    With oMail
        .To = "XYZ@XYZ.COM"
        .CC = ActiveWorkbook.Worksheets("Change Paper").Range("G10").Value
        .Subject = "More Information Required re. your Change Paper - Comments Attached"
        .body = "Dear XX," & vbCrLf & vbCrLf & _
                "I have reviewed your proposed Change and am unable to support it at the present time without further information." & vbCrLf & vbCrLf & _
                "Please see attached for my comments and respond ASAP." & vbCrLf & vbCrLf & _
                "Kind regards,"
        .Attachments.Add LWorkbook.FullName
        .Display
    End With

    'Delete the temporary file and close temporary Workbook
    LWorkbook.ChangeFileAccess Mode:=xlReadOnly
    Kill LWorkbook.FullName
    LWorkbook.Close SaveChanges:=False

    'Turn back on screen updating
    Application.ScreenUpdating = True
    Set oMail = Nothing
    Set oApp = Nothing
    
End Sub

Kind regards,

Alex

[patel]

eliminate line

    ActiveSheet.Copy

[ajgcook1983]

Again, so simple.

Brilliant - thanks!