Mail Merge Problem

[s6mc]

Hi, here is my problem.

1. When opening my mail merged word document, an extra copy of my access database is opened. How can i stop this?

2. I want to create a button to open the mail merged document on a form. I have tried putting the path of the document in the hyperlink property of the button but this isn't working well. What code for the "on click" would i need to open word and open the document?

Any help would be much appreciated.

Thanks
Andrew

 

[dhaveedh]

Call this function with the relevant parameters from the onclick event of your button.

I hope you have set up the relevant Mail Merge document indicating the data source.

Good Luck.


Function fn_MergeFromTextFile(sSourceFilePath As String, strTemplateName As String, strLetterPath As String, Optional bdisplay As Boolean = False, Optional bPrint As Boolean = False) As Boolean
On Error GoTo errHand

Dim lPID As Long
Dim sFileToShell As String

'Declare a word document and fill it with the mail merge template
Dim objWord As Word.Document

Set objWord = GetObject(strLetterPath & strTemplateName, "Word.Document")

If bdisplay = True Then
'Make Word visible.
objWord.Application.Visible = True
End If
'Set up the mailmerge
objWord.MailMerge.OpenDataSource _
Name:=sSourceFilePath
'Propmt to merge to new document the execute the mail merge
objWord.MailMerge.Destination = wdSendToNewDocument
objWord.MailMerge.SuppressBlankLines = True
objWord.MailMerge.Execute

If bPrint = True Then
objWord.Application.Documents("Form Letters1").PrintOut
DoEvents
sb_Wait (20) 'wait for n seconds to allow print command to be executed
MsgBox "Printing " & Left(strTemplateName, 7) 'Click OK when the letters have printed."
sb_Wait (10) 'wait for further 10 seconds to allow print command to be executed
End If

If bdisplay = False Then
'Close the template
objWord.Application.quit wdDoNotSaveChanges
'Let OS catch up
DoEvents
Else
objWord.Application.Documents(strTemplateName).Close wdDoNotSaveChanges
MsgBox "Now please make any amendments to your letter, save it with a suitable filename and then close Word."
End If

'Kill sSourceFilePath

fn_MergeFromTextFile = True
Exit Function

errHand:

If err.number <> 0 Then
MsgBox err.number & vbTab & err.Description

LogError err.number, err.Description, "modMerge", "CreateLetterFile"
End If

End Function



'Forces a wait of iSeconds

Sub sb_Wait(iSeconds As Integer)
On Error GoTo errHand
Dim iStartTime As Single
Dim iEndTime As Single

iStartTime = Timer
iEndTime = Timer

Do Until iStartTime + iSeconds < iEndTime
iEndTime = Timer
Loop

Exit Sub

errHand:

If err.number <> 0 Then
LogError err.number, err.Description, "ModTimer", "sb_Wait"
End If

End Sub


KISS - Keep It Simple Sugar!

 

[s6mc]

Thanks, got it working.

This is the code i used to open a mail merge document with a command button:

Private Sub confirmletter_Click()
Dim objWord As Word.Document
Dim docname As String
docname = "C:\Documents and Settings\Andrew McLeod\My Documents\Select\CustomerConfirmationLetters.doc"
Set objWord = GetObject(docname)
objWord.Application.Visible = True

End Sub