Mail Merge in MS Word? 
Author Message
 Mail Merge in MS Word?

I need help!

I have to somehow create a form letter in Word 2000 and merge it with
addresses from a SQL Server database.

I have already discovered that Word will only access data in Excel or
Access.

I have no idea where to begin with this project.  I know how to program
in VB.  I know how to create mail merges in Word.

What I don't know is how to write a program in VB that will export SQL
server data to Excel or Access, open up a Word document, and merge it
with the data in Access or Excel.

The online help is too top-level, not enough detail.  Can anyone
recommend any books on how to do this?  Does anyone have a clue as to
how to go about doing this?

Any help at all would be greatly appreciated.

Liz

Sent via Deja.com http://www.*-*-*.com/
Before you buy.



Sat, 08 Jun 2002 03:00:00 GMT  
 Mail Merge in MS Word?
Is it possible to set up an ODBC connection to the SQL database? Is there an
option for mail merge from an ODBC data source in Word? If not, can you
create a linked database in Access and use the Access database as your
source?
Quote:

> I need help!

> I have to somehow create a form letter in Word 2000 and merge it with
> addresses from a SQL Server database.

> I have already discovered that Word will only access data in Excel or
> Access.

> I have no idea where to begin with this project.  I know how to program
> in VB.  I know how to create mail merges in Word.

> What I don't know is how to write a program in VB that will export SQL
> server data to Excel or Access, open up a Word document, and merge it
> with the data in Access or Excel.

> The online help is too top-level, not enough detail.  Can anyone
> recommend any books on how to do this?  Does anyone have a clue as to
> how to go about doing this?

> Any help at all would be greatly appreciated.

> Liz

> Sent via Deja.com http://www.deja.com/
> Before you buy.



Sun, 09 Jun 2002 03:00:00 GMT  
 Mail Merge in MS Word?
Hi Steve,

From what I can gather, I believe the only external data sources that
Word can access are Excel and Access.  I believe it has to be a Jet
engine data source.

I'm getting a recordset from SQL Server, and I'm going to write it to
an Access table.  Then, I've got to figure out how to instantiate Word
objects to run the mail merge.  Any clues that you (or anyone reading
this) can give as to whether you think this will work and how to do it
would be greatly appreciated.

Thanks for responding,

Liz



Quote:
> Is it possible to set up an ODBC connection to the SQL database? Is
there an
> option for mail merge from an ODBC data source in Word? If not, can
you
> create a linked database in Access and use the Access database as your
> source?




Quote:
> > I need help!

> > I have to somehow create a form letter in Word 2000 and merge it
with
> > addresses from a SQL Server database.

> > I have already discovered that Word will only access data in Excel
or
> > Access.

> > I have no idea where to begin with this project.  I know how to
program
> > in VB.  I know how to create mail merges in Word.

> > What I don't know is how to write a program in VB that will export
SQL
> > server data to Excel or Access, open up a Word document, and merge
it
> > with the data in Access or Excel.

> > The online help is too top-level, not enough detail.  Can anyone
> > recommend any books on how to do this?  Does anyone have a clue as
to
> > how to go about doing this?

> > Any help at all would be greatly appreciated.

> > Liz

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.


Fri, 14 Jun 2002 03:00:00 GMT  
 Mail Merge in MS Word?

Quote:

> Hi Steve,

> From what I can gather, I believe the only external data sources that
> Word can access are Excel and Access.  I believe it has to be a Jet
> engine data source.

> I'm getting a recordset from SQL Server, and I'm going to write it to
> an Access table.  Then, I've got to figure out how to instantiate Word
> objects to run the mail merge.  Any clues that you (or anyone reading
> this) can give as to whether you think this will work and how to do it
> would be greatly appreciated.

> Thanks for responding,

> Liz



> > Is it possible to set up an ODBC connection to the SQL database? Is
> there an
> > option for mail merge from an ODBC data source in Word? If not, can
> you
> > create a linked database in Access and use the Access database as
your
> > source?



> > > I need help!

> > > I have to somehow create a form letter in Word 2000 and merge it
> with
> > > addresses from a SQL Server database.

> > > I have already discovered that Word will only access data in Excel
> or
> > > Access.

> > > I have no idea where to begin with this project.  I know how to
> program
> > > in VB.  I know how to create mail merges in Word.

> > > What I don't know is how to write a program in VB that will export
> SQL
> > > server data to Excel or Access, open up a Word document, and merge
> it
> > > with the data in Access or Excel.

