Copy file and email to macro

Posted on 2012-09-20
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 33

    Accepted Solution

    Change this,

    Open in new window

    to this:

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
    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.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now