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 187 times.

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