Solved

Very Basic Mail Merge Required

Posted on 2003-12-07
6
522 Views
Last Modified: 2006-11-17
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
0
Comment
Question by:BillPowell
  • 3
  • 3
6 Comments
 
LVL 23

Expert Comment

by:heer2351
ID: 9893253
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

0
 
LVL 11

Author Comment

by:BillPowell
ID: 9893458
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
0
 
LVL 23

Expert Comment

by:heer2351
ID: 9893470
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
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 11

Author Comment

by:BillPowell
ID: 9893786
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,
0
 
LVL 23

Accepted Solution

by:
heer2351 earned 500 total points
ID: 9893893
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 :)
0
 
LVL 11

Author Comment

by:BillPowell
ID: 9894093
Hans,

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

Best Regards,

Bill
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now