> > > The online help is too top-level, not enough detail.  Can anyone
> > > recommend any books on how to do this?  Does anyone have a clue as
> to
> > > how to go about doing this?

> > > Any help at all would be greatly appreciated.

> > > Liz

> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.

> Sent via Deja.com http://www.deja.com/
> Before you buy.

Just for your information, Word can absolutely access ANY ODBC
compatible data source, including Oracle, Foxpro, Informix, etc...

You can do it programaticallybut you should look in Word's native
interface for a headstart.  When you create the mailmerge, step 2 is
defining the data source.  Use the Open Data Source option and then
click MS Query.  You can connect to any ODBC data source the MS Query.

Good luck!
--
-Jamie Weinstein, Senior Programmer Analyst
Morgan Stanley Dean Witter, Investment Banking Division
1585 Broadway, 34th Floor
New York, NY 10019

Sent via Deja.com http://www.deja.com/
Before you buy.



Fri, 14 Jun 2002 03:00:00 GMT  
 Mail Merge in MS Word?
Hi Jamie,

Thanks for the input on MS Query.  It didn't seem to have an option for
SQL Server, but we already had a DSN set up, so I could access the DB
through that.  However, MS Query didn't seem too flexible.  I've got
input parameters and date arithmetic and stuff in my query, so I think
I'm gonna stick with the Access scenario.  I'm already writing the data
out to an Access table and was able to merge it into the form letters.

Do you know (or know where I can find information on) how to automate
the mail merge from a VB program?  The on-line help doesn't seem to
have much on the subject.

How's NYC treating you?  That's where I'm originally from.  I escaped
winter and moved to Texas 16 years ago.

Liz

Sent via Deja.com http://www.deja.com/
Before you buy.



Sat, 15 Jun 2002 03:00:00 GMT  
 Mail Merge in MS Word?
Liz,

Go to the MS Download website and download ODBC 3.5.

http://www.microsoft.com/downloads/search.asp?

Select ODBC 3.0 from the Product name dropdown box, and select the
appropriate OS. When you install this, it should prompt you what drivers to
load. They will include the ODBC drivers for SQL, and you can then set up
either a System DSN or a USER DSN to the SQL database. I like this version
of ODBC because it allows you to test the connection once you set it up
without having to leave the wizard.

Hope this helps.

Quote:

> Hi Steve,

> From what I can gather, I believe the only external data sources that
> Word can access are Excel and Access.  I believe it has to be a Jet
> engine data source.

> I'm getting a recordset from SQL Server, and I'm going to write it to
> an Access table.  Then, I've got to figure out how to instantiate Word
> objects to run the mail merge.  Any clues that you (or anyone reading
> this) can give as to whether you think this will work and how to do it
> would be greatly appreciated.

> Thanks for responding,

> Liz



> > Is it possible to set up an ODBC connection to the SQL database? Is
> there an
> > option for mail merge from an ODBC data source in Word? If not, can
> you
> > create a linked database in Access and use the Access database as your
> > source?



> > > I need help!

> > > I have to somehow create a form letter in Word 2000 and merge it
> with
> > > addresses from a SQL Server database.

> > > I have already discovered that Word will only access data in Excel
> or
> > > Access.

> > > I have no idea where to begin with this project.  I know how to
> program
> > > in VB.  I know how to create mail merges in Word.

> > > What I don't know is how to write a program in VB that will export
> SQL
> > > server data to Excel or Access, open up a Word document, and merge
> it
> > > with the data in Access or Excel.

> > > The online help is too top-level, not enough detail.  Can anyone
> > > recommend any books on how to do this?  Does anyone have a clue as
> to
> > > how to go about doing this?

> > > Any help at all would be greatly appreciated.

> > > Liz

> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.

> Sent via Deja.com http://www.deja.com/
> Before you buy.



Sat, 15 Jun 2002 03:00:00 GMT  
 Mail Merge in MS Word?
Thanks for the suggestion.  We already have a system DSN set up for SQL
Server that I can get to work with MS Word.  The trouble is, that the
MS Query tool does not seem to be quite robust enough for me to get the
data exactly as I want it.  I need to use input parameters, and I
didn't see a way to do that with MS Query.  There's also date
arithmetic in one the where clauses.  So I'm back to getting the data I
want in VB and writing it out to an Access table.  I can manually merge
the form letters with the Access data in Word.  Now, all I have to do
is to figure out how to get VB to run the Word mailmerge and store the
results in a file.

