Solved

Very Basic Mail Merge Required

Posted on 2003-12-07
6
528 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

740 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