Multiple Apps

Automating Outlook to Send an Email with an Attachment

Ease of Use

Intermediate

Version tested with

2000,2002,2003 

Submitted by:

mark007

Description:

This code will automate Outlook to create a new email with the given attachment. 

Discussion:

The following code can be used to automate Outlook from Excel, Word, Access or any VBA enabled application. It could also be used in a VB6 app. It will create a new mail message and attach the file specified. In it's current form it will display this message for you to check before hitting send, however it can easily be modified as suggested in the code to send it straight away. 

Code:

instructions for use 

			
Option Explicit 
 
Sub SendMail() 
     
    Dim olApp As Outlook.Application 
    Dim olMail As Outlook.MailItem 
    Dim blRunning As boolean 
     
     'get application
    blRunning=True 
    On Error Resume Next 
    Set olApp = GetObject(, "Outlook.Application") 
    If olApp Is Nothing Then 
        Set olApp = New Outlook.Application 
        blRunning=False 
    End If 
    On Error Goto 0 
     
    Set olMail = olApp.CreateItem(olMailItem) 
    With olMail 
         'Specify the email subject
        .Subject = "My email with attachment" 
         'Specify who it should be sent to
         'Repeat this line to add further recipients
        .Recipients.Add "name@host.com" 
         'specify the file to attach
         'repeat this line to add further attachments
        .Attachments.Add "c:\test.txt" 
         'specify the text to appear in the email
        .Body = "Here is an email" 
         'Choose which of the following 2 lines to have commented out
        .Display 'This will display the message for you to check and send yourself
         '.Send ' This will send the message straight away
    End With 
     
    If Not blRunning Then olApp.Quit 
     
    Set olApp=Nothing 
    Set olMail=Nothing 
     
End Sub

How to use:

  1. Open you application of choice (e.g. Excel or Word)
  2. Hit Alt + F11 to open the Visual Basic Editor.
  3. Click Insert-Module from the menu.
  4. Goto Tools-References and place a checkbox next to Microsoft Outlook xx.0 Object Library
  5. Copy the above code
  6. Paste the code into the window that appears at right of the Visual Basic Editor
  7. Edit the code to specify the path of the file to attach, subject, recipients etc.
  8. Close the VBE (Alt + Q or press the x in the top right corner).
 

Test the code:

  1. Run the macro via Tools-Macros-Macros, selecting it and hitting run or call the procedure through code
 

Sample File:

sample.zip 6KB 

Approved by MOS MASTER


This entry has been viewed 190 times.

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