So, I'm combing through the help files and the Object Browser to see
how these Word objects work.

If you know of any good resources for this information, I'm all ears.

Thanks,

Liz

Sent via Deja.com http://www.deja.com/
Before you buy.



Sun, 16 Jun 2002 03:00:00 GMT  
 Mail Merge in MS Word?
Below is a demonstration that I wrote a few months back to run from an
access form.
Whilst not a true mail merge I find this system to be more flexible as I can
control other aspects of the letter at the same time (i.e. formatting of
inserted text if such meets certain criteria)
 It should run just fine from VB.
All I did was set out a letter in Word with the control name (have done
similar using field name for a database) in <<>> characters such as
<<LoanAmount>> and it works fine.

The Line con.SetFocus is a dog.
It was necessary in Access as you can only read a control's content if it
has focus. This line would not be necessary in VB and can usually be got
around in Access by using RecordestClone to build a recordset for the merge.

Private Sub Command67_Click()
Dim AppWd As Word.Application
Dim wd As Word.Document
Dim con As Control
On Error GoTo Err_Command67_Click

Set AppWd = CreateObject("Word.Application.8")
Set wd = AppWd.Documents.Add("P:\Database\Loan Proposal.doc")

For Each con In Me.Controls
    If TypeOf con Is TextBox Then
        con.SetFocus
        With wd.Content.Find
            .Execute FindText:="<<" & con.Name & ">>",
ReplaceWith:=con.Text, _
            Replace:=wdReplaceAll
        End With
    End If
Next
AppWd.Visible = True

Exit_Command67_Click:
Set wd = Nothing
Set AppWd = Nothing
Exit Sub

Err_Command67_Click:
    MsgBox Err.Description
    Resume Exit_Command67_Click

End Sub

Quote:

> Hi Steve,

> From what I can gather, I believe the only external data sources that
> Word can access are Excel and Access.  I believe it has to be a Jet
> engine data source.

> I'm getting a recordset from SQL Server, and I'm going to write it to
> an Access table.  Then, I've got to figure out how to instantiate Word
> objects to run the mail merge.  Any clues that you (or anyone reading
> this) can give as to whether you think this will work and how to do it
> would be greatly appreciated.

> Thanks for responding,

> Liz



> > Is it possible to set up an ODBC connection to the SQL database? Is
> there an
> > option for mail merge from an ODBC data source in Word? If not, can
> you
> > create a linked database in Access and use the Access database as your
> > source?



> > > I need help!

> > > I have to somehow create a form letter in Word 2000 and merge it
> with
> > > addresses from a SQL Server database.

> > > I have already discovered that Word will only access data in Excel
> or
> > > Access.

> > > I have no idea where to begin with this project.  I know how to
> program
> > > in VB.  I know how to create mail merges in Word.

> > > What I don't know is how to write a program in VB that will export
> SQL
> > > server data to Excel or Access, open up a Word document, and merge
> it
> > > with the data in Access or Excel.

> > > The online help is too top-level, not enough detail.  Can anyone
> > > recommend any books on how to do this?  Does anyone have a clue as
> to
> > > how to go about doing this?

> > > Any help at all would be greatly appreciated.

> > > Liz

> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.

> Sent via Deja.com http://www.deja.com/
> Before you buy.



Wed, 19 Jun 2002 03:00:00 GMT  
 Mail Merge in MS Word?
I don't normally reply to my own posts but as this is only part of the job I
thought I better point out the rest.
The demo below works for a single record. For multiple records instantiate 2
word documents. create a range object including the entire text of the 2nd
doc, Run the range through a loop, merging it, then adding a page break to
the first doc and insert this into the first, move to the next record then
start the loop again.
I can't comment on the speed of this approach as we only use this method for
small runs (10 max).

MS have/had an office automation help file called Auto97.hlp available for
download which you will find useful for this type of work.


Quote:
> Below is a demonstration that I wrote a few months back to run from an
> access form.
> Whilst not a true mail merge I find this system to be more flexible as I
can
> control other aspects of the letter at the same time (i.e. formatting of
> inserted text if such meets certain criteria)
>  It should run just fine from VB.
> All I did was set out a letter in Word with the control name (have done
> similar using field name for a database) in <<>> characters such as
> <<LoanAmount>> and it works fine.

