Word 2000 Mail Merge 5

[Dutt]

I am trying to set up a mail merge document that when I open the word doc it will automatically do the mail merge in that particular file not open another. The merge would get the data from an outside ODBC text file. Anyone know how to set this up?

Thanks

Dutt

 

[Telsa]

Here is a sample of what I did. Bet you can figure out how to make it work for you! You will need to add the Word Object via Tools-References. I found the info to develop this in Access 2000 Beginning VBA by Wrox. Between that and the Word 2000 VBA book by Wrox, I have successfully made an easy to update Word template for Access.

Here it is!

Generating Word Doc from Access without using Merge

WORD TEMPLATE
Create your Word document with a template format. Save it as a template file (.dot). Use bookmarks to mark the place you want the data to be pulled in. You can have as many bookmarks as you want. If you require the data to be pulled into tables, don't create tables in Word, but let Access VBA create the tables for you.

ACCESS DATABASE
Set up queries showing the fields you want to transfer to the Word document. You may need to set up more than one query. If you have to do that, then you will need to set up each as a recordset in the code with its own SQL string. The idea of the SQL string is to narrow down the records in the recordset to the exact info you need.

Assuming only two queries were made, code as follows:

In a module, key in a Public variable to be shared in database


Option Compare Database
Option Explicit

' location of the documents and templates -
' Where will Access find the Word Template?
Public Const m_strDIR As String = "d:\database\"
Public Const m_strTEMPLATE As String = "submittalcd.dot"

' set up objects for use and Public variables to be shared in database
Private m_objWord As Word.Application
Private m_objDoc As Word.Document
Public strProdNum As String

In the Forms Button for starting the event…

Create SQL statements based on the values of the active record (i.e., prodnum)

Click event:

Dim db As DAO.Database
Dim recSubmittal As DAO.Recordset
Dim recSubmittal2 As DAO.Recordset
Dim strSQL As String
Dim strSQL2 As String


' Capture the field whose value will narrow your recordset down
strProdNum = Me.PartsID

strSQL = "SELECT * FROM qrySubmittalBase WHERE ProdNum= '" & strProdnum & "';"
Set db = CurrentDb()
Set recSubmittal = db.OpenRecordset(strSQL)

StrSQL2 = "SELECT * FROM qrySubmittalDetail WHERE ProdNum= '" & strProdnum & "';"
Set db = CurrentDb()
Set recSubmittal2 = db.OpenRecordset(strSQL2)



' This CreateSubmittal sub is created in the module
CreateSubmittal recSubmittal, recSubmittal2

Back in the module, create the above sub (remember, this is referenced in the Forms click procedure)
This can be a little confusing here… the recSubmit is capturing the recSubmittal and the recSubmit2 is capturing the recSubmittal2 recordsets.

Public Sub CreateSubmittal(recSubmit As DAO.Recordset, recSubmit2 As DAO.Recordset)

Set m_objWord = New Word.Application
Set m_objDoc = m_objWord.Documents.Add(m_strDIR & m_strTEMPLATE)

m_objWord.Visible = True

InsertTextAtBookmark "basepart", recSubmit("base&quot
InsertTextAtBookmark "title", recSubmit("title-version&quot
InsertTextAtBookmark "bundledparts", recSubmit("bundledparts&quot
InsertTextAtBookmark "ReleaseDate", recSubmit("ReleaseDate&quot
InsertTextAtBookmark "version", recSubmit("Version&quot


' Generate the table data
InsertSummaryTable recSubmit2


Set m_objDoc = Nothing
Set m_objWord = Nothing


End Sub

Private Sub InsertTextAtBookmark(strBkmk As String, varText As Variant)
' This finds the bookmarks in the Word template to place the data.
m_objDoc.Bookmarks(strBkmk).Select
m_objWord.Selection.Text = varText & ""

End Sub

Private Sub InsertSummaryTable(recR As DAO.Recordset)
' This pulls in the data for a table then highlights the data
' and creates a table in the Word document at a bookmark location
' for each field you want in the column of the table, have tabs
' surround it. Items in quotes are field names from the query/recordset
' If you need to have a blank column, just place vbTab in twice
On Error GoTo No_Record_Err
Dim strTable As String
Dim objTable As Word.Table

recR.MoveFirst
strTable = ""
While Not recR.EOF
strTable = strTable & vbTab & recR("discontinuedpart&quot & vbTab & vbTab & recR("5x5No&quot & vbCr
recR.MoveNext
Wend

InsertTextAtBookmark "DiscPart", strTable
Set objTable = m_objWord.Selection.ConvertToTable(Separator:=vbTab)

objTable.Select
objTable.Columns(1).Width = InchesToPoints(1.51)
objTable.Columns(2).Width = InchesToPoints(2.56)
objTable.Columns(3).Width = InchesToPoints(1.44)
objTable.Columns(4).Width = InchesToPoints(2.14)

Set objTable = Nothing
No_Record_Err:
Exit Sub

End Sub
Mary

Rule 1: Don't sweat the small stuff.
Rule 2: EVERYTHING is small stuff!! X-)

 

[Dreamboat]

Nice one, Telsa! You get a star. I'm stealing your code, girl! LOL

http://www.PiersonTech.com

dreamboat@nni.com
Brainbench MVP for Microsoft Word

 

[Antoiny]

Yea Tes,

I was just going to spend a few hrs figuring it all out but your post saved me a getting to know Word better!

I think I'll have to scour for questions to answer when I get my own net connection again and hopefully return the favour