Excel

Email a Worksheet or Workbook Using Outlook

Ease of Use

Easy

Version tested with

2002 

Submitted by:

Jacob Hilderbrand

Description:

Programmatically email the active worksheet or the entire workbook using Outlook. 

Discussion:

You need to email a workbook or a certain worksheet periodically and you are tired of doing this manually. VBA can do most of the work for you. This example uses Outlook to send the email. 

Code:

instructions for use

			

Option Explicit Sub eMailActiveWorksheet() Dim OL As Object Dim EmailItem As Object Dim Wb As Workbook Dim FileName As String Dim y As Long Dim TempChar As String Dim SaveName As String Application.ScreenUpdating = False Set OL = CreateObject("Outlook.Application") Set EmailItem = OL.CreateItem(olMailItem) FileName = ActiveSheet.Name & " - " & ActiveWorkbook.Name For y = 1 To Len(FileName) TempChar = Mid(FileName, y, 1) Select Case TempChar Case Is = "/", "\", "*", "?", """", "<", ">", "|", ":" Case Else SaveName = SaveName & TempChar End Select Next y ActiveSheet.Copy Set Wb = ActiveWorkbook Wb.SaveAs SaveName Wb.ChangeFileAccess xlReadOnly With EmailItem .Subject = "Insert Subject Here" .Body = "Insert message here" & vbCrLf & _ "Line 2" & vbCrLf & _ "Line 3" .To = "User@Domain.Com" .Importance = olImportanceNormal 'Or olImprotanceHigh Or olImprotanceLow .Attachments.Add Wb.FullName .Send End With Kill Wb.FullName Wb.Close False Application.ScreenUpdating = True Set Wb = Nothing Set OL = Nothing Set EmailItem = Nothing End Sub Sub eMailActiveWorkbook() Dim OL As Object Dim EmailItem As Object Dim Wb As Workbook Application.ScreenUpdating = False Set OL = CreateObject("Outlook.Application") Set EmailItem = OL.CreateItem(olMailItem) Set Wb = ActiveWorkbook Wb.Save With EmailItem .Subject = "Insert Subject Here" .Body = "Insert message here" & vbCrLf & _ "Line 2" & vbCrLf & _ "Line 3" .To = "User@Domain.Com" .Importance = olImportanceNormal 'Or olImprotanceHigh Or olImprotanceLow .Attachments.Add Wb.FullName .Send End With Application.ScreenUpdating = True Set Wb = Nothing Set OL = Nothing Set EmailItem = Nothing End Sub

How to use:

  1. Open Excel.
  2. Press Alt + F11 to open VBE.
  3. From the menu, choose Insert-Module.
  4. Paste the code in the window at right.
  5. Modify the code as necessary according to: .Subject, .Body .To, and .Importance.
  6. Hit Tools-References from the menu.
  7. Set a reference for "Microsoft Outlook X.0 Object Library". Note that the number represented by X.0 will be vary according to the version of Office.
  8. Close the VBE (Alt + Q or press the X in the top right hand corner).
 

Test the code:

  1. Go to the workbook/worksheet that you want to email.
  2. Hit Tools-Macro-Macros and double-click eMailActiveWorksheet or eMailActiveWorkbook.
 

Sample File:

Email From Excel.zip 7.43KB 

Approved by mdmackillop


This entry has been viewed 2205 times.

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