Copy file and email to macro
Posted on 2012-09-20
Can you help me alter the code below so that it copies the entire workbook and not just sheet 3.
Private Sub CommandButton1_Click()
Dim wb1 As Workbook, wb2 As Workbook
Dim Sht1 As Worksheet, strTempShtname As String, TmpSheet As Worksheet
Dim pass As String, obj As OLEObject
pass = InputBox("please enter a password")
If Len(Trim(pass)) = 0 Then GoTo ExitNow
If pass <> "gaz5" Then
MsgBox "Incorrect Password"
Application.DisplayAlerts = False
On Error GoTo Err
Dim wbk As Workbook
'Save temporary file so that it can be attached to an email
OutFile = Environ$("temp") & "\" & Format(Now, "DD-MM-YYYY") & " Copy" & ".xls" 'construct temp file name
If Dir(OutFile) <> "" Then Kill OutFile ' delete any previously saved temp file
' copy sheet to new workbook
Set wbk = ActiveWorkbook
wbk.SaveAs Filename:=OutFile ' save temp file
' construct email, attaching temp file to email
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
.Subject = "new file "
.Body = "please see attachment"
Set OutMail = Nothing
Set OutApp = Nothing
If Dir(OutFile) <> "" Then Kill OutFile ' delete the temp file
Application.DisplayAlerts = True