Excel - Mail Single Worksheet

[dhilipkumar]

We often get asked how to e-mail a single worksheet from Excel, here I am hopefully going to show you one method to help.

Simplistically, we are going to:

Create an instantiation of Outlook
Create a new Workbook
Copy the entire ActiveWorkSheet into the new Workbook
Copy the formatting across
Save the Workbook with a suitable filename to a temporary directory
Attach the workbook and send it to our recipients
Delete the temporary file
Clean up


Here's how we do this in VBa:


Code:
Sub eMailActiveWorksheet()

' Written by Richard J Hare
' Haresoftware 2003
' http://www.haresoftware.com
     
    Dim OL As Object ' Outlook instantiation
    Dim EmailItem As Object ' A new mail item (e-mail)
    Dim FileName As String ' The name of the file we are attaching
    Dim lngLoop As Long ' counter for the FileName length
    Dim TempChar As String ' used for the loop to test for unusable characters
    Dim SaveName As String ' Attachment's new name, after cleaning
     
    Application.ScreenUpdating = False ' speed up Excel processing time
    Set OL = CreateObject("Outlook.Application") ' New Outlook application
    Set EmailItem = OL.CreateItem(olMailItem) ' new MailItem
    FileName = ActiveSheet.Name & " - " & ActiveWorkbook.Name ' create a filename on the fly
    For lngLoop = 1 To Len(FileName) ' error check for unusable chars in the filename
        TempChar = Mid(FileName, y, 1)
        Select Case TempChar
        Case Is = "/", "\", "*", "?", """", "<", ">", "/"
        Case Else
            SaveName = SaveName & TempChar
        End Select
    Next lngLoop
    ActiveSheet.Cells.Copy ' copy the contents of the ActiveSheet
    Workbooks.Add ' create a new workbook
    Selection.PasteSpecial Paste:=xlValues ' paste the worksheet values into the new book
    Selection.PasteSpecial Paste:=xlFormats ' and their formats
    ActiveWorkbook.SaveAs "C:\" & SaveName ' temp file attachment location
    ActiveWorkbook.ChangeFileAccess xlReadOnly ' make access read only
    With EmailItem ' with the newly created e-mail
        .Subject = ActiveWorkbook.Name
        .Body = "This is an example of a single worksheet sent by VBa mail"
        .To = "Enter your Recipients here - change"
        .Importance = olImportanceNormal 'Or olImportanceHigh Or olImportanceLow
        .Attachments.Add "C:\" & SaveName
        .Send ' send the worksheet
    End With
    Kill "C:\" & SaveName ' delete the temporary attachment
    ActiveWorkbook.Close False ' close down the workbook without saving (single sheet)
     
    Application.ScreenUpdating = True ' always remember to switch it back on!!
     
    Set OL = Nothing ' clean down memory
    Set EmailItem = Nothing
     
End Sub


Pretty simple to be honest, especially when someone has written it for you!!!!