Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. How to use a recordset to send Outlook e-mail to multiple recipients in Microsoft Access

How to use a recordset to send Outlook e-mail to multiple recipients in Microsoft Access

Advanced: Requires expert coding, interoperability, and multiuser skills.

↑ Back to the top

Summary

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 Book.

↑ Back to the top

More Information

To create an Outlook session from Access and to use a recordset to send e-mail messages to multiple recipients, follow these steps:

  1. 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 C:\TestAttachment.txt.
  2. Create a new database that is named Db1.mdb.
  3. Create a new table with the following information in Db1.mdb:

    Table: tblMailingList
    ----------------------
    Field Name: EmailAddress
    Data Type:  text
  4. Type several valid e-mail addresses in the EmailAddressfield.
  5. Create a form with the following information:

    Form: frmMail
       ----------------------
       RecordSource: tblMailingList

       Text box
       ---------------------------
       Name: CCAddress
       ControlSource: Unbound
       Label Caption: CC Address
       
       Text box
       ---------------------------
       Name: Subject
       ControlSource: Unbound
       Label Caption: Subject

       Text box
       ---------------------------
       Name: MainText
       ControlSource: Unbound
       Label Caption: Message Body
  6. 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.
  7. Create a new module. Add the following code sample to the new module.
    Option Compare Database
    Option Explicit

    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")
      MyRS.MoveFirst

      ' 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]

         With objOutlookMsg
            ' 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
            Else
               Set objOutlookRecip = .Recipients.Add(Forms!frmMail!CCAddress)
               objOutlookRecip.Type = olCC
            End If
       
            ' 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)
            End If

            ' Resolve the name of each Recipient.
            For Each objOutlookRecip In .Recipients
               objOutlookRecip.Resolve
               If Not objOutlookRecip.Resolve Then
                 objOutlookMsg.Display
               End If
            Next
            .Send
          End With
          MyRS.MoveNext
       Loop
       Set objOutlookMsg = Nothing
       Set objOutlook = Nothing
    End Sub
  8. In the Visual Basic Editor, click References on the Tools menu.
  9. 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.
  10. Press CTRL+G to open the Debug window.
  11. 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:
    SendMessages
    Press ENTER.

↑ Back to the top

References

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:

161088 Using Automation to send a Microsoft Outlook message

160502 Using Automation to add appointments to Microsoft Outlook

161012 How to create a new contact item in Outlook with Automation

↑ Back to the top

Keywords: kbhowto, kbsoconvert, ocsentirenet, kbbillprodsweep, kb, kbarchive

↑ Back to the top

Article Info
Article ID : 318881
Revision : 1
Created on : 1/7/2017
Published on : 10/30/2008
Exists online : False
Views : 201