How to make this VBA code attach a workbook instead of a worksheet to an email
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!
Code:
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
