Mail merge to Word

[giladp1]

I found Albert Kallal's great "Super easy Word Merge" code in his site
at:
http://www.members.shaw.ca/AlbertKal.../msaccess.html

Thanks Albert so much for sharing this.

I am looking for any comments about the use of the docmd.transfertext
method instead of the code Albert used for creating the text file.

Also, perhaps some ideas for coding the Subject Line of each email so
it will be different for each merged record.
Can the "Super easy Word Merge" be used for such a purpose?

Thanks greatly
Gilad

[giladp1]

Ok, I now had time to give some more attention to the detail of
Albert's "Super easy Word Merge" and I have some more issues that I
would like to clarify.

Albert, I hope you can read this and respond. That would be really
great.
I actually have been using my own merge code for quite some time and
it has been working fine, but after looking at your code I think it is
potentially really better. I say potentially because as of yet I can
not use it because of multilingual issue I will note in a minute.

What I previously did in my application is link the Word merge
templates to tables in an extra mdb that I added to my mde
application. I also included an extra security mdw file and have code
create an ODBC connection between the two on start of the application.
There are no problems with this setup, but it is much more cumbersome
than linking the Word merge templates to a temporary file. Also, since
I am using the Merge process to create email messages, I really want
to work on this to be able to change the subject line for each merged
record and also perhaps the attachments for each one.

Now for my issues:
Most importantly, using a language other than English in the
application ends up with a document that is gibberish. This only
happened when merging all records in your code, Albert, but not when
merging the current record.
This is critical for me. When i try to open the 888 file in notepad it
does show the text correctly, but opening it in Word brings up the
file conversion dialogue. I wonder if you have any ideas about this.

Two other less important questions are:
Why when creating a new doc not set EmbedTrueTypeFonts to true?
Why the reference to Microsoft visual basic extensibility 5.3

I will also try to continue to work on this. Perhaps I will try to
create the temporary 888 file from access tables using the
docmd.transfertext command. Maybe that will make a difference.
I do not want to specify a local, (even though I wouldn't know where
or how to do this) because I want it to work for all locals.
Any other ideas will be most welcome of course.
Best
Gilad

[Albert D. Kallal]

Now for my issues:

Most importantly, using a language other than English in the
application ends up with a document that is gibberish. This only
happened when merging all records in your code, Albert, but not when
merging the current record.
This is critical for me. When i try to open the 888 file in notepad it
does show the text correctly, but opening it in Word brings up the
file conversion dialogue. I wonder if you have any ideas about this.

Hum, I surprised that a "single" record does work. It is important to note
that when a single record is used, I take all fields from the screen + all
fields from the underlying reocrdset. I do this, because you can thus
actually use UN-BOUND controls on your form, and those un-bound controls
will appear in the merge drop down list in word. This perhaps explains why a
single merge works - but, I extremely surprised, as just plain text is
written out, and it is NOT uni-code text. The VBA write commands to create a
text file are plain text, not uni-code text files.

Anyway, for the "many" merge, I use code to write out the text, and the
standard writing out of the text file gets it data source from a reocrdset.
Once again, uni-code text is NOT created when you use the write commands.

Two other less important questions are:
Why when creating a new doc not set EmbedTrueTypeFonts to true?

I don't touch that setting at all. I simply execute a word merge to a new
document. do you think there is a need to add this as a option? How much
does this bloat a document? It certainly could be added as a setting.

Why the reference to Microsoft visual basic extensibility 5.3

