MailMerge datasource directory (WindowsXP / Word2003 NL)

Topic

New Reply

I’ve searched the Lounge, but that didn’t come up with anything useful. What I want to accomplish in VBA code is:
1. load a Word document
2. set this document to be used as the main document for a mailmerge
3. let the user pick a (comma separated) TXT file in a specific directory on the network (put there by a third party application)
4. set that textfile to be used as the datasource file for the mailmerge
5. do other stuff (change printer etc.)
6. execute the mailmerge.
The things I cannot get right are 2/3/4. The main purpose of trying to do this in VBA is that the path to the datasource files is very long and using the wizzard forces the user to click his way down to the desired file each time, because the wizzard always starts in the MySources directory. I tried recording a macro when going through the steps manually, but that failed because virtually nothing was recorded… Any ideas?

Reply | Quote

Replies

Try this:

Sub MailMergeTest()
Dim strFile As String

With Application.FileDialog(msoFileDialogFilePicker)
‘ Substitute appropriate path
.InitialFileName = “serversharefolder*.txt”
.Filters.Add “Text files”, “*.txt”
If .Show = False Then Exit Sub
strFile = .SelectedItems(1)
End With

With ActiveDocument.MailMerge
.MainDocumentType = wdFormLetters
.Destination = wdSendToNewDocument
.OpenDataSource _
Name:=strFile, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
Format:=wdOpenFormatText
.Execute
End With
End Sub

You must insert code for #5.

Reply | Quote

Thanks Hans. I’ll give that a try.

Reply | Quote

Thanks Hans, worked like a charme, as usual.

Reply | Quote

Two more questions about this mailmerge.

1. Every time the mailmerge is executed with a different CSV textfile, a dialogpanel shows up, asking for the field delimiter. As the default, “(None)” is showing. Is it possible to pre-set this delimiter to “,” in VBA code, thus avoiding this dialogpanel?
2. Normally each record consists of 5 fields, separated bij “,”. But every now and then somebody has typed a value in a field that includes 1or more “,”. So Word’s mailmerge thinks that record contains more than 5 fields and generates a message “Too many fields in record X”. And because this CSV textfile is the result of an automated process, this is very annoying. Is it possible to tell Word to ignore the extra field(s) or is there some workaround for this?

Reply | Quote

1) I don’t know.
2) If fields in a delimited text file (such as a .csv file) contain the delimiter character, the fields should be enclosed in quotes. Otherwise, there is no way for the application that processes the text file to know what is what. The application that created the .csv files should have an option for this.

Reply | Quote

Thanks Hans.
About 2): I’ve mailed the third party about our problem and your suggestion of putting the value of each field in quotes. They immediately mailed back with another solution: alter a parameter for their application, to use a semicolon instead of a comma (their default) to separate the fields. I tried this just now and it works fine. I can’t believe they “forgot” to mention this in their documentation…

Reply | Quote