MS Excel: Email the active worksheet in Excel 2007
Question: In Microsoft 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 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.
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 = "email@example.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