After Mail merging single documents - how to combine several documents into one?

[Abacus1234]

I have a vba process for mail-merging many documents. I have several one-page-documents that really belong together in one document. However each document is pushing the limits of data I can handle in one query. Does anyone have any suggestions for how to bring several documents together into one after the merge. I need a solution that will work on 2002 and up.

Thanks for any suggestions.

[macropod]

If the mailmerge main documents all reference the same data source (and sometimes even if they don't), you should be able to combine them into a single mailmerge main document, thus obviating the need to splice the output together afterwards.

[gmayor]

If you cannot do as Paul suggests, then see http://www.gmayor.com/Boiler.htm which will combine the files subject to the issues that would be raised by disparate formatting.

[Abacus1234]

Thanks for both suggestions. I will look at the suggestions. Each page looks at a different Query, and each query is pushing the limit of too many fields. So I don't think I can make 3 pages into one document and then run them through the vba merge process. That would be the most ideal solution.
This is a production system used in many offices, and users have different versions of Office. So it needs to be kept as simple as possible. I see products generally using .pdf that seem to make it very easy and seamless. I really need one simple solution for all versions of Office. Just asking for the moon! I would have thought it could be done in vba code, but I don't know where to begin. Thanks again.

[macropod]

You mention three different queries, a single mailmerge main document can be configured to process one query (to implement conditions that apply globally), and field coding, via IF, SKIPIF fields and the like can typically be used to do the rest. Or, if you want tabular output, DATABASE fields can be used to run their own queries (even against different data sources). No VBA or post-merge document consolidation required. These approaches all work with any Office version and, once the initial configuration is done, should be easy for anyone to use.

[Abacus1234]

Hi Paul,
I'm late getting back on this post. I had a database meltdown, and had to spend several days getting back to where I was.

I will take some time to try to understand your suggestions. I have two problems I would like to solve.

1. combining 5 documents that run as single pages, because they have so much data that I have to have 5 queries.

I have an access database, the vba code that does the mailmerge, looks at an access table that has the name of a document, and a field containing the name of the query that the document relies on to perform the mailmerge. The documents to be merged and printed are checked off by the user, and the code takes each document, merges it, and prints it until all documents are printed. Or I can take a single document, merge it to the screen, and make changes before printing. This is why I tend to think a little more vba code, and I could combine the 5 one page documents into a single document, which the user could then email as one which would be more professional for them.

2. I have (I hope this is the right terminology) a multi-record table, that I now have as a report. From the user perspective this needs to be one of the pages in the 5-page document, but I don't know how to reproduce the data in a document. I understand that I should be able to do this through the ?"directory"? mailmerge functionality, but I haven't yet been able to get to a point where I can grasp what the tutorial is telling me. Yet another area to continue to research.

Thanks again for the suggestions. Lots of homework for me to do.

[macropod]

Without knowing what queries you're using for each of the five documents, I can't really advise on the specifics of how you'd combine all five documents - I can only give general advise such as in my last post. The DATABASE field can certainly be used for your multi-record table. For what you're doing, a Directory merge would probably over-complicate things. For a non-mailmerge DATABASE field implementation, see: https://www.msofficeforums.com/mail-...-coloumns.html . As you'll see there, the DATABASE field executes its own query.

[Abacus1234]

see last post. thanks

[Abacus1234]

Hi Paul,

I am unable to make this document work. It is stripped down with accompanying datasource.

Could you suggest what I am doing wrong? Thanks.

[macropod]

The DATABASE field in your last post is encoded as:

{ DATABASE \d "{ OS(C:)\test2a\test2a.doc\p/../OS(C:)\Test2a\Test2a.mdb"\s"SELECT [sectionnumber], [description],[borrowerpaid],[borrowerpoc], } FROM QCDPage2areport WHERE [Page2AID=30 \ 1 “1”\b”6”\h” }

Note that all of:

{ OS(C:)\test2a\test2a.doc\p/../OS(C:)\Test2a\Test2a.mdb"\s"SELECT [sectionnumber], [description],[borrowerpaid],[borrowerpoc], }

is actually another field - all the content is enclosed in a pair of field braces! That will never work, since both your datasource and part of your SELECT statement are inside the field. You may have been led astray by the presence of the {FILENAME \p} field code in the link I posted. That's only there to get the DATABASE field used in that thread to look in whatever folder the document calling it is in - it's not something you'd use if the data source is in another folder.

Your DATABASE field code is mal-formed in other respects, too. It should be something along the lines of:

{DATABASE \d "C:\\Test2a\\Test2a.mdb" \s "SELECT [sectionnumber], [description],[borrowerpaid], [borrowerpoc] FROM [QCDPage2areport] WHERE [Page2AID]=30" \l "1" \b "6" \h}

The sample document attached to your previous post is even worse - it contains one DATABASE field inside the other, and both contain yet another field within them.

The following excerpt based on a discussion in another forum might help:

Suppose you need to create individual merge letters to the members of numerous teams, each with their team number and ID and the letter is to include a list of the individual’s team mates (but without including the individual in the list). The data source is an Excel worksheet with the following field names:
Firstname, Surname, ID, Team
eg:
Alexander, Thompson, 1024, 12
Heather, Thompson, 1025, 12
Michael, Thompson, 1026, 12
Lynette, Andrews, 257, 10
Michael, Richards, 1026, 11
In this case, you could employ a database field, where you:
• use your Excel worksheet as the data source for the merge; and
• insert a DATABASE field into the mailmerge main document to select the records for each individual’s team.
For example, suppose your workbook is “C:\Documents\MyData.xls” and the data are on “Sheet1”. The following set of nested field codes should result in a one-column table listing the name of each participant in the team other than the individual to which the merge record applies:
{DATABASE \d " C:\\Documents\\MyData.xls" \s "SELECT [firstname] & ' ' & [surname] FROM [Sheet1$] WHERE [Team] = {MERGEFIELD Team} AND [ID] <> {MERGEFIELD ID}"}

[Abacus1234]

Hi Paul,

I have now corrected and simplified everything, but cannot get it to work.

It does work if I do it manually..... which is something...

I have attached document and database, would you look at it again.

Thanks.

[macropod]

What you need to add to your DATABASE field is the mergefield condition that needs to be satisfied to generate the output. For example, you might need to replace:
WHERE [Linktoclosing]=30
with:
WHERE [Linktoclosing]={MERGEFIELD Closing}
or add an AND condition like the one at the end of the quoted content in my previous post.

[Abacus1234]

Hi Paul,

Thanks for your help as always.

Together with a colleague we have now spent many hours trying to learn the secrets of the datafield merge. I have attached some of our results. As I want to format a report with sub-totals, which would need much more penetration into the "secrets", I think this functionality is not stable enough for my purposes.

I am left with a very nice report, that I would like to turn into a word document. Could you suggest any other process that would do this. If I could say "select all, copy, open document, paste" wouldn't that be a happy ending.

[macropod]

Without knowing exactly what a given table in your report should contain and how it should be formatted, I'm unable to give much help. What I can say, though, is that all of the following code seems unnecessary:

\c "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=F:\\test2a\\Test2a.mdb;Mode=Read;Extended Properties=\"\";Jet OLEDB:System database=\"\";Jet OLEDB:Registry Path=\"\";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=\"\";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False"

[Abacus1234]

Hi Paul,

It is what the system produces when pulling in the data manually through quick parts. I thought that would throw light on the problem, but it didn't. I don't understand why I have to have a Mergefield in the WHERE clause. Also, when I try it through Quickparts it does not select the fields that I ask for. I have attached what the final report looks like. Thanks.