Access Mail Merge without OpenDataSource Curr Record only

[cablass]

I was having difficulty with OpenDataSource when trying to mail merge from Access to Word. Also I only wanted to merge the current record. I got round it thanks to a post by Benoit Mann on Blue Claw Database Design. Go to

http://www.blueclaw-db.com/mail-merge-bookmarks.htm

to view the original.

So here is the general procedure:

First create a Word document and place bookmarks to link to the data fields in your access database. Note the file path of your Word document.

Create a command button in Access on a form containing the record you wish to merge.

The following is how I have interpreted the original code to suit my own requirements. I hope that by viewing both mine and the original you will find a solution to your own needs. Code follows:

Private Sub CmdSendLetter_Click()

Dim MyWord As Word.Application
Dim PathDocu As String

'This differs from the original in that I only required that the txtClientID field contain a number greater than 0
If Me.txtClientID <> 0 Then
Set MyWord = New Word.Application

'Note the actual document name is not included below
'I'm sure you could include it and cut out the "& "Merge Letter.doc" bit later
'I have retained it as it may provide functionality later
'Also - IMPORTANT - You will have to change the file path to correspond with your document
PathDocu = "C:\Documents and Settings\Yvonne\My Documents\"
End If
With MyWord
.Documents.Open (PathDocu & "Merge Letter.doc")
.ActiveDocument.Bookmarks("CaseWorker").Range.Text = Me.txtCaseWorker
.ActiveDocument.Bookmarks("ClientID").Range.Text = Me.txtClientID
.ActiveDocument.Bookmarks("Date").Range.Text = Format(Date, "d MMMM yyyy")
If Me.txtTitle <> "" Then
MyWord.ActiveDocument.Bookmarks("Title").Range.Text = Me.txtTitle
MyWord.ActiveDocument.Bookmarks("GreetingTitle").Range.Text = Me.txtTitle
End If
If Me.txtFirstName <> "" Then
MyWord.ActiveDocument.Bookmarks("FirstName").Range.Text = Me.txtFirstName
End If
If Me.txtLastName <> "" Then
MyWord.ActiveDocument.Bookmarks("LastName").Range.Text = Me.txtLastName
MyWord.ActiveDocument.Bookmarks("GreetingName").Range.Text = Me.txtLastName
End If
If Me.txtAddressLine1 <> "" Then
MyWord.ActiveDocument.Bookmarks("AddressLine1").Range.Text = Me.txtAddressLine1
End If
If Me.txtTown <> "" Then
MyWord.ActiveDocument.Bookmarks("Town").Range.Text = Me.txtTown
End If
If Me.txtPostcode <> "" Then
MyWord.ActiveDocument.Bookmarks("Postcode").Range.Text = Me.txtPostcode
End If
If Me.txtCaseWorker = "PW" Then
MyWord.ActiveDocument.Bookmarks
("Signatory").Range.Text = "Paul Wills"
Else: If Me.txtCaseWorker = "JH" Then MyWord.ActiveDocument.Bookmarks("Signatory").Range.Text = "Jo Hinnigan"
End If
MyWord.ActiveDocument.Bookmarks("JobTitle").Range.Text = "Money Advice Worker"
.Visible = True
.ActiveDocument.SaveAs (txtLastName & " " & txtFirstName & " " & Format(Date, "d MM"))
End With

DoEvents
Set MyWord = Nothing

On Error GoTo Err_Enveloppe_Click

Exit_Enveloppe_Click:
Exit Sub

Err_Enveloppe_Click:
MsgBox Err.Description
Resume Exit_Enveloppe_Click

'With many thanks to Benoit Mann for his contribution at

http://www.blueclaw-db.com

NOTE: THE FOLLOWING IS NOT PART OF THE ROUTINE, REMOVE FROM ANY CUT & PASTE!

This tip is contributed by a novice for users of similar status. I would greatly appreciate input from more experienced users, particularly to point out possible pitfalls or correct any potential flaws in my procedure. Also, any suggestions for improvement would be greatly appreciated; I am sure my solution is far from being the most elegant.

Other than that, it works, and I hope someone else finds the routine as helpful as I did.

Kind Regards

Yvonne