Solved

Mail Merge frpm Access 2003

Posted on 2009-03-30
17
816 Views
Last Modified: 2013-11-27
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
0
Comment
Question by:Adlerm
  • 7
  • 5
  • 5
17 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 24017384
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
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 24017419
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

0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 24017440
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.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 24017502
Otherwise try:
        .MailMerge.OpenDataSource Name:=inpDatabase, _

         LinkToSource:=True, _

         SQLStatement:="SELECT * FROM `qry1200_sel_5PStudyCourseToStudentReportParameters`"

Open in new window

0
 

Author Comment

by:Adlerm
ID: 24018740
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.  
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 24022989
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.
0
 

Author Comment

by:Adlerm
ID: 24024174
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.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 24024221
Re single quotes.

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

Have you tried omitting the OpenDataSource step?
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 24024718
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
0
 

Author Comment

by:Adlerm
ID: 24025956
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.
0
 

Author Comment

by:Adlerm
ID: 24026024
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)
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 24026034
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

0
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 180 total points
ID: 24026066
Further to post above - the pros and cons of this approach

Firstly a mail merge will normally give you 1 document will all letters in, this creates a 1 doc for each recipient in a given location.

I have some options on my form to enable a preview for 1 letter to check & test. The recipients are in a list box, so have anabled them to be selected in batches for printing to avoid tying up printers for lengthy periods.

Preview on may is a pain and you don't want to end up in that situation.


Kelvin
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 24026733
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.

0
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 70 total points
ID: 24027205
You could avoid the MailMerge mechanism with this code.

You would need to remove the mailmerge status from the document  and then save it as a template.

The code uses early binding, so needs a reference to the Microsoft Word object library.

Currently it ignores the Word field format switches. If this becomes an issue, the code can be tweaked.
0
 

Author Closing Comment

by:Adlerm
ID: 31564261
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
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 24046508
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
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

This is written from a 'VBA for MS Word' perspective, but I am sure it applies to most other MS Office components where VBA is used.  One thing that really bugs me is slow code, ESPECIALLY when it's mine!  In programming there are so many ways to…
This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…

746 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

14 Experts available now in Live!

Get 1:1 Help Now