asked on

Mail Merge frpm Access 2003

Please help
Can anyone please tell me what are where I'm going wrong. I've been working on this for several days and just going around in ever decreasing circles and getting nowhere.
I'm trying to automate a procedure to do a mail merge from Access 2003.
I have created the Word 2003 document together with its MergedFields and linked it to a query in the Access 2003 database. The Mail Merge code below is taken from similiar questions/responses I found in EE's knowledge base which I've adapted for my own requirements eg inpLetterName is the full path name and document (Completion Letter.doc) name while inpDatabase is the full path name and the frontend database name (.mdb) where the query is found.

When the logic enters this code it works to a degree - in that it opens the Completion Letter.doc but then opens a Select Table form and asks for a table/query to be selected. After selectiing the appropriate query (qry1200_) it redisplays the Completion Letter.doc correctly, that is, the fields from the query have been merged correctly into the document.

My problem is this and this is where I need help as I seem to be assuming something that isn't happening or I don't understand the merge logic I've copied (or both)

1) Why does it request you select a table/query as the code indicates what query to use and how can I get this to perform automatically (in other words remove user intervention)
2) How do I get the merged Completion Letter.doc back into Access so that the user can
a) Decide whether to Print / Preview / Cancel the document
b) Select s printer or use the default printer setting to print the document
Note: I have already developed the logic to cater for 2a and 2b but it's under the assumption the document (report) is in the Access Frontend Database
Does my thinking need to be reversed/altered - is it possible to do a merge on a database developed report rather than in Word or is it possible to to include command buttons on the Word document (Print/Preview/Cancel) which are excluded on the printed document. If so how can I change the defauilt printer setting - if required - in Word

Function msfDoMailMerge(inpLetterName As String, inpDatabase As String)
    Dim objWord                                  As Object
    Dim WSDoc                                    As Object
    Dim wdFormLetters                       As Boolean
    Dim wdOpenFormatAuto               As Boolean
    Dim wdSendToNewDocument       As Boolean
  '*******************************************************************************************************  
    On Error GoTo Err_DoMailMerge:
    
   Set objWord = CreateObject("Word.Application")
   objWord.Visible = True
   Set WSDoc = objWord.Documents.Open(inpLetterName)
   
   wdFormLetters = 0
   wdOpenFormatAuto = 0
   wdSendToNewDocument = 0
   
   With WSDoc
        .MailMerge.MainDocumentType = wdFormLetters
        .MailMerge.OpenDataSource Name:=inpDatabase, _
         LinkToSource:=True, _
         Connection:="Query qry1200_sel_5PStudyCourseToStudentReportParameters"
        .MailMerge.Destination = wdSendToNewDocument
        .MailMerge.Execute
   End With
         
Exit_DoMailMerge:
 
   If Not WSDoc Is Nothing Then
      WSDoc.Close SaveChanges:=False
      Set WSDoc = Nothing
   End If
   
Exit Function
 
Err_DoMailMerge:
 
If Err.Number <> 0 And Err.Number <> 3029 Then
   MsgBox "Error number = " & Err.Number & vbCrLf & Err.Description
End If
 
Resume Exit_DoMailMerge:
   
End Function

Open in new window

Completion-Letter.doc

Kelvin Sparks

