mail merging

[99mel]

Anyone got any links to good info on doing this?

i'm actually mail merging a recordset into a word document. How ever theres aboout 50 records and after its mail merged it saves the document, thus getting 50 .docs which i don't want!

Is it possible to have all the records mail merged into one document? where you can navigate through each record in the document?

Cheers

 

[softwarescience]

Hello

When you say 'Mail Merging' do you mean that you are outputting the recordset to a text file then running a word object MailMerge execute (i.e. Word.activedocument.MailMerge.execute) or are you directly inserting the data into a word document.

I.e. what is your data source?

 

[99mel]

whichever.. i trying to insert the data straight into the word document.

I have the data in a adodb recordset in vb!

As there are multiple records in this recordset, i need all the records put into one document, which can then be printed off at later date manually.

At the moment I have some code which inserts the data from the recorset and then saves the word document, leaving me with loads of word documents! i need all the records to be in one document at the end of the day.

Cheers for any help!

 

[softwarescience]

Ok.

Output the recordset to a text file or create a stored query in your Access.
Create a mail merge template in word based on the data file you created ensuring that the document TYPE is 'Catalogue'.
This will repeat the format you used for the data with a line between.

In Vb you would do;

Word.activedocument.MailMerge.maindocumenttype = wdCatalog
Word.activedocument.MailMerge.opendatasource (MailData)
Word.activedocument.MailMerge.Destination = wdSendToNewDocument
Word.activedocument.MailMerge.Execute

 

[99mel]

i was using this code, which i pass a recordset containing all the records i want to be mailmerged!
This however saves each document which i dnt want.
Is it possible to modify this code to return all the records in one document?



Dim loWordApp As New Word.Application
Dim loDoc As New Word.Document
Dim loGlobVar As New Word.Global
Dim Nc As Integer
Dim liPos As Integer
Dim lsFieldName As String
Dim lsaFldNames() As String

Dim counter As Integer

If Not rRecords.EOF Then
rRecords.MoveFirst


While Not rRecords.EOF
counter = counter + 1
txtcount.Text = counter
loWordApp.Documents.Add (sMergeDoc)
Set loDoc = loWordApp.ActiveDocument
loDoc.Activate

ReDim lsaFldNames(loDoc.MailMerge.Fields.Count)



For Nc = 1 To loDoc.MailMerge.Fields.Count
lsFieldName = Trim(Right(loDoc.MailMerge.Fields(Nc).Code, Len(loDoc.MailMerge.Fields(Nc).Code) - 11))
lsaFldNames(Nc - 1) = lsFieldName
Next Nc


For Nc = 1 To loDoc.MailMerge.Fields.Count
loDoc.MailMerge.Fields(1).Select
If IsNull(rRecords(lsaFldNames(Nc - 1))) Then
loGlobVar.Selection.TypeText ""
Else
loGlobVar.Selection.TypeText rRecords(lsaFldNames(Nc - 1))
End If
Next Nc
loDoc.SaveAs "C:\" & counter & ".doc"
loDoc.Close False
rRecords.MoveNext
Wend
rRecords.Close
loWordApp.Quit
End If

Set loDoc = Nothing
Set loWordApp = Nothing
Set rRecords = Nothing

 

[softwarescience]

The problem is that during the recordset while loop you are doing 'loWordApp.Documents.Add (sMergeDoc)' and at the end of the loop you are 'loDoc.SaveAs "C:\" & counter & ".doc"' this means that for every record you are creating and saving a document.

You need the 'loWordApp.Documents.Add (sMergeDoc)' before the loop then use the 'Selection.InsertBreak(wdPageBreak)' command to create a new page after each record then move the ' loDoc.SaveAs "C:\" & counter & ".doc" loDoc.Close False' statements outside the loop

Along the lines of the following

    loWordApp.Documents.Add (sMergeDoc)
    Set loDoc = loWordApp.ActiveDocument
    loDoc.Activate
    
    While Not rRecords.EOF
        counter = counter + 1
        If counter > 1 Then
            loWordApp.Selection.InsertBreak wdPageBreak
        End If

        'your code

        rRecords.MoveNext
    Wend
    
    loDoc.SaveAs "C:\" & counter & ".doc"
    loDoc.Close False

 

[99mel]

I have tried this and after the first loop the loDoc.MailMerge.Fields.Count = 0

i.e. it cant find any mail merge fields in the document, is this because a page break was added and its look on the new page or somert?

I'm gonna have a fiddle

Any suggestions?

 

[99mel]

The way this code is doing it is by overwriting the mail merge fields with the data, which is a bit poo.

So obviously once the first record has been wrote, there are no more fields left.

Anyone know any other ways of mail merging?

 

[softwarescience]

Ok - here's the way i did it for another project.

*******

Step 1
'Create a text file from the recordset you created

Const DataFile = "C:\temp\Data.txt"

for each Fld in rRecords.Fields
  sHeaders = iif(sHeaders = "", "", sHeaders & "," ) & Fld.name
next 
FF = FreeFile
Open DataFile For Output As #FF
Print #FF, sHeaders

while not rRecords.Eof
   for each Fld in rRecords.Fields
      sData = iif(sData = "", "", sData & "," ) & Fld.value
   next
   Print #FF, sData
   sdata = ""

   rRecords.movenext
Wend
Close #FF

Step 2
'perform the mailmerge

Set Word = CreateObject("Word.Application.8") 'change to 9 for Word 2000
Word.Visible = True
Word.Documents.Add ("C:\Temp\MailMergeDoc.doc")
Word.activedocument.MailMerge.maindocumenttype = wdCatalog
Word.activedocument.MailMerge.opendatasource (DataFile)
Word.activedocument.MailMerge.Destination = wdSendToNewDocument
Word.activedocument.MailMerge.Execute
Word.activewindow.Previous.Activate
Word.activewindow.Close (0)
Word.showme

*******

This definately works and you shouldn't have to change your mailmerge template.

 

[99mel]

WOHOO that works! cheers!

is it possible to insert a page break after each record though? so each record is on a seperate page?


Also (i'm being picky now )
Do you know when you have a excel spreadsheet attached to a word document (mail merging) you get the navigation arrows at the top. Is it not possible to get this function using this method?

 

[HermanvLimbeek]

Hi 99mel,

Take a look at

http://www.planet-source-code.com

and search for "mailmerge" in "Visual Basic" then you'll find an excellent sample project which solves your problem (I think).

Herman :-Q