Hum, that should not be there!!! (I don't know why then that sunk in). I
will remove that when I get a chance today (thanks for the heads up).

I will also try to continue to work on this. Perhaps I will try to
create the temporary 888 file from access tables using the
docmd.transfertext command.

You could consider using transfertext (you have to write out the supplied
sql to a temp query, and then transfer on that).

As mentioned, for a single record merge, I use the recordset + un-bound
controls on the form (so, for single merge transfer text is not possbile --
I still surprised that a single merge works). For the "many" merge, it
certainly a possibility to consider the above. Note that because I using a
comma delimited file, the code actually changes all " (double) quotes to '
(single) so the word merge don't get confused when it tries to import. (you
can't do that with transfer text, and if users type in a " (double) quote
into the text, your merge is going to fail on the word side.

Maybe that will make a difference.
I do not want to specify a local, (even though I wouldn't know where
or how to do this) because I want it to work for all locals.
Any other ideas will be most welcome of course.
Best
Gilad

The solution to this is really quite simple. In place of writing out text
via "print" command, I simply has to start using the File Scripting Object
(FSO). It is good deal slower then the current "native" print command, but
for most merges it not likely to be even noticed (it not like machines are
slow today!!). So, I think perhaps I bite the bullet, and start using the
FSO...as that would fix this problem...

I will check into this, but I believe the problem is that the "write #", and
"print #" in VBA is plain ASCII and does NOT support uni-code....
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[giladp1]

(I am posting this a second time since the first didn't show up)

Albert, thanks so much for your comments.
I am trying to implement your ideas.
To recap, I have come up with gibberish when merging to word using
Albert Kallal's "Super easy Word Merge".
There are two options of merging using this code. With one I end up
with gibberish and with the other I don't. I can't figure the relevant
difference between the two options since both use a query, and then a
string to hold data from this query, and then the #print method to
write this data to a text file.
Albert, if you can shed some light on this to help me understand why
merging the single record causes gibberish, that would be very
interesting. By the way, you mentioned that the code for merging the
single record uses unbound form controls as well as bound ones. I
looked and noticed that your code first runs through the recordset-
clone of the form and then looks through the controls on the form.
When I used the code the information was only gathered from the
recordset-clone, and the rest of the data gathering for the merge did
not add anything to the "strData" variable. So in both cases a
recordset is used, so I don't understand the different behaviour.

Now for the suggested solutions, and my next question:
I am trying to consider the better alternative of the two following
options in order to create the data-source text file for the merge:
1) The docmd.transfertext method
2) The FSO

It seems to me that both may have disadvantages, and I am trying to
weigh them against each other. The possible disadvantages of each are
as follows:

For 1 above, the disadvantage may be:
The transfertext does have an optional argument for 'Code page'. Will
this be cause trouble if deploying an application to users from
different locations and different default code pages? In some
situations perhaps again the user will end up with gibberish in the
merged result, if the code page will not be included in the command or
if one will be included and will not be appropriate to that user's
local.

For 2 above, the disadvantage may be:
I read that the SCRRUN.DLL for the FSO is distributed with VB6 and
with Internet Explorer. Do you think that I should distribute it with
my application just in case a user does not have either of these two
programs installed?
This may complicate matters, since I understand such a dll must also
be registered on the user machine as well. Does anybody know if it is
also distributed with Access?
Any thoughts about this will again be greatly appreciated

Gilad

[Albert D. Kallal]

For 1 above, the disadvantage may be:

The transfertext does have an optional argument for 'Code page'. Will
this be cause trouble if deploying an application to users from
different locations and different default code pages?

Well, I assume the solution would be to not use the code page parameter, and
it would thus default to the correct locale.

>
For 2 above, the disadvantage may be:
I read that the SCRRUN.DLL for the FSO is distributed with VB6 and
with Internet Explorer. Do you think that I should distribute it with
my application just in case a user does not have either of these two
programs installed?

I never seen windows without the FSO. However, some corporate environment
do turn off windows scripting, and that could perhaps make this fail, but
even with scripting turned off, you likely can still use the FSO.

This may complicate matters, since I understand such a dll must also
be registered on the user machine as well. Does anybody know if it is
also distributed with Access?

I would actually not worry. I never have come across a machine that
don't have the FSO. The failure rate is going to be *extremely* low

However, it likely better to re-write the output code I use to output
unit-code text.