I think you are thrying to run in circles. When you create the mail merge, you have created a Word Document. It is just that. You can use Access to drive Word, but you cannot store a Word document in Access. You can tell ASccess to print, select printers for that (but not using the Access Printers Collection, or use Word printpreview

Kelvin

Kelvin Sparks

The code below will illustrate how to choose between preview and print. In this case the code to change the printer is commented out as this user did not want that change. The string for the printer name is exactly what is shown in the Control Panel printers collection. There are plenty of example of how to use Access 2003 to build a list of these. You'd want to be on the form that you drive the job from so the selection can be made from a combo before they start.

        If sOutput = "preview" Then
            objWord.Visible = True
            objWord.activedocument.SaveAs LetterName(rsWord!SubmitterName, rsWord!SubmitterNo, sLettersLocn)
            objWord.activedocument.PrintPreview
        Else
            'sOrigPrinter = objWord.ActivePrinter
            'objWord.ActivePrinter = sPrinter
            objWord.Application.Options.PrintBackground = True
            objWord.activedocument.PrintOut
            'objWord.ActivePrinter = sOrigPrinter
            ''Save the document
            objWord.activedocument.SaveAs LetterName(rsWord!SubmitterName, rsWord!SubmitterNo, sLettersLocn)
            ''Close the instance of Word
            objWord.Quit
            Set objWord = Nothing
        End If
        rsWord.MoveNext

Open in new window

GrahamSkan

If you don't have to change the table or query, you can omit the OpenDataSource step, because it is already set in the document.

GrahamSkan

Otherwise try:

        .MailMerge.OpenDataSource Name:=inpDatabase, _
         LinkToSource:=True, _
         SQLStatement:="SELECT * FROM `qry1200_sel_5PStudyCourseToStudentReportParameters`"

Open in new window

Adlerm

ASKER

Hi Kevinsparks

Many thanks for you response but I'm not sure where the code you included should go - I'm so confused. Does it replace some of my code or do I tack it on the end or somewhere in the middle of my code .

Hi Graham Skan
Many thanks also for your response - I tried what you suggested but now instead of opening a Select Table form and selecting the relevant query it opens a Data Link Properties form displaying the correct front end database name but asking for a password for user 'admin' If I click cancel I get a wierd message that the database has been placed in a state by user 'admin' that prevents the database from being opened or locked. Also can you confirm that in the code line SQLStatement you sent the query name is wrapped in a single quote
eg SQLStatement:="Select * From 'qry1200_sel_5PStudyCourseToStudentReportParameters'" . I entered the code without the single quotes around the query name so this may have confused the system if it was expecting the single quotes around the query name.

Kelvin Sparks

My code should be added after the End With in your code.

In respect of your other issue, and I haven't used mail merge for a long time (I prefer to push the data from Access rather than get Word to suck it in), I recal having to oipen a second instance of Access for the merge to avoid the "admim" lock. Word is acting like another user, and you have already locked the data source.

Will see if I have some old code that did this.

Adlerm

ASKER

Hi kevinsparks

Many thanks - if you have some old code it would be very much appreciated. I may have to take the push approach rather than the pull option to get over my problem.

GrahamSkan

Re single quotes.

These quotes are reported when using code to view the querystring.

Have you tried omitting the OpenDataSource step?

Kelvin Sparks

Looks like old code is long gone. If I recal correctly, the second instance was only needed if using a secure mdw (i.e. a login).

You may get that admin prompt while debugging as you have the form in design mode which locks db into Exclusive mode. Ensure it runs in Shared mode & try without debugging.

At the end of the day the push to Word technique is much simler and cleaner, Create bookmarks in a template wherever you want Access to insert text and then just push out from a recordset or similar.

Can ygive you the code for that - is also easy to create tables into Word docs this way

Kelvin

Adlerm

ASKER

Hi Kevin

I'm not in design mode when I testing the application. I'm actually running the application as a live application. Unfortunately the application still keep asking me to select a query when I branch out to Word. As soon as I select the query (q1200_) it informs me that opening the database may be harmful but after clicking the OK evey thing works as expected - that is all the merged fields are updated by yhe appropriate query fields. I justt wish it would do the merge automatically and so avoid user intervention.

I'd be more than happy to accept your bookmark code just to look at an alternative approach especially if it gets around the user intervention problem I'm experiencing.

Adlerm

ASKER

Hi Graham

Yes I tried running the code omitting the OpenDataSource step but no go. Get the following error when the OpenDatasource code is omitted
Error Number 4605
The method or poperty is not available because the current mail merge main document needs a data source.

which them promply locks the system and the only way out is via Alt/Ctl/Del and to end the task (kill access)

Kelvin Sparks

OK the code below is a full letter function. It is written for an Access Project (not database so some calls are for the SQL Server here. I'll try to describe the process. It has more functionality than you need here, but may be of use for you at a later stage (each inserting data tables into a letter)

The function is given the template location and name - you could do this within the function and parameters for output type (print/preview) and whether it has a datatable

The first items just setup the connection object for SQL Server (not needed) and defines a recordset - you can use a dao recordset if you want. then itentify the name of their color printer (was needed by this client).

Opens recordset with data - you could open recordset and use your query as the source of the data - then a quick check that data has been found

If data, identify what you'll call the letter & where to store it - make the folder if needed. Now open word, add the template and start populating the bookmarks from recordset values - note it checks if there are null values in the address and deletes those bookmarks so that the addressing of the letter is tidy.

If has a table (bHasLetter parameter) extracts the table data - the template woud have a blank table of 1 row, so it populates that row the adds a new row and populates for each extra row in the table recordset.

Then does the print stuff - final bit is an excel export of the mailmerge data to be a simple record of the letters produced.

This is often used to do runs of several hundred letters that can have tables of hundreds of rows of table data.

Hope this can help you.

Kelvin

Function CreateLetter(sTemplate, sOutput, bHasLetter)
Dim objWord As Object
Dim cnn As ADODB.Connection
Dim cmd As New ADODB.Command
Dim rsWord As ADODB.Recordset
Dim rsTable As ADODB.Recordset
Dim sLettersLocn As String
Dim sLettersLocntmp As String
Dim iFolder As Integer
Dim bFldr As Boolean
Dim stblData As String
Dim i As Long
Dim sPrinter As String
Dim sOrigPrinter As String
Dim iData As Integer
 
On Error GoTo Err_CreateLetter
 
 
Set cnn = CurrentProject.Connection
Set rsWord = New ADODB.Recordset
 
 
''Find the colour printer
sPrinter = DLookup("[LPValue]", "[LetterParameters]", "[LPName] = 'Colour Printer Name'")
 
 
''Get the data
rsWord.Open "dbo.LettersExport", cnn, adOpenForwardOnly
If rsWord.BOF And rsWord.EOF Then
    ''Check for data
    MsgBox "The system has not found any data to be used for letters.", vbOKOnly + vbInformation, "No Letter data"
    Exit Function
Else
    rsWord.MoveFirst
    ''Work out a folder to store letters in
    sLettersLocn = DLookup("[LPValue]", "[LetterParameters]", "[LPName] = 'Letter Location'")
    sLettersLocn = sLettersLocn & "\" & Format(Date, "yyyymmdd")
    If Dir(sLettersLocn, vbDirectory) <> "" Then
        iFolder = 1
        bFldr = False
        Do Until bFldr
            sLettersLocntmp = sLettersLocn & "_" & iFolder
            If Dir(sLettersLocntmp, vbDirectory) <> "" Then
                iFolder = iFolder + 1
            Else
                bFldr = True
            End If
        Loop
        sLettersLocn = sLettersLocntmp
    
    End If
    MkDir sLettersLocn
    Do Until rsWord.EOF
        ''Loop through the dataset & create the letters
        DoCmd.Echo False, "Producing Letter to " & rsWord!SubmitterName
        Set objWord = CreateObject("Word.Application")
        objWord.Documents.Add sTemplate
        objWord.Visible = False
        With objWord.activedocument.Bookmarks
            .Item("Contact").select
            If IsNull(rsWord!Contact) Then
                objWord.Selection.Rows.Delete
            Else
                objWord.Selection.text = Nz(rsWord!Contact, "")
            End If
            
            .Item("sName").select
            If IsNull(rsWord!SubmitterName) Then
                objWord.Selection.Rows.Delete
            Else
                objWord.Selection.text = Nz(rsWord!SubmitterName, "")
            End If
            
            .Item("Street_Address").select
            If IsNull(rsWord!Address) Then
                objWord.Selection.Rows.Delete
            Else
                objWord.Selection.text = Nz(rsWord!Address, "")
            End If
            
            .Item("Address_1").select
            If IsNull(rsWord!Address1POBox) Then
                objWord.Selection.Rows.Delete
            Else
                objWord.Selection.text = Nz(rsWord!Address1POBox, "")
            End If
            
            .Item("Address_2").select
            If IsNull(rsWord!Address2RD) Then
                objWord.Selection.Rows.Delete
            Else
                objWord.Selection.text = Nz(rsWord!Address2RD, "")
            End If
            
            .Item("Address_3").select
            If IsNull(rsWord!Address2RDArea) Then
                objWord.Selection.Rows.Delete
            Else
                objWord.Selection.text = Nz(rsWord!Address2RDArea, "")
            End If
            
            .Item("Address_4").select
            If IsNull(rsWord!Address3Suburb) Then
                objWord.Selection.Rows.Delete
            Else
                objWord.Selection.text = Nz(rsWord!Address3Suburb, "")
            End If
            .Item("Town_City").Range.text = Nz(rsWord!City, "") & " " & Nz(rsWord!PostalCode, "")
        End With
        
        If bHasLetter Then
            Set cmd = New ADODB.Command
            With cmd
                .ActiveConnection = cnn
                .CommandType = adCmdStoredProc
                .CommandTimeout = 0
                .CommandText = "dbo.sp_4_2_tempLetterData"
                .Execute Parameters:=Array(rsWord!SubmitterNo)
            End With
            Set rsTable = New ADODB.Recordset
            stblData = "dbo.tempLetterData"
            rsTable.Open stblData, cnn, adOpenForwardOnly
            rsTable.MoveFirst
            rsTable.MoveLast
            rsTable.MoveFirst
            iData = rsTable.RecordCount
            If Not (rsTable.BOF And rsTable.EOF) Then
                i = 1
                rsTable.MoveFirst
                Do Until rsTable.EOF
                    If i = 1 Then
                        objWord.activedocument.Bookmarks("Submission_No").select
                        objWord.Selection.text = Nz(rsTable![Column 1], "")
                        objWord.activedocument.Bookmarks("Related_To").select
                        objWord.Selection.text = Nz(rsTable![Column 2], "")
                        objWord.activedocument.Bookmarks("Remedy_Sought").select
                        objWord.Selection.text = Nz(rsTable![Column 3], "")
                    Else
                        ''Need to add another row to the table
                        objWord.Selection.InsertRowsBelow (1)
                        objWord.Selection.MoveUp
                        objWord.Selection.MoveDown
                        
                        objWord.activedocument.Bookmarks.Add Name:="Submission_No" & i
                        objWord.Selection.MoveRight
                        objWord.activedocument.Bookmarks.Add Name:="Related_To" & i
                        objWord.Selection.MoveRight
                        objWord.activedocument.Bookmarks.Add Name:="Remedy_Sought" & i
                        
                        objWord.activedocument.Bookmarks("Submission_No" & i).select
                        objWord.Selection.text = Nz(rsTable![Column 1], "")
                        objWord.activedocument.Bookmarks("Related_To" & i).select
                        objWord.Selection.text = Nz(rsTable![Column 2], "")
                        objWord.activedocument.Bookmarks("Remedy_Sought" & i).select
                        objWord.Selection.text = Nz(rsTable![Column 3], "")
                    End If
                    i = i + 1
                    rsTable.MoveNext
                Loop
            End If
            rsTable.Close
            Set rsTable = Nothing
            stblData = ""
        End If
        ''Print to document to the selected printer
        If sOutput = "preview" Then
            objWord.Visible = True
            objWord.activedocument.SaveAs LetterName(rsWord!SubmitterName, rsWord!SubmitterNo, sLettersLocn)
            objWord.activedocument.PrintPreview
        Else
            'sOrigPrinter = objWord.ActivePrinter
            'objWord.ActivePrinter = sPrinter
            objWord.Application.Options.PrintBackground = True
            objWord.activedocument.PrintOut
            'objWord.ActivePrinter = sOrigPrinter
            ''Save the document
            objWord.activedocument.SaveAs LetterName(rsWord!SubmitterName, rsWord!SubmitterNo, sLettersLocn)
            ''Close the instance of Word
            objWord.Quit
            Set objWord = Nothing
        End If
        rsWord.MoveNext
        
        
   Loop
End If
 
rsWord.Close
DoCmd.Echo True
Set rsWord = Nothing
Set cnn = Nothing
 
''Export the data
DoCmd.OutputTo acOutputTable, "dbo.LettersExport", acFormatXLS, sLettersLocn & "\MailList.xls", False
 
Exit_CreateLetter:
    Exit Function
    
Err_CreateLetter:
    MsgBox Err.Number & ": " & Err.Description
    Resume Exit_CreateLetter
    Set Application.Printer = Nothing
    
 
End Function

Open in new window

ASKER CERTIFIED SOLUTION

Kelvin Sparks

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GrahamSkan

Can you use the mailmerge main document to do a successful merge by doing it manually (in Word)?

If that is possible, it is should also be possible to automate it with .MailMerge.Execute.

SOLUTION

GrahamSkan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Adlerm

ASKER

Hi Kevin and Graham

I would like to thank you both for your terrific input. With a bit of cutting and pasting I now have a solution that work and works well - again my appreciation for your time and experience. Cheers Michael

Kelvin Sparks

Good points in your last post Graham. I've been meaning to redevelop this as an object based approach (which I use with Excel), but am not quite so familiar with Word. That then avoids referencing Word and the issues associated with a user who suddenly turns up with an earlier version.

Yes there's much more you can do here - I only wish I knew the Word side of things better (or even Excel for that matter). It does make Access so powerful as an automation engine - despite all the bad press it gets.

Parts of that Word code were created as a result of my questions here on EE, and I have a hunch Graham, that you may have been the Expert to resolve!!

Anyway Adlerm, I'm glad you found the solution and wqas pleased to have assisted in your solution.

Kelvin