?
Solved

Copy file and email to macro

Posted on 2012-09-20
1
Medium Priority
?
464 Views
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
   Sheet3.Copy
   Set wbk = ActiveWorkbook
   
   wbk.SaveAs Filename:=OutFile  ' save temp file
   
   ' construct email, attaching temp file to email
   Set OutApp = CreateObject("Outlook.Application")
   OutApp.Session.Logon
   Set OutMail = OutApp.CreateItem(0)
   
   With OutMail
      .Subject = "new file "
      .Body = "please see attachment"
      .Attachments.Add OutFile
      .Display
   End With
   
   Set OutMail = Nothing
   Set OutApp = Nothing
   
   wbk.Close False
   If Dir(OutFile) <> "" Then Kill OutFile   ' delete the temp file
   
ExitNow:
    Application.DisplayAlerts = True
   
    Exit Sub
Err:
    MsgBox Err.Description
End Sub
0
Comment
Question by:Kiwi-123
1 Comment
 
LVL 35

Accepted Solution

by:
Norie earned 2000 total points
ID: 38420359
Change this,
Sheet3.Copy

Open in new window


to this:
Sheets.Copy

Open in new window

0

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