Copy file and email to macro

Posted on 2012-09-20
Medium Priority
Last Modified: 2012-09-21
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"
        Exit Sub
    End If
    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)
   With OutMail
      .Subject = "new file "
      .Body = "please see attachment"
      .Attachments.Add OutFile
   End With
   Set OutMail = Nothing
   Set OutApp = Nothing
   wbk.Close False
   If Dir(OutFile) <> "" Then Kill OutFile   ' delete the temp file
    Application.DisplayAlerts = True
    Exit Sub
    MsgBox Err.Description
End Sub
Question by:Kiwi-123
1 Comment
LVL 35

Accepted Solution

Norie earned 2000 total points
ID: 38420359
Change this,

Open in new window

to this:

Open in new window


Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question