SQL > Access > Word Mail Merge

Question

SQL > Access > Word Mail Merge

Bah

SSC Enthusiast

Points: 153
More actions
October 25, 2005 at 3:18 am

#67920

Hello,
I have an Access setup with linked SQL tables on a remote server.
I need to be able to provide a mail merge function to users. I can do this for a single record quite readily using Word bookmarks. However what I really need is to be able to merge from a recordset rather than a single record.
The database stores contacts. The idea behind this mail merge is to create envelope labels.
I have the word template setup already. The Access Report Wizard for mail merging labels didn't suffice.
Besides just this mail merge understanding the process to merge a recordset with Word would help me to add other automation functionality also.
Thanks for any help you can offer me.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

MTSmith SSC-Addicted Points: 449 More actions · Answer 1

You did not say what version of MS Office you're using, so my example is coming from MS Office XP:

1) Create a simple SELECT query from your linked table/view. Arrange this query with the fields you need and in the order you want them to appear on the Word document

2) Left click once to highlight your new SELECT query - look just below the word "HELP" in the file menu, you should see a blue letter "W" with an envelope icon. This is a drop down list of Office links. Choose "MERGE IT WITH MS WORD"

3) Word should open - on the right hand side, Word XP will display another help wizard on how to create your mail merge and give you the choice of letter; labels; envelopes; etc.

Good luck!

[mts]

Bah SSC Enthusiast Points: 153 More actions · Answer 2

Hi,

Thanks for your reply.

I was hoping to offer my less technically aware users a more simple method of achieving this. Ideally by just hitting a button - answering a question to complete the query then the recordset should be merged into word.

I appreciate this will need vba however this is the result I am aiming to achieve.

WSquared SSCrazy Points: 2393 More actions · Answer 3

Hi,

The way that I do it is to write the recordset to a file, and then use the file as the datasource for the mail merge, here's the function, you will need to add a reference to ScriptingRuntime in order to use it

Function RecordsetToTextFile(strFileName As String, rs As ADODB.Recordset)

Dim fs As FileSystemObject

Dim tsOutput As TextStream

Dim fld As ADODB.Field

Dim rec As ADODB.Record

Dim strOutput As String

Set fs = New FileSystemObject

Set tsOutput = fs.CreateTextFile(strFileName, True)

For Each fld In rs.Fields

strOutput = strOutput & ";" & Chr(34) & fld.Name & Chr(34)

Next fld

tsOutput.WriteLine (Mid(strOutput, 2))

Do While Not rs.EOF

strOutput = ""

For Each fld In rs.Fields

strOutput = strOutput & ";" & Chr(34) & Trim(CStr(Nz(fld.Value, ""))) & Chr(34)

Next fld

tsOutput.WriteLine (Mid(strOutput, 2))

rs.MoveNext

Loop

tsOutput.Close

End Function

Bah SSC Enthusiast Points: 153 More actions · Answer 4

WSquared,

That looks to be exactly what I need - thank you for your reply.

How would I assign that function to a button on an access form ?

When I try the following it bails with an error:

Private Sub Command525_Click()

Dim FileName As String

FileName = "C:\test.txt"

RecordsetToTextFile (FileName)

End Sub

Compile Error: Argument not optional

WSquared SSCrazy Points: 2393 More actions · Answer 5

Hi Bah,

You need to pass a recordset to the function.

So to do this you need to create an adodb recordset upfront, and pass that through as the second argument.