Integrate Word mail merge in MS Access

[Kayleigh]

Hi
I'm looking to integrate a mail merge facility into an MS Access DB. I know there are solutions to this out there but couldn't quite find what I'm after.
I would like as little user input as possible. So the user can select a letter template they wish to merge with a particular data source (in the DB) and then the letter opens linked to the source. Then the user should specify what merge fields to include and where (not sure how possible it is to automate this part eg. enter line number and the field to place there?)
Then it would create a template with all the letters merged.
Any advice would be appreciated
Krayna

 

[Kayleigh]

Hi there,
Further to my post above, I have been able to start coding based on some snippets I have found but my code keeps throwing errors particularly 4198. I have enclosed a sample. Bear in mind that my DB is split with BE being another Access DB (hence the connection string).
I've never done much with Word VBA so feel as if I'm poking in the dark.
Thanks for your help.

NB. Some helpful URLs for this topic:

Connection String for linked tables

Hello All, I have multiple table linked via ODBC connection. This includes a connected oracle db. I have a code that is running some queries and then send it to excel. However during the code it is asking for user name and password. I input the details and it continues. I am trying to have...

www.access-programmers.co.uk

Run-time error '5922' - Mail Merge - Page 5

Afternoon all, Sorry for the mislead title - I had that error previously and will probably have it again, but another hump got in the way, so I amended my post. I'm trying to set up a mail merge from Excel to Word. I've got it working manually, but need to automate the process for our...

www.vbaexpress.com

Runtime Error 4198 in Word Macro when ActiveDocument.SaveAs - vbCity - The .NET Developer Community

vbCity is a community of VB and .NET developers joined together with a common goal: to learn, teach, and have fun programming. Developers from all over the world come together to share knowledge, source code, and tutorials for free to help their fellow programmers - Professional Developers...

vbcity.com

How to automate Word with Visual Basic to create a Mail Merge - Office

Describes that how to automate Word with Visual Basic to create a Mail Merge.

docs.microsoft.com

UtterAccess.com

www.utteraccess.com

MailMerge.OpenDataSource method (Word)

Office VBA reference topic

docs.microsoft.com

 

[Pat Hartman]

I don't use mail merge. I automate word to populate the documents. I've attached a very simple sample so you can see how it is done. The sample uses hard-coded fields so it isn't very practical. I have applications that use tables to manage the documents and data fields that manage thousands of documents without programmer intervention. As long as the data required for the document is "known" to the application, the user just creates a new word document with the appropriate bookmarks and then defines the document to the application and maps the bookmarks. I'll also post pictures from the production application which I cannot post for obvious reasons as well as the section of the schema related to these documents. This particular application is different in that the variable data is an EVA schema which is essentially one column per row so it is ultra normalized and not particular efficient but what it does do is it allows the use to create new insurance policy types and if the data they need to collect doesn't already exist, they can simply add a new field to the fields table and that causes it to show p on the data entry form (which I'm not posting unless you want to see it) as well as to be available for use with any document.

Word Automation Sample

This database presents an example of using VBA to fill bookmarked fields in a Word document. The Database is A2003 format. However, the only version of Word installed on my PC is W2007 so before using the example, you may need to open any code module and change the Word reference to whatever...

www.access-programmers.co.uk

 

[moke123]

I've used Albert Kallal's super easy wordmerge for years. It can be found here http://www.kallal.ca/msaccess/msaccess.html

here's a modified example using his app.

 

[Pat Hartman]

Mail merge won't handle things like invoices where you have header/detail data. That's why I don't use it.

 

[Kayleigh]

Thanks @moke123 but that is a little too advanced for my needs.

My key problem at the moment is how to successfully link the data to the word document from DB whilst open. Every option I try throws all kinds of errors. I tried OLEDB and DDE but it will not work.
I have read through all Microsoft documentation on this. If anyone has any clearer info on this I would be most grateful.

 

[SHANEMAC51]

My key problem at the moment is how to successfully link the data to the word document from DB whilst open.

because of all these problems with merging - I use the WORD bookmark filling from the code that is located along with the data table in ACCESS/EXCEL

of course, there are problems in this approach, but they are more solvable

additionally , the code is embedded
1 - data filtering
2 - data correction
3 - if you apply the replacement field (NOT BOOKMARKS), then you can make nested substitutions

 

[SHANEMAC51]

3 - if you apply the replacement field (NOT BOOKMARKS), then you can make nested substitutions

for example: we have a label (address)
in it you can enter not only the full address, but also a partial one, and there will be no extra commas

mrepl "(address)", ("," +country) & ("," +city) & ("," +street)

Option Explicit
Public app As Object ' Word.Application
Public strAppPath As Stream
Sub mrepl(nfind, nrepl)  ''in public module
app.ActiveDocument.Select
Debug.Print nfind, nrepl
app.Selection.Find.ClearFormatting
app.Selection.Find.Replacement.ClearFormatting
app.Selection.Find.Replacement.Font.ColorIndex = 15 '' wdViolet
    With app.Selection.Find
        .Text = nfind & ""
        .Replacement.Text = nrepl & ""
      '  .Forward = True
      '  .Wrap = wdFindContinue
        .Format = True
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    app.Selection.Find.Execute Replace:=2
    ''wdReplaceAll
   
End Sub
Sub mm220518()  ''in form
mrepl "(address)", "(Country)(city)(street)"
mrepl "(Country)", (", " + Me.Country)
mrepl "(city)", (", " + Me.city)
mrepl "(street)", (", " + Me.street)
mrepl "(balance)", Me.balance
mrepl "(balance in words)", func_amount_in_words(Me.balance)
'''''
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function func_amount_in_words(summa As Currency) As String  ''in public module
''''
End Function

 

[Kayleigh]

This sounds like an interesting approach. Would you have a sample app where it is integrated in?

 

[SHANEMAC51]

This sounds like an interesting approach. Would you have a sample app where it is integrated in?

lay out an example of the database and a realistic template
I will correct my code for your data

 

[SHANEMAC51]

names of tables and fields in English and 4-10 filled lines

 

[moke123]

Thanks @moke123 but that is a little too advanced for my needs.

My key problem at the moment is how to successfully link the data to the word document from DB whilst open. Every option I try throws all kinds of errors. I tried OLEDB and DDE but it will not work.
I have read through all Microsoft documentation on this. If anyone has any clearer info on this I would be most grateful.

My implementation is a little more complicated than Alberts basic app due to my needs. I needed to have different libraries of documents, so they could be sorted by user, content, etc. I also needed to use a non -normalized table as I need data from several unrelated sources.

Alberts basic app is pretty simple to use, its basically drag and drop and it works.

 

[Pat Hartman]

Thanks @moke123 but that is a little too advanced for my needs.

Did you look at the sample I posted? It has nothing to do with the pictures.

 

[Kayleigh]

names of tables and fields in English and 4-10 filled lines

Thank you for the offer.

I have enclosed a sample DB with 10 records filled in. The query 'qryMailMerge' should have all the info for the merge. Also the italised words in the template would be where the merged fields go.

 

[moke123]

Just as an example, I imported Alberts code into your database. (Alberts original code, not my version)

I added a button with the code

Private Sub Command145_Click()
MergeAllWord "qryMailMerge"
End Sub

I then clicked it and created a Mailmerge template by selecting "add new template" and saved it. It took less than 5 minutes overall.

To test it, click the command button I added. Then choose the test document and click "Ok Merge to word"

You can also create a new template , modify a template, or delete a template.

Edit: by the way there was no template included in your post.

 

[Kayleigh]

Thank you for this

I am also trying to use Albert's code but would like to make some amendments.
1. The user can select a previously created file and use as a template.
2. The files do not all need to be saved in the same location (using a table to store static file addresses).
3. Option to select data source (queries).

 

[moke123]

Read through Alberts comments in his code.
There are many options to consider.

 

[HiTechCoach]

Thanks @moke123 but that is a little too advanced for my needs.

My key problem at the moment is how to successfully link the data to the word document from DB whilst open. Every option I try throws all kinds of errors. I tried OLEDB and DDE but it will not work.
I have read through all Microsoft documentation on this. If anyone has any clearer info on this I would be most grateful.

You should never (the rare time never should be used) want to link Word mail merge to an Access database, epically if you have it opened. You are only asking for trouble.


From: Page Title (kallal.ca)

Don't let word attached to you running mdb/accdb file.
Again, this is just common sense. A large portion of word merge examples actually let word OPEN your mdb/accdb file. This is a formula for disaster. Allowing word to open the mdb file means that you have to deal with some known bugs (like word launching a second copy of ms-access for example). Further, if you implement security in ms-access, now word has to deal with passwords and permissions (again, a huge can of worms). Worse is if you have the runtime of ms-access installed, or multiple versions of ms-access, then word can again cause all kinds of problems as it tries to launch ms-access, and it might even launch the wrong version of ms-access. I could again rant on for pages here, but I think any developer can clearly see that if we prevent word from trying to attach to the ms-access mdb/accdb file, then we avoid a TON OF POSSIBLE problems. Again, since we CAN control this, then lets do so. As a result, my sample merge code DOES NOT let word attached to the mdb/accdb file. As a result, it is rock solid. As a result, it just works!

Of course what I do is create a temp txt file with the one record merged as a text csv file. Note that I used to use a file name of merge.txt. However, it turns out that a bug exists in the word97 merge on pc's with file extensions HIDDEN. The problem is especially noticeable on win xp machines, since hidden extensions is a common default for many users pc's. So, the solution here is to either turn on extensions, but that is hard to do. It is way too much to ask users to re-configure their pc's to show file extensions. However, by using a file name with a non registered file extension, this known word merge bug can be avoided! Thus, I now use a file name of merge.888, and this avoids this bug. If you need to use those merge files in Excel, you can either change my code, as the file name used is a constant. You can also just re-name the temp file also.

The Super Easy Mail Merge by Albert Kallal does exactly what you described as your needs. You can import it and start using it. I have never had to modify the original code in over 10 years.

The original cod is rock solid and works. I would only use the code from the original source.

The original code can be found on this page Super Easy Mail Merge (scroll down page)

If I needed additional functionality, I never modified the original code. I used wrapper functions to extend the functionality.

I urge yoy to import the super easy mail merge into a copy of your front end (hopefully you app is split) and test it fully. You may find that it already does everything you need, just in a little different method.

 

[moke123]

You should never (the rare time never should be used) want to link Word mail merge to an Access database, epically if you have it opened. You are only asking for trouble.


From: Page Title (kallal.ca)



The Super Easy Mail Merge by Albert Kallal does exactly what you described as your needs. You can import it and start using it. I have never had to modify the original code in over 10 years.

The original cod is rock solid and works. I would only use the code from the original source.

The original code can be found on this page Super Easy Mail Merge (scroll down page)

If I needed additional functionality, I never modified the original code. I used wrapper functions to extend the functionality.

I urge yoy to import the super easy mail merge into a copy of your front end (hopefully you app is split) and test it fully. You may find that it already does everything you need, just in a little different method.

Knock on wood that Alberts prediction many years ago that future updates would break his code never came to pass.

 

[Pat Hartman]

Did you ever look at the sample I posted? It does not use mail merge. It uses OLE automation. The example has hard coded field names but it isn't hard as I described to map fields to documents and use a loop to populate them. The code should be far easier for you to understand.