Excel

Email active worksheet using Outlook

Ease of Use

Easy

Version tested with

2003 

Submitted by:

Ken Puls

Description:

This code will email the active worksheet using Microsoft Outlook. It is set by default to open a new email and attach the sheet, with no recipient or subject filled in (although this can be easily changed.) While similar to http://www.vbaexpress.com/kb/getarticle.php?kb_id=97, it does not require the user to set a reference in the Visual Basic Editor. 

Discussion:

Sometimes you may want to email one specific worksheet from a file to a user. This code will allow you to do that. If you would like to hard code an email address to send to, simply update someone@somedomain.com to the appropriate email address, and remove the ' from the beginning of the line. To hard code a subject, change Look at my workbook! and remove the ' from the beginning of the line. NOTE: If you want to send the workbook with all formulas removed, you will need to Copy/Paste values before saving the temporary file. Please ask for help in the Excel forum if you would like to do this. This code was inspired by Juan Pablo Gonzalez! 

Code:

instructions for use 

			
Sub EmailWithOutlook() 
     'Variable declaration
    Dim oApp As Object, _ 
    oMail As Object, _ 
    WB As Workbook, _ 
    FileName As String 
     
     'Turn off screen updating
    Application.ScreenUpdating = False 
     
     'Make a copy of the active sheet and save it to
     'a temporary file
    ActiveSheet.Copy 
    Set WB = ActiveWorkbook 
    FileName = "Temp.xls" 
    On Error Resume Next 
    Kill "C:\" & FileName 
    On Error GoTo 0 
    WB.SaveAs FileName:="C:\" & FileName 
     
     'Create and show the outlook mail item
    Set oApp = CreateObject("Outlook.Application") 
    Set oMail = oApp.CreateItem(0) 
    With oMail 
         'Uncomment the line below to hard code a recipient
         '.To = "someone@somedomain.com"
         'Uncomment the line below to hard code a subject
         '.Subject = "Look at my workbook!"
        .Attachments.Add WB.FullName 
        .Display 
    End With 
     
     'Delete the temporary file
    WB.ChangeFileAccess Mode:=xlReadOnly 
    Kill WB.FullName 
    WB.Close SaveChanges:=False 
     
     'Restore screen updating and release Outlook
    Application.ScreenUpdating = True 
    Set oMail = Nothing 
    Set oApp = Nothing 
End Sub

How to use:

  1. Copy above code.
  2. In Excel press Alt + F11 to enter the VBE.
  3. Press Ctrl + R to show the Project Explorer.
  4. Right-click desired file on left (in bold).
  5. Choose Insert -> Module.
  6. Paste code into the right pane.
  7. Press Alt + Q to close the VBE.
  8. Save workbook before any other changes.
 

Test the code:

  1. From the main Excel interface, press Alt + F8 to open the macro dialog box
  2. Choose EmailWithOutlook and click Run
  3. Wait for the email to be created
  4. Right click the attachment in the email and choose Open
  5. Verify that only the active sheet exists in the file
 

Sample File:

MailSheet.zip 8.36KB 

Approved by mdmackillop


This entry has been viewed 379 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express