I don't use transfer text because I would have to write out a temp query
each time, and this re-writing causes bloat. (we would be saving the query
over and over for each merge). further, as mentioned, if you use transfer
text, and the user types in the " into the text, then the word merge is
going to fail (so, you have to change each " to a single quote BEFORE you
merge, but that will require you to modify the users data..and we can't do
that!!

Last, but not least, there is a serous draw back to this!!!

the problem is that you can't use automation in word to select and tell word
that the merge data is uni-code. The *only* way I know is to use a odbc
connection, and write out what is called a schema.ini file for word. In
other words, you CAN NOT set the csv word merge to use uni-code (except
through the user interface). I have to look further, but it not that hard to
modify the ms-access code to output uni-code, but you can't modify the code
that does a the word merge to *USE* uni-code.

Until I find a solution as to how to tell word to use uni-code...we duck
soup...

To modify ms-access to output uni-code, you cna go:
Open strOutFile For Binary As intFile
'Print #intFile, strFields
strFields = strFields & StrConv(vbCrLf, vbUnicode)
Put #intFile, , strFields

' output all data
Do While rstOutput.EOF = False

strData = "" ' one line of data for csv file
For Each OneField In rstOutput.Fields

If strData <"" Then strData = strData & StrConv(",", vbUnicode)
strData = strData & StrConv(Chr(34), vbUnicode) &
StrConv(rstOutput(OneField.Name),
vbUnicode) & StrConv(Chr(34), vbUnicode)

Next OneField

strData = strData & StrConv(vbCrLf, vbUnicode)
Put #intFile, , strData

So, we can use "put" and *always* convert each character we put into the
string with strConv.

The resulting text file will be uni-code, and we not had to use FSO, or
transfertext.

However, this does not solve our problem of telling word to use uni-code for
the csv inport....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[giladp1]

I'm beginning to think that my original solution was good enough.
As I wrote above, I use temporary tables in an additional mdb that i
link to from my main front-end mde. Then I add another mdw file to my
setup. I creat an odbc connection between the two using code, and
verify this connection on each start up. Then I empty and fill these
tables in this additional mdb, and link the Word merge documents to
these tables. Without the odbc connection Word requires a password
each time a merge is conducted. The odbc connection has a password for
the mdw file so Word is satisfied and also no other instance of word
is opened, as i know sometimes happens without the odbc connection, so
it works well.

It has worked well for me for quite a while, but not much testing on
other computers.
There are two main reasons I was thinking of changing to your method
and linking the merge documents to a text file instead of to my mdb
tables:
1) It seems more simple, robust and stable. There is no need for the
creation of the odbc connection which will reduce the time it takes
the application to start up. Also no need for an additional mdb
(holding the data for the merge) and no need for the mdw file.
2) I am thinking of access 07 which as I understand is removing
security altogether, so no more mdw files. I want to be ready for this
change. But, perhaps since there will be no more use for security mdw
files, the merge will not require any odbc connection to such a file
to begin with, and my problems will be solved.

What do you think of my previous method for setting up the merge to
Access tables and creating the odbc connection? Are there any other
problems or pitfalls with it that I should be aware of?

[Albert D. Kallal]

What do you think of my previous method for setting up the merge to
Access tables and creating the odbc connection? Are there any other
problems or pitfalls with it that I should be aware of?

The problem is what happens if you have different versions of ms-access
installed on that machine? What version does word launch? I don't
particularly like having word connect to a mdb file, but it certainly a
solution to the uni-code.

if I could tell word that the text file is uni-code, then problem would be
solved..but, I can't.

Assuming that word uses odbc, and NOT DDE, and does NOT launch ms-access,
then I do think creating a temp mdb file is a great idea....

In fact, I might even adopt that idea. I always avoided using a mdb file
because word launches a copy of ms-access. However, if you use odbc, then
that would possible avoid that problem.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[giladp1]

The only problem I did have with this was for merging Memo fields.
They get truncated to 256 characters in the resulting Word document
I think in one point of time Microsoft solved this issue in one of the
service packs, but then in the next one the problem repapered again in
all its might to aggravate me.

Fortunately I have only one such field to merge, and it is always for
a single record, so I solved this issue by looking through the merge
document before instigating the merge, finding this particular merge
field, replacing it with the text from the mdb, and then instigating
the merge. So in essence, for this particular field I am using a very
bad and primitive way to merge data from the mdb into it. I am editing
the merge document first, and then running the merge, the edited Merge-
document is not saved and so I can continue to re-use it next time I
merge.
I hope I made this clear enough. It works and I am not worried about
any more service packs.

Gilad