Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Microsoft: Access Modules (VBA Coding) FAQ

Word Template Emailer

How do I use Word as an email template and merge to Outlook by 1DMF
Posted: 12 Jun 08

I had a requirement to allow staff to use word to generate rich content emails based on a corporate template.

Then use the word doc to merge with our membership DB and send to outlook for mailing.

The core code for achieving this is as follows


Private Sub Send_Email_Click()

Dim sFile As String
Dim i As Integer
Dim itm As Object
Dim ID As String
Dim wd As Word.Application
Dim Doc As Word.Document
Dim objApp As Outlook.Application
Dim l_Msg As MailItem
Dim oReceipt As Outlook.Recipient

' check buletin selected for sending

If Nz(Me!sendEBS, "") = "" Then
    MsgBox "Please select a bulletin to send"
    Exit Sub
End If

'get email addresses record set

    Select Case Me!sTo
        Case Is = "Members"
            Set rs = CurrentDb.OpenRecordset("SELECT [EmailName] FROM [Contacts] WHERE [Contact_Type] = 'Member'", dbOpenSnapshot, dbSeeChanges)
        Case Is = "Prospects"
            Set rs = CurrentDb.OpenRecordset("SELECT [EmailName] FROM [Contacts] WHERE [Contact_Type] = 'Prospect'", dbOpenSnapshot, dbSeeChanges)
        Case Else
            MsgBox "Please select a recipient"
            Exit Sub
    End Select
'Are You Sure
If vbNo = MsgBox("Send [ " & Me!sendEBS & " ] To [ " & Me!sTo & " ], Are you sure?", vbYesNo) Then
   Exit Sub
End If

Msg = "Enter the subject to be used for each email message."    ' Set prompt.

tit = " Email Subject Input"    ' Set title.

' Display message, title

sSubject = InputBox(Msg, tit)
If Nz(sSubject, "") = "" Then
    MsgBox "You must supply an email subject"
    Set rs = Nothing
    Exit Sub
End If
sFile = EBS_DIR & Me!sendEBS

Set wd = CreateObject("Word.Application")

Set Doc = wd.Documents.Open(FileName:=sFile, ReadOnly:=True)
    Set itm = Doc.MailEnvelope.Item
    With itm
        .To = DEFAULT_EMAIL
        .Subject = sSubject
        ID = .EntryID
    End With

'clear references
Doc.Close wdDoNotSaveChanges
wd.Quit False

Set itm = Nothing
Set Doc = Nothing
Set wd = Nothing

' start email and get saved item
    Set objApp = CreateObject("Outlook.Application")

    Set l_Msg = objApp.GetNamespace("MAPI").GetItemFromID(ID)
    With l_Msg
        'Loop over recipients
        Do While Not rs.EOF
            Set oReceipt = .Recipients.Add(rs.Fields("EmailName"))
            oReceipt.Type = olBCC
        Set rs = Nothing
        ' add any attachments
        If (Me.editEBS.ListCount > 0) Then
            i = 0

           Do While i < Me.attEBS.ListCount
                .Attachments.Add (Me.attEBS.ItemData(i))
                i = i + 1

        End If
    End With

    'clear references
    Set oReceipt = Nothing
    Set l_Msg = Nothing
    Set objApp = Nothing
End Sub
The key to this is using the MailEnvelope on the Word object [which I have highlighted] to turn the document into an email message and save into drafts.

You can then use the MAPI namespace to get the saved draft email and manipulate further and display for sending.

I have created an  MS Access 2003 application download so you can see how I put it all together.

I am unsure which versions of Office this works in as we only have MS-Office 2003.

You will need to download the zip file and place the EBS.doc on the C:\ drive, there is also a C:\EBS\ folder with a test file in you will need as well.

You will see when viewing the code there are a few 'constants' which you can play with to move the file locations.

Also I have included some dummy tables, but you can of course change this to connect to your SQL or other data source for use by the recordset in the code.

Hope you find this as useful a I have.


NOTE: I have not supplied any error checking, you will need to handle your own errors winky smile


Back to Microsoft: Access Modules (VBA Coding) FAQ Index
Back to Microsoft: Access Modules (VBA Coding) Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close