Sub Send_Email()
Dim outlookApp As Object
Dim outlookMail As Object
Dim wbTemp As Workbook
Dim strFileName As String
Set outlookApp = CreateObject("Outlook.Application")
Set outlookMail = outlookApp.CreateItem(0)
ActiveSheet.Copy
Set wbTemp = ActiveWorkbook
wbTemp.SaveAs "C:\ Test\" & wbTemp.Sheets(1).Name & ".xlsx", xlOpenXMLWorkbook
strFileName = wbTemp.FullName
wbTemp.Close False
With outlookMail
.To = "recipient@yourco.com"
.Subject = "Test email File"
.BodyFormat = 2
.HTMLBody = "Hi,<p>This is a test email from Excel using VBA."
.Attachments.Add strFileName
.Importance = olImportanceHigh
.Send
End With
Set outlookMail = Nothing
Set outlookApp = Nothing
Kill strFileName
End Sub
Worksheet.Copy
will create a new workbook from a worksheet. I assume you can then just continue with the remainder of the scrip to send that workbook.