copy active worksheet plus another worksheet

[Guest]

No luck answering this one myself.
I'd like to send the active worksheet plus a copy of another worksheet in my
workbook.

here is my code I tried but Iget subscript out of range

Private Sub Send1_Click()
Dim strDate As String
ActiveSheet.Copy
Worksheets("Main").Copy
strDate = Format(Date, "dd-mm-yy") & " " & Format (Time, "h-mm-ss")
ActiveWorkbook.SaveAs "NewEmployeeData.xls"
ActiveWorkbook.SendMail "", _
"Employee Attendance Data"
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
End Sub

Thanx

 

[Tom Ogilvy]

Private Sub Send1_Click()
Dim strDate As String
ActiveSheet.Copy
ThisWorkbook.Worksheets("Main").Copy After:= _
Activeworkbook.Worksheets(1)
strDate = Format(Date, "dd-mm-yy") & " " & Format(Time, "h-mm-ss")
ActiveWorkbook.SendMail "", _
"Employee Attendance Data"
ActiveWorkbook.Close False
End Sub

You don't do anything with strDate and you don't have an email address in
SendMail.

If you want the workbook to have a specific name


Private Sub Send1_Click()
Dim strDate As String, aFname as String
ActiveSheet.Copy
ThisWorkbook.Worksheets("Main").Copy After:= _
Activeworkbook.Worksheets(1)
strDate = Format(Date, "dd-mm-yy") & " " & Format (Time, "h-mm-ss")
ActiveWorkbook.SaveAs "NewEmployeeData.xls"
sFname = ActiveWorkbook.FullName
ActiveWorkbook.SendMail "", _
"Employee Attendance Data"
ActiveWorkbook.Close False
Kill sFname
End Sub