Alternative Mailmerge In Word Using Access Database That Does Not Use Words Mailmerge.

This is useful when doing a mail merge where you may require a subsequent mail merge. For instance, you may have a large list of senders and recipients. Creating a normal mail merge based on the senders would mean that subsequent mail merge to the recipients cannot be achieved (in Word 97). The method I have used creates text entries based on the selections of the template user and has no ties to the data source, leaving the user free to perform a further mail merge as required.

---------
STEP 1
---------

The first step is to download two files from Microsoft and install them on each machine. These can be found on the URLÆs below.

* DCOM (Distributed Component Object Model)
http://www.microsoft.com/com/resources/downloads.asp

* MDAC (Microsoft Data Access Components)
http://www.microsoft.com/data/download.htm

If you do not install the same version then you will receive a ôCompile Error: CanÆt Find Project Or Libraryö

---------
STEP 2
---------
The second step is to create the database table in Access. No other coding or setup is required in Access.

---------
STEP 3
---------

The third step is to use the code below in Word:

Put This code in the form.
-------------------------------

' For the ADODB object to work you must set a reference
' Select TOOL, REFERENCES from the visual basic menu bar
' Select the MICROSOFT ACTIVE X DATA OBJECTS 2.1 LIBRARY

' Once set within the template they do not need setting again
Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset

Now you need to create an array to store the details

Private Sub UserForm_Activate()

Dim nRowCount As Integer
Dim nColCount As Integer
Dim sArray()
Dim ArrayRowCounter As Integer
Dim ArrayColCounter As Integer


' Create a new ADO connection
Set oConn = CreateObject("ADODB.Connection")
' Open our connect (NOTE the OLEDB.4.0 depends on what version of DCOM is installed)
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFileName & ";Persist Security Info=False"
' Execute a SQL statement to retrieve the information
Set oRS = oConn.Execute(" SELECT SenderName, ID, SenderTitle, SenderDivision, SenderAddress1, SenderAddress2, SenderAddress3, SenderAddress4, SenderAddress5 FROM Test")

' Store column count to variable
nColCount = oRS.Fields.count

' move to first record in file
oRS.MoveFirst

' count the number of columns to size the array
nRowCount = 0
Do
nRowCount = nRowCount + 1
oRS.MoveNext
Loop Until oRS.EOF = True

' Build the array x rows long and y columns wide
ReDim sArray(nRowCount, nColCount)

' Move to the top of the recordset
oRS.MoveFirst

' Put information into the array
For ArrayRowCounter = 1 To nRowCount
For ArrayColCounter = 0 To (nColCount - 1)
' Put the value of the field in the array cell
sArray(ArrayRowCounter, ArrayColCounter) = oRS.Fields(ArrayColCounter)
Next ArrayColCounter
' Move to the next recordset
oRS.MoveNext
Next

' Specify the number and size of columns for the dropdown
cboSender.ColumnCount = nColCount
' The size of all the columns, except the name, are hidden by having a 0 width
cboSender.ColumnWidths = "2.3in;0in;0in;0in;0in;0in;0in;0in;0in;0in"
' Populate the combobox with the array
cboSender.List() = sArray

' close the recordset
oRS.Close

End Sub

---------
STEP 4
---------

The final stage is to populate the document with the details, depending on the selection. Put the code in below:

Private Sub cmdPopulate_Click()

If IsNull(cboSender.Value) Then
MsgBox "You must select a person from the list before the details can be copied to the letter"
Else
If Trim(cboSender.Value) = "" Or IsNull(cboSender.Value) = True Then
MsgBox "You must select a person from the list before the details can be copied to the letter"
Else
'Now create a new recordset to search for the value

' Create a new ADO connection
Set oConn = CreateObject("ADODB.Connection")
' Open our connect
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFileName & ";Persist Security Info=False"
' Execute a SQL statement to retrieve the information
Dim sqlstr As String
sqlstr = " SELECT SenderName, ID, SenderTitle, SenderDivision, SenderAddress1, SenderAddress2, SenderAddress3, SenderAddress4, SenderAddress5 FROM Test WHERE ID=" + Str(cboSender.Value) + ""
Set oRS = oConn.Execute(sqlstr)

' Replace the bookmark values
ActiveDocument.Bookmarks("SenderName").Select
Selection.InsertAfter oRS.Fields("SenderName")
ActiveDocument.Bookmarks("SenderName2").Select
Selection.InsertAfter oRS.Fields("SenderName")
ActiveDocument.Bookmarks("Sendertitle").Select
Selection.InsertAfter oRS.Fields("SenderTitle")
ActiveDocument.Bookmarks("Sendertitle2").Select
Selection.InsertAfter oRS.Fields("SenderTitle")
ActiveDocument.Bookmarks("SenderDivision").Select
Selection.InsertAfter oRS.Fields("SenderDivision")
ActiveDocument.Bookmarks("SenderAddress1").Select
Selection.InsertAfter oRS.Fields("SenderAddress1")
ActiveDocument.Bookmarks("SenderAddress2").Select
Selection.InsertAfter oRS.Fields("SenderAddress2")
ActiveDocument.Bookmarks("SenderAddress3").Select
Selection.InsertAfter oRS.Fields("SenderAddress3")
ActiveDocument.Bookmarks("SenderAddress4").Select
Selection.InsertAfter oRS.Fields("SenderAddress4")
ActiveDocument.Bookmarks("SenderAddress5").Select
Selection.InsertAfter oRS.Fields("SenderAddress5")

' put in the non database information

' close the recordset
oRS.Close

' unload the form
Unload frmSender

End If
End If

End Sub