MS Excel: Email the active worksheet in Excel 2007
Question: In Excel 2007, I have a large workbook at is 97MB in size. Is there a way to create a button on a worksheet so that I can e-mail only that worksheet and not the entire workbook?
Answer:Let's take a look at an example.
Download Excel spreadsheet (as demonstrated below)
In our spreadsheet, we have a button that runs VBA code to e-mail only Sheet1 and not the entire workbook. When you first open this spreadsheet, you will be presented with a Security Warning because of the macro. Click on the Options button next to the Security Warning message.
When the Microsoft Office Security Options window appears, select "Enable this content" and then click on the OK button.
Now when you click on the E-Mail Sheet button and if your e-mail client is Outlook, a new Outlook message should be created with Sheet1.xlsx as the attachment (which is ONLY Sheet1 from the workbook).
You can view the VBA code by opening the Visual Basic editor.
Macro Code:
The macro code looks like this:
Sub Email_Sheet()
Dim oApp As Object
Dim oMail As Object
Dim LWorkbook As Workbook
Dim LFileName As String'Turn off screen updating
Application.ScreenUpdating = False'Copy the active worksheet and save to a temporary workbook
ActiveSheet.Copy
Set LWorkbook = ActiveWorkbook'Create a temporary file in your current directory that uses the name
' of the sheet as the filename
LFileName = LWorkbook.Worksheets(1).Name
On Error Resume Next
'Delete the file if it already exists
Kill LFileName
On Error GoTo 0
'Save temporary file
LWorkbook.SaveAs FileName:=LFileName'Create an Outlook object and new mail message
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
'Set mail attributes (uncomment lines to enter attributes)
' In this example, only the attachment is being added to the mail message
With oMail
'.To = "user@yahoo.com"
'.Subject = "Subject"
'.body = "This is the body of the message." & vbCrLf & vbCrLf & _
'"Attached is the file"
.Attachments.Add LWorkbook.FullName
.Display
End With'Delete the temporary file and close temporary Workbook
LWorkbook.ChangeFileAccess Mode:=xlReadOnly
Kill LWorkbook.FullName
LWorkbook.Close SaveChanges:=False'Turn back on screen updating
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing
End Sub