Producing letters from Access using bookmarks.

  • Thread starter Thread starter Stephen Glynn
  • Start date Start date
S

Stephen Glynn

Using the article at http://support.microsoft.com/Default.aspx?kbid=210271
I've set up Access to send the current record from an Access form to
Word to produce a form letter using bookmarks rather than Merge Fields.

I like this way of doing it because it's a lot faster than using Merge,
but there's one drawback. If there's no data for the Merge Field
Address2, Word takes it out in the merge so there's no blank line. It
doesn't, however, do that if there's nothing to put in the bookmark
Address2. Instead, I just have a blank line between Address1 and City.
Is there any way in Word to deal with this problem, or should I
attack it from the Access end using code?

Steve
 
Instead of creating the bookmarks for the words "First" "Last" "Address"
"City" "Region" and "PostalCode", create a single bookmark with the name
"AddressDetail". Then use the following modification of the code given in
the KB article:

Private Sub MergeButton_Click()
On Error GoTo MergeButton_Err

Dim objWord As Word.Application
Dim AddressDetail As String

AddressDetail = (CStr(Forms!Employees!FirstName)) & " " &
(CStr(Forms!Employees!LastName))
AddressDetail = AddressDetail & vbCr & (CStr(Forms!Employees!Address1))
If Trim((CStr(Forms!Employees!Address2))) <> "" Then
AddressDetail = AddressDetail & vbCr &
(CStr(Forms!Employees!Address2))
End If
AddressDetail = AddressDetail & vbCr & (CStr(Forms!Employees!City))
AddressDetail = AddressDetail & ", " & (CStr(Forms!Employees!Region))
AddressDetail = AddressDetail & ", " &
(CStr(Forms!Employees!PostalCode))



'Copy the Photo control on the Employees form.
DoCmd.GoToControl "Photo"
DoCmd.RunCommand acCmdCopy

'Start Microsoft Word 97.
Set objWord = CreateObject("Word.Application")

With objWord
'Make the application visible.
.Visible = True

'Open the document.
.Documents.Open ("C:\MyMerge.doc")

'Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("AddressDetail").Select
.Selection.Text = AddressDetail
.ActiveDocument.Bookmarks("Greeting").Select
.Selection.Text = (CStr(Forms!Employees!FirstName))

'Paste the photo.
.ActiveDocument.Bookmarks("Photo").Select
.Selection.Paste
End With

'Print the document in the foreground so Microsoft Word will not close
'until the document finishes printing.
objWord.ActiveDocument.PrintOut Background:=False

'Close the document without saving changes.
objWord.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges

'Quit Microsoft Word and release the object variable.
objWord.Quit
Set objWord = Nothing
Exit Sub

MergeButton_Err:
'If a field on the form is empty, remove the bookmark text, and
'continue.
If Err.Number = 94 Then
objWord.Selection.Text = ""
Resume Next

'If the Photo field is empty.
ElseIf Err.Number = 2046 Then
MsgBox "Please add a photo to this record and try again."
Else
MsgBox Err.Number & vbCr & Err.Description
End If

Exit Sub
End Sub


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP
 
Back
Top