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

MORE INFORMATION

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 EmailAddress field.
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.