> The Line con.SetFocus is a dog.
> It was necessary in Access as you can only read a control's content if it
> has focus. This line would not be necessary in VB and can usually be got
> around in Access by using RecordestClone to build a recordset for the
merge.

> Private Sub Command67_Click()
> Dim AppWd As Word.Application
> Dim wd As Word.Document
> Dim con As Control
> On Error GoTo Err_Command67_Click

> Set AppWd = CreateObject("Word.Application.8")
> Set wd = AppWd.Documents.Add("P:\Database\Loan Proposal.doc")

> For Each con In Me.Controls
>     If TypeOf con Is TextBox Then
>         con.SetFocus
>         With wd.Content.Find
>             .Execute FindText:="<<" & con.Name & ">>",
> ReplaceWith:=con.Text, _
>             Replace:=wdReplaceAll
>         End With
>     End If
> Next
> AppWd.Visible = True

> Exit_Command67_Click:
> Set wd = Nothing
> Set AppWd = Nothing
> Exit Sub

> Err_Command67_Click:
>     MsgBox Err.Description
>     Resume Exit_Command67_Click

> End Sub



- Show quoted text -

Quote:
> > Hi Steve,

> > From what I can gather, I believe the only external data sources that
> > Word can access are Excel and Access.  I believe it has to be a Jet
> > engine data source.

> > I'm getting a recordset from SQL Server, and I'm going to write it to
> > an Access table.  Then, I've got to figure out how to instantiate Word
> > objects to run the mail merge.  Any clues that you (or anyone reading
> > this) can give as to whether you think this will work and how to do it
> > would be greatly appreciated.

> > Thanks for responding,

> > Liz



> > > Is it possible to set up an ODBC connection to the SQL database? Is
> > there an
> > > option for mail merge from an ODBC data source in Word? If not, can
> > you
> > > create a linked database in Access and use the Access database as your
> > > source?



> > > > I need help!

> > > > I have to somehow create a form letter in Word 2000 and merge it
> > with
> > > > addresses from a SQL Server database.

> > > > I have already discovered that Word will only access data in Excel
> > or
> > > > Access.

> > > > I have no idea where to begin with this project.  I know how to
> > program
> > > > in VB.  I know how to create mail merges in Word.

> > > > What I don't know is how to write a program in VB that will export
> > SQL
> > > > server data to Excel or Access, open up a Word document, and merge
> > it
> > > > with the data in Access or Excel.

> > > > The online help is too top-level, not enough detail.  Can anyone
> > > > recommend any books on how to do this?  Does anyone have a clue as
> > to
> > > > how to go about doing this?

> > > > Any help at all would be greatly appreciated.

> > > > Liz

> > > > Sent via Deja.com http://www.deja.com/
> > > > Before you buy.

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.



Thu, 20 Jun 2002 03:00:00 GMT  
 Mail Merge in MS Word?
OK, I've made progress on this mail merge thing.  I'm writing the
secondary merge data to an Access DB and I'm getting the primary doc to
merge with the secondary data.

Now, how do I save the merged file?

Here's basically how I've set up the Word object:

Dim objWord as Word.Document
set objWord = GetObject("filepath\primarydocname.doc", "Word.Document")
objWord.MailMerge.OpenDataSource
  Name:=  "filepath\database.mdb"
  LinkToSource:=True
  Connection:="TABLE tablename"
  SQLStatement:="Select * from [tablename]"

objWord.MailMerge.Execute

This works.  I get the desired merged file.  How do I save it?

Also, how do I get Word and Access to shut down?  Even though I set all
the objects to nothing, both applications stay open.

Any help would be much appreciated.

Thanks,

Liz

Sent via Deja.com http://www.deja.com/
Before you buy.



Tue, 25 Jun 2002 03:00:00 GMT  
 
 [ 10 post ] 

 Relevant Pages 

1. Mail merge to Ms Word

2. mail-merge MS Word and MS Access via ASP

3. Word Mail Merge Err - Word could not merge...

4. From VB6, call a MS WORD Mail-Merge feature

5. Word Mail merge field TO MS Access???

6. VB to MS Word Mail merge functionallity

7. Mail Merge: MS-Word/Access

8. MS Word 97 Mail Merge with Oracle Datasource?

9. SLOW MS Word Mail Merge with VB5

10. MS-Word mail merge

11. OLE Control and MS Word Mail Merge

12. MS Word 97 Mail Merge with Oracle Datasource?

 

 
Powered by phpBB® Forum Software