Possible to control MailMerge programmatically?

[Robert]

I have an existing form using an WordXP template that is activated
from a VisualBasic application. The app sends a connection string and
ID number to the template which then takes over and uses a series of
VBA procedures to pull in recordsets from a number of tables in a
database, locate corresponding bookmarks and write out the data in the
recordsets to the document.

This form works well but was designed to use only one ID at a time,
fill out the form, save it and close.

I am trying now to figure out if there is a way to have this process
repeat for an array of IDs and create a single document in a manner
similar to a mail merge so that it can be saved and/or sent to the
printer in a single operation.

I don't know much about mail merge but I don't think it would work
since there are multiple calls to the database. I have seen
suggestions to others that VBA can manually control a mail merge step
by step (page by page?) but have never found any code for this and am
not sure if it's even possible in the way I would need.

I could obviously have VB send one ID and create one document at a
time but as I stated above I'd like to create them all at once from an
array.

I don't know if it is possible from within Word to create new
instances of a particular document successively added on at the end as
in a mail merge without actually using a mail merge.

I may be taking a completely wrong approach to this problem. Any ideas
would be greatly appreciated.

Robert

 

[Cindy Meister -WordMVP-]

Hi Robert,

Mail merge can't work for this for the simple reason that mail merge
requires a data source saved to a file.

I am trying now to figure out if there is a way to have this process
repeat for an array of IDs and create a single document in a manner
similar to a mail merge so that it can be saved and/or sent to the
printer in a single operation.

What you'd basically need to do is use InsertFile for each "repeat",
until you've processed all the records.

I have an existing form using an WordXP template that is activated
from a VisualBasic application. The app sends a connection string and
ID number to the template which then takes over and uses a series of
VBA procedures to pull in recordsets from a number of tables in a
database, locate corresponding bookmarks and write out the data in the
recordsets to the document.

This form works well but was designed to use only one ID at a time,
fill out the form, save it and close.

I am trying now to figure out if there is a way to have this process
repeat for an array of IDs and create a single document in a manner
similar to a mail merge so that it can be saved and/or sent to the
printer in a single operation.

I don't know much about mail merge but I don't think it would work
since there are multiple calls to the database. I have seen
suggestions to others that VBA can manually control a mail merge step
by step (page by page?) but have never found any code for this and am
not sure if it's even possible in the way I would need.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any follow question
or reply in the newsgroup and not by e-mail

 

[Bobby Hopper]

What I did was create a series of docs then merge them
into one big one like this.

Hope it helps.

Bobby
-----------------------------------------------------------
----------------------

Private Sub MergeWordDocs(objWord As Word.Application, _
strPath, _
AnnouncementNumber, _
objScript As Scripting.FileSystemObject, _
objFolder As Scripting.Folder, _
objFile As Scripting.File, _
WordDocPathName, _
WordDocFileName, _
ApplicantID, _
AnnouncementID, _
FirstName, _
LastName, _
objWordDoc As of612DB.WordDoc)

Dim retval&, i&, j&, z, aField, Attachments(), strSteps
Dim PowerPlusWaterMarkObject1
Const WordDocType = "P"


On Error GoTo errorHandler

'use Scripting to obtain filenames of Word docs
strSteps = strSteps & "objFolder" & vbCrLf
With objScript
' Get folder.
Set objFolder = objScript.GetFolder(strPath)
' Loop through Files collection, adding to
dictionary.
i = 0
For Each objFile In objFolder.Files
If InStr(objFile.Name, "OF612") Then 'ignore
anything with OF612
Else
' If InStr(objFile.Name, AnnouncementNumber
& ".doc") Or _
' InStr(objFile.Name,
AnnouncementNumber & "Continue") Then 'just store
filenames for this AnnouncementNumber
If InStr(objFile.Name, ApplicantID &
AnnouncementID & ".doc") Or _
InStr(objFile.Name, ApplicantID &
AnnouncementID & "Continue") Then 'just store filenames
for this AnnouncementNumber
ReDim Preserve Attachments(1,
i) 'Attachments array contains filenames for email
Attachments(0, i) = objFile.Name 'put
this doc name in array for use by email object
Attachments(1, i) = objFile.Path 'put
this path and doc name in array for use by email object
i = i + 1
End If
End If
Next
End With

strSteps = strSteps & "Open Doc" & vbCrLf
'merge all the of612 docs into one
With objWord
.Documents.Open FileName:=Attachments(1,
0) 'strPath & "\" & AnnouncementNumber & ".doc"
strSteps = strSteps & "Add Docs" & vbCrLf
With .Selection
For j = 1 To UBound(Attachments, 2)
If objWord.ActiveDocument.FullName =
Attachments(1, j) Then
Else
.EndKey Unit:=wdStory
.InsertBreak Type:=wdPageBreak
.InsertFile FileName:=Attachments(1,
j), _
Range:="", _
ConfirmConversions:=False, _
link:=False, _
Attachment:=False
End If
Next
End With

WordDocPathName = strPath & "\"
WordDocFileName = "OF612" & ApplicantID &
AnnouncementID & ".doc"

strSteps = strSteps & "Save Doc" & vbCrLf
With .ActiveDocument
'Insert Watermark
.Sections(1).Range.Select
.ActiveWindow.ActivePane.View.SeekView =
wdSeekCurrentPageHeader
With Selection
.HeaderFooter.Shapes.AddTextEffect
(PowerPlusWaterMarkObject1, _
"DRAFT", "Arial", 1, False, False, 0,
0).Select
With .ShapeRange
.Name = "PowerPlusWaterMarkObject1"
.TextEffect.NormalizedHeight = False
.Line.Visible = False
.Fill.Visible = True
.Fill.Solid
.Fill.ForeColor.RGB = RGB(192, 192,
192)
.Fill.Transparency = 0.5
.Rotation = 315
.LockAspectRatio = True
.Height = InchesToPoints(2.22)
.Width = InchesToPoints(5.54)
.WrapFormat.AllowOverlap = True
.WrapFormat.Side = wdWrapNone
.WrapFormat.Type = 3
.RelativeHorizontalPosition = _
wdRelativeVerticalPositionMargin
.RelativeVerticalPosition = _
wdRelativeVerticalPositionMargin
.Left = wdShapeCenter
.Top = wdShapeCenter
End With
End With
.ActiveWindow.ActivePane.View.SeekView =
wdSeekMainDocument
.PageSetup.BottomMargin = InchesToPoints(0.5)
.Protect Password:="h]redgun", NoReset:=True,
Type:=wdAllowOnlyFormFields
'present Word Doc in Event Log
App.logEvent vbNewLine & "Saved " &
WordDocPathName & WordDocFileName
.SaveAs FileName:=WordDocPathName &
WordDocFileName, _
FileFormat:=wdFormatDocument, _
LockComments:=False, _
AddToRecentFiles:=True, _
ReadOnlyRecommended:=False, _
EmbedTrueTypeFonts:=False, _
SaveNativePictureFormat:=False, _
SaveFormsData:=False, _
SaveAsAOCELetter:=False
.Close SaveChanges:=wdDoNotSaveChanges
End With
End With
End Sub