Automating email from access as both embbeded email AND attachment

Okay, I've been pouring over the articles from Experts Exchange in regards to automating an Email Merge from Microsoft Access. However all of the examples given show you how to send an email EITHER as an attachment. or as an embedded email (in other words take a Word Document and merge it into the body of an email), but none of these methods show you how to do BOTH (and sending plain text in the body of the email in not an option, I need it to be another Word Document).

This is exactly what I want to do.

1--I have two Word Documents that have the merge fields already set up in them and both use the same datasource.
2--Within Access I want the user to open a form, select their datasource from a combo box, type a subject into a text box, and then hit a button.
3--The code will then merge the data into both Word documents and take one of the Word documents and make it the body of an email message.
4--It will also attach the second Word document to the SAME email and send it as an attachment.

I've been able to do the first 3 steps with the code below, but I'm  not sure how to do step number 4 (somehow I don't think creating an "objWord2" would attach the second Word document to the same email message). Any help is much appreicated. Thanks!

Sub MergeEmail()
         Dim objWord As Object
         Dim strEmbeddedLetterPath As String 'Path to letter that will be body of email message
         Dim strAttachedLetterPath As String 'Path to letter that will be attachment
         Dim strDBPath As String 'Path to database
         Dim strSubject As String 'Subject of email
         Dim strDataSource As String 'Name of query selected
         
         strDataSource = Me.cmbDataSource 'Data source chosen from combo box
         strSubject = Me.txtSubject
         strEmbeddedLetterPath = "g:\Documents and Settings\jbeverid\Desktop\UGS Contacts\TestLetterEmbedded.doc"
         strAttachedLetterPath = "g:\Documents and Settings\jbeverid\Desktop\UGS Contacts\TestLetterAttachment.doc"
         strDBPath = "g:\Documents and Settings\jbeverid\Desktop\UGS Contacts\UGS Contacts Database.mdb"
         
         Set objWord = GetObject(strEmbeddedLetterPath, "Word.Document")
       
       
         ' Make Word visible.
         objWord.Application.Visible = True
         ' Set the mail merge data source
         objWord.MailMerge.OpenDataSource Name:=strDBPath, _
            LinkToSource:=True, AddToRecentFiles:=False, Connection:="QUERY " & strDataSource
         
         ' Execute the mail merge.
         objWord.MailMerge.MailSubject = strSubject      
         objWord.MailMerge.MailFormat = wdMailFormatHTML
         objWord.MailMerge.MailAddressFieldName = "Email"
         objWord.MailMerge.Destination = wdSendToEmail
         objWord.MailMerge.Execute
         
       
End Sub



Start Free Trial