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/BusCertificate.doc (Once again Id like to use whatever path the current db is in to make this portable for development purposes.
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_Click:

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_Click:
MsgBox "Error Number~ " & Err.Number & vbCrLf & Err.DESCRIPTION
Resume Exit_cmdReceiptPreview_Click:

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
LVL 11
BillPowellAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

heer2351Commented:
Hi 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 & ".doc") <> ""
    i = i + 1
  Wend
  docName = CurrentDBDir & "Documents\BusCertificate_" & i & ".doc"
  FileCopy CurrentDBDir & "Documents\BusCertificate.doc", docName

  Set objWord = CreateObject("Word.Application")  
  Set objDoc = objWord.Documents.Open(docName)
 
  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

BillPowellAuthor Commented:
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
heer2351Commented:
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
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

BillPowellAuthor Commented:
Heres the onclick event:

Private Sub cmdCertificatePreview_Click()
On Error GoTo Err_cmdCertificatePreview_Click:
Dim strSQL As String

strSQL = "Select * from qryCompileBusLic where BUS_ID =" & Forms!frmBusLicAdmin!BUS_ID

Call MakeqryBusLicMailMerge(strSQL)
Call doMailMerge

Exit_cmdReceiptPreview_Click:
On Error Resume Next
Exit Sub

Err_cmdCertificatePreview_Click:
MsgBox "Error Number~ " & Err.Number & vbCrLf & Err.DESCRIPTION
Resume Exit_cmdReceiptPreview_Click:
End Sub

Heres the code that makes the query:

Public Sub MakeqryBusLicMailMerge(strSQL As String)
'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("qryBusLicMailMerge", strSQL)

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.Application")
 Set objDoc = objWord.Documents.Open(docName)
 
 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,
heer2351Commented:
Bill,

I just checked the reason for this behaviour is that the default for word when doing a mail merge is to create a new document. So copying the file is not required, also the original file can be closed when merge has been executed. Please try this:

Sub doMailMerge()
 Dim objWord As Object
 Dim objDoc As Object

 Set objWord = CreateObject("Word.Application")
 objWord.Visible = True

 Set objDoc = objWord.Documents.Open(CurrentDBDir & "Documents\Business Certificate.doc")
 
 'This will create a new document
 objDoc.MailMerge.Execute

  'Close the original document
  objDoc.Close
  Set objDoc = Nothing
End Sub


Even simpler than I thought intially :)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BillPowellAuthor Commented:
Hans,

That worked exactly how I needed it.  Thanks again for your expertise.

Best Regards,

Bill
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.