This article describes how to use Automation to send a
Microsoft Outlook e-mail message to multiple recipients whose addresses are
listed in a Microsoft Access table. This article also uses Access
form controls to add the message body, to fill in the Cc field, and to fill in the Subject
field. Additionally, this article describes a programmatic method that you can use to include
e-mail attachments and to resolve e-mail addresses against the Outlook Address
To create an Outlook session from Access and to use
a recordset to send e-mail messages to multiple recipients, follow these steps:
- Create a text file that you can use as an attachment. Save the text file as "TestAttachment.txt" in the root of drive C. For example, you can save the text file as
- Create a new database that is named Db1.mdb.
- Create a new table with the following information in Db1.mdb:
Field Name: EmailAddress
Data Type: text
- Type several valid e-mail addresses in the EmailAddress
- Create a form with the following information:
Label Caption: CC Address
Label Caption: Subject
Label Caption: Message Body
- Open the form in Form view, and then type sample data in
the appropriate boxes. Press ENTER after each entry, and then leave the form
open when you are finished.
- Create a new module. Add the following code sample to the new module.
Option Compare Database
Sub SendMessages(Optional AttachmentPath)
Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailAddress]
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo
' Add the Cc recipients to the e-mail message.
If (IsNull(Forms!frmMail!CCAddress)) Then
Set objOutlookRecip = .Recipients.Add(Forms!frmMail!CCAddress)
objOutlookRecip.Type = olCC
' Set the Subject, the Body, and the Importance of the e-mail message.
.Subject = Forms!frmMail!Subject
.Body = Forms!frmMail!MainText
.Importance = olImportanceHigh 'High importance
'Add the attachment to the e-mail message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
If Not objOutlookRecip.Resolve Then
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
- In the Visual Basic Editor, click References on the Tools menu.
- In the Available References list, click Microsoft Outlook 8.0 Object
Library, and then click OK.
Note You have to select the correct Microsoft Outlook Object
Library. The Microsoft Outlook Object
Library that you select depends on the version of Outlook that is installed
on your computer. For example, if you have Office Outlook 2003 installed on your computer, click Microsoft Outlook 11.0 Object Library.
- Press CTRL+G to open the Debug window.
- To send the e-mail message with an attachment, type the
following command in the Debug window:
SendMessages "C:\TestAttachment.txt"Press ENTER.
Alternatively, to send the e-mail message without an attachment, omit the
argument when calling the procedure. Type the
following command in the Debug window:
For additional information about how to use Automation to control
Outlook, click the following article numbers to view the articles in the Microsoft Knowledge Base:
Using Automation to send a Microsoft Outlook message
Using Automation to add appointments to Microsoft Outlook
How to create a new contact item in Outlook with Automation
Article ID: 318881 - Last Review: October 11, 2004 - Revision: 3.4
- Microsoft Access 97 Standard Edition
- Microsoft Access 2000 Standard Edition
- Microsoft Access 2002 Standard Edition
- Microsoft Office Access 2003