BillPowell
asked on
Very Basic Mail Merge Required
In a previous question I was given an excellent and very robust example of automating word mail merge. It accounted for many scenarios and was very comprehensive. However, Im a newbie to COM automation and I think I need to start smaller so I can understand the process better and work my way up to bigger things. If anyone can provide me an example or sample of how to perform a Very Simple mail merge given the following information:
The data source for the word document is a query called qryBusLicMailMerge (it is created dynamically prior to the merge)
The data source is in the Current database (I would like to use Currentdb if possible instead of specifying C:Desktop....etc)
The word document is located in Currentdb path/Documents/BusCertific
The word document is already set up with merge fields and data source(not sure if data source needs to be set each time during automation)
I would like it to use the word document specified above but merge it to a new word document and close the original right away so your left with a document that they can save or delete and has no effect on the original.
The user would be responsible for doing File->Print, but I will probably make an alternative later that sends directly to the printer while hiding word.
The procedure will go like this:
Private Sub cmdPreview_Click()
On Error GoTo Err_cmdReceiptPreview_Clic
Call DeleteQuery()
Call MakeQuery()
Call <<Merge Function or Procedure that I dont yet have>>
Exit_cmdPreview_Click:
On Error Resume Next
Exit Sub
Err_cmdReceiptPreview_Clic
MsgBox "Error Number~ " & Err.Number & vbCrLf & Err.DESCRIPTION
Resume Exit_cmdReceiptPreview_Cli
End Sub
I am using Office 2000
If anyone can walk through this with me I would be indebted. Let me know if you need more info
Thanks,
Bill
The data source for the word document is a query called qryBusLicMailMerge (it is created dynamically prior to the merge)
The data source is in the Current database (I would like to use Currentdb if possible instead of specifying C:Desktop....etc)
The word document is located in Currentdb path/Documents/BusCertific
The word document is already set up with merge fields and data source(not sure if data source needs to be set each time during automation)
I would like it to use the word document specified above but merge it to a new word document and close the original right away so your left with a document that they can save or delete and has no effect on the original.
The user would be responsible for doing File->Print, but I will probably make an alternative later that sends directly to the printer while hiding word.
The procedure will go like this:
Private Sub cmdPreview_Click()
On Error GoTo Err_cmdReceiptPreview_Clic
Call DeleteQuery()
Call MakeQuery()
Call <<Merge Function or Procedure that I dont yet have>>
Exit_cmdPreview_Click:
On Error Resume Next
Exit Sub
Err_cmdReceiptPreview_Clic
MsgBox "Error Number~ " & Err.Number & vbCrLf & Err.DESCRIPTION
Resume Exit_cmdReceiptPreview_Cli
End Sub
I am using Office 2000
If anyone can walk through this with me I would be indebted. Let me know if you need more info
Thanks,
Bill
ASKER
Hans
That worked perfectly. Thats just the type of simplicity I needed given my time constraints. Now, just a small cleanup issue.
When the code is executed, two instances of word are opened.
One is called Form Letters1.doc and has all the proper merged info. When I close that document, I see another called Business Certificate_1.doc which has no merged data and looks like the document was opened but no merge was executed. Was it supposed to happen like this or do we need to get rid of that other instance some how.
Just so you know, Im doing my developement on Access XP, but this will be deployed on boxes that have Access 2000, so maybe this wont occur on 2000, Im not sure.
Thanks
Bill
That worked perfectly. Thats just the type of simplicity I needed given my time constraints. Now, just a small cleanup issue.
When the code is executed, two instances of word are opened.
One is called Form Letters1.doc and has all the proper merged info. When I close that document, I see another called Business Certificate_1.doc which has no merged data and looks like the document was opened but no merge was executed. Was it supposed to happen like this or do we need to get rid of that other instance some how.
Just so you know, Im doing my developement on Access XP, but this will be deployed on boxes that have Access 2000, so maybe this wont occur on 2000, Im not sure.
Thanks
Bill
Hi Bill,
No that is not supposed to happen. Could you please post the code you are using right now, so I can have a look.
Hans
No that is not supposed to happen. Could you please post the code you are using right now, so I can have a look.
Hans
ASKER
Heres the onclick event:
Private Sub cmdCertificatePreview_Clic
On Error GoTo Err_cmdCertificatePreview_
Dim strSQL As String
strSQL = "Select * from qryCompileBusLic where BUS_ID =" & Forms!frmBusLicAdmin!BUS_I
Call MakeqryBusLicMailMerge(str
Call doMailMerge
Exit_cmdReceiptPreview_Cli
On Error Resume Next
Exit Sub
Err_cmdCertificatePreview_
MsgBox "Error Number~ " & Err.Number & vbCrLf & Err.DESCRIPTION
Resume Exit_cmdReceiptPreview_Cli
End Sub
Heres the code that makes the query:
Public Sub MakeqryBusLicMailMerge(str
'Makes the query for mail merge
'DAO had to be used to make the query because
'ADO queries do not show up in the database window
Dim strQuery As String
Dim db As Database
Dim qdf As QueryDef
Call DeleteQuery(strQuery)
Set db = CurrentDb()
Set qdf = db.CreateQueryDef("qryBusL
End Sub
Heres the code that deletes the query:
Public Sub DeleteQuery(strQueryName As String)
'Deletes the virtual query that is used to get the data to prepare for it to be recreated
On Error Resume Next
DoCmd.SetWarnings False
DoCmd.DeleteObject acQuery, "qryBusLicMailMerge"
DoCmd.SetWarnings True
End Sub
And heres the code you gave me modified for the full names of the objects:
Sub doMailMerge()
Dim objWord As Object
Dim objDoc As Object
Dim i As Integer
Dim docName As String
'Copy the file to a new unique name
i = 1
While Dir(CurrentDBDir & "Documents\Business Certificate_" & i & ".doc") <> ""
i = i + 1
Wend
docName = CurrentDBDir & "Documents\Business Certificate_" & i & ".doc"
FileCopy CurrentDBDir & "Documents\Business Certificate.doc", docName
Set objWord = CreateObject("Word.Applica
Set objDoc = objWord.Documents.Open(doc
objWord.Visible = True
objDoc.MailMerge.Execute
End Sub
Function CurrentDBDir() As String
Dim strDBPath As String
Dim strDBFile As String
strDBPath = CurrentDb.Name
strDBFile = Dir(strDBPath)
CurrentDBDir = Left$(strDBPath, Len(strDBPath) - Len(strDBFile))
End Function
Let me know if you need anything else.
Thanks,
Private Sub cmdCertificatePreview_Clic
On Error GoTo Err_cmdCertificatePreview_
Dim strSQL As String
strSQL = "Select * from qryCompileBusLic where BUS_ID =" & Forms!frmBusLicAdmin!BUS_I
Call MakeqryBusLicMailMerge(str
Call doMailMerge
Exit_cmdReceiptPreview_Cli
On Error Resume Next
Exit Sub
Err_cmdCertificatePreview_
MsgBox "Error Number~ " & Err.Number & vbCrLf & Err.DESCRIPTION
Resume Exit_cmdReceiptPreview_Cli
End Sub
Heres the code that makes the query:
Public Sub MakeqryBusLicMailMerge(str
'Makes the query for mail merge
'DAO had to be used to make the query because
'ADO queries do not show up in the database window
Dim strQuery As String
Dim db As Database
Dim qdf As QueryDef
Call DeleteQuery(strQuery)
Set db = CurrentDb()
Set qdf = db.CreateQueryDef("qryBusL
End Sub
Heres the code that deletes the query:
Public Sub DeleteQuery(strQueryName As String)
'Deletes the virtual query that is used to get the data to prepare for it to be recreated
On Error Resume Next
DoCmd.SetWarnings False
DoCmd.DeleteObject acQuery, "qryBusLicMailMerge"
DoCmd.SetWarnings True
End Sub
And heres the code you gave me modified for the full names of the objects:
Sub doMailMerge()
Dim objWord As Object
Dim objDoc As Object
Dim i As Integer
Dim docName As String
'Copy the file to a new unique name
i = 1
While Dir(CurrentDBDir & "Documents\Business Certificate_" & i & ".doc") <> ""
i = i + 1
Wend
docName = CurrentDBDir & "Documents\Business Certificate_" & i & ".doc"
FileCopy CurrentDBDir & "Documents\Business Certificate.doc", docName
Set objWord = CreateObject("Word.Applica
Set objDoc = objWord.Documents.Open(doc
objWord.Visible = True
objDoc.MailMerge.Execute
End Sub
Function CurrentDBDir() As String
Dim strDBPath As String
Dim strDBFile As String
strDBPath = CurrentDb.Name
strDBFile = Dir(strDBPath)
CurrentDBDir = Left$(strDBPath, Len(strDBPath) - Len(strDBFile))
End Function
Let me know if you need anything else.
Thanks,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hans,
That worked exactly how I needed it. Thanks again for your expertise.
Best Regards,
Bill
That worked exactly how I needed it. Thanks again for your expertise.
Best Regards,
Bill
In my opinion the easiest approach given the setup you sketched is that you simply copy the file and then open it in word, since you have already setup the data source all that is left to do is execute the merge. Example:
BTW: I use late binding so you do not need a reference to word.
Sub doMailMerge()
Dim objWord As Object
Dim objDoc As Object
Dim i as Integer
Dim docName as string
'Copy the file to a new unique name
i = 1
While Dir(CurrentDBDir & "Documents\BusCertificate_
i = i + 1
Wend
docName = CurrentDBDir & "Documents\BusCertificate_
FileCopy CurrentDBDir & "Documents\BusCertificate.
Set objWord = CreateObject("Word.Applica
Set objDoc = objWord.Documents.Open(doc
objWord.Visible = True
objDoc.MailMerge.Execute
End Sub
Function CurrentDBDir() As String
Dim strDBPath As String
Dim strDBFile As String
strDBPath = CurrentDb.Name
strDBFile = Dir(strDBPath)
CurrentDBDir = Left$(strDBPath, Len(strDBPath) - Len(strDBFile))
End Function