Integrate Word mail merge in MS Access (2 Viewers)

Kayleigh

Member
Local time
Today, 16:34
Joined
Sep 24, 2020
Messages
706
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

Member
Local time
Today, 16:34
Joined
Sep 24, 2020
Messages
706
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:
 

Attachments

  • MailMergeDB.accdb
    2.6 MB · Views: 274

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:34
Joined
Feb 19, 2002
Messages
42,970
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.

DGT_AllFields.JPG
DGT_ProductDefinition.JPG
DGT_DocumentFields.JPG
DGT_EAVtableSchema.JPG
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:34
Joined
Feb 19, 2002
Messages
42,970
Mail merge won't handle things like invoices where you have header/detail data. That's why I don't use it.
 

Kayleigh

Member
Local time
Today, 16:34
Joined
Sep 24, 2020
Messages
706
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

Active member
Local time
Today, 19:34
Joined
Jan 28, 2022
Messages
310
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

Active member
Local time
Today, 19:34
Joined
Jan 28, 2022
Messages
310
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

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

Code:
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
 
Last edited:

Kayleigh

Member
Local time
Today, 16:34
Joined
Sep 24, 2020
Messages
706
This sounds like an interesting approach. Would you have a sample app where it is integrated in?
 

SHANEMAC51

Active member
Local time
Today, 19:34
Joined
Jan 28, 2022
Messages
310
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

Active member
Local time
Today, 19:34
Joined
Jan 28, 2022
Messages
310
names of tables and fields in English and 4-10 filled lines
 

moke123

AWF VIP
Local time
Today, 12:34
Joined
Jan 11, 2013
Messages
3,852
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

Super Moderator
Staff member
Local time
Today, 12:34
Joined
Feb 19, 2002
Messages
42,970
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

Member
Local time
Today, 16:34
Joined
Sep 24, 2020
Messages
706
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.
 

Attachments

  • MailMergeDB.zip
    545.2 KB · Views: 209

moke123

AWF VIP
Local time
Today, 12:34
Joined
Jan 11, 2013
Messages
3,852
Just as an example, I imported Alberts code into your database. (Alberts original code, not my version)

I added a button with the code
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.
 

Attachments

  • MailMergeDB_AKMM2.zip
    654 KB · Views: 223
Last edited:

Kayleigh

Member
Local time
Today, 16:34
Joined
Sep 24, 2020
Messages
706
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

AWF VIP
Local time
Today, 12:34
Joined
Jan 11, 2013
Messages
3,852
Read through Alberts comments in his code.
There are many options to consider.
 

HiTechCoach

Well-known member
Local time
Today, 11:34
Joined
Mar 6, 2006
Messages
4,357
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

AWF VIP
Local time
Today, 12:34
Joined
Jan 11, 2013
Messages
3,852
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

Super Moderator
Staff member
Local time
Today, 12:34
Joined
Feb 19, 2002
Messages
42,970
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.
 

Users who are viewing this thread

Top Bottom