[Jerome_Ball]

I am using Word 2003. I need to create a Template and save this template.
What I need to do is as follows:

1. Data Source is in Excel 2003.

2. Monthly worksheets, that I need to send to parent that have an amount
owed to
the company.

3. Need a way to have the user just run the Mail Merge process to create &
print
the letters that need to be sent.

4. Need for the user to select the month they wish to report. Each month
correspond to a worksheet for the Workbook. Workbook is called
School_1.
Worksheets are "JAN", "FEB", etc.

5. The selection criteria is Column C must have "PARENT" and Column D must
be
greater than 0.00. Need to be able to have template set up with
C1:C200 = "PARENT" and D1200 0.00.

6. When I used the wizard, I keep having to changing the field code of the
merge
field, so that dollar amounts print properly from Column D.

7. What I would like to have it do is have the user enter the month they
wish to
report against and then have the mail merge job run, display the output
to
allow the user to make any mods they may want and then print.

This may be easy, yet I need expert help to do this. I would like to
automate as much as possible to keep them from making mistakes and contacting
me. Thanx in advance for your help and asistance in this process. You could
also send any directly to .

Jerome Ball

[Peter Jamieson]

If you are starting from a .dot template, then I suggest you do the
following:
a. create and save the template
b. set up the mail merge document type
c. connect to the data source and lay out your fields as you need them
d. set up the destination (for a directory, it can only be a "new document"
anyway)
e. in the Word VBA editor's immediate window, type
activedocument.mailmerge.datasource.close

In word 2003, that should close the data source without losing info. about
your document type or destination.

f. put the following Autonew macro code in a new module in your template
g. save and close your template
h. test by using file-new, selecting the template, and providing a month
abbreviation.

Some notes:
- It's just an outline - I don't claim it all works, and you will have to
change at least some of it to get it to work at all.
- ask again about how to do the bits you are unfamiliar with. But
http://word.mvps.org is a useful resource about using macros
- if you specify sheet "feb" and there is no sheet feb, you will see an
ODBC dialog box that your users should probably cancel out of. There may be
ways around this, but VBA error trapping will not do it.
- the first row in each excel sheet must contain column names.
- the data returned from Excel will not necessarily match what was entered
unless the sheet is very simple. See http://tips.pjmsn.me.uk/t0003.htm for
details if you need them.


Sub autonew()
'
'
Dim bQuit As Boolean
Dim bRetry As Boolean
Dim objMMMD As Word.Document
Dim strMmm As String
Dim strFullName As String
Dim strSQL As String

strMmm = "mmm"
bRetry = True
While bRetry
strMmm = InputBox("Enter the 3-letter month abbreviation, e.g. e.g. jan,
or blank to quit. (Or press escape, etc.)", "Select the month", "mmm")
strMmm = LCase(Trim(strMmm))
Select Case strMmm
Case ""
MsgBox "You quit, or entered a blank month abbreviation - no merge
will be performed"
bQuit = True
bRetry = False
Case "jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep",
"oct", "nov", "dec"
' OK
bQuit = False
bRetry = False
Case Else
MsgBox "The month abbreviation needs to be one of jan, feb, mar,
etc.,"
End Select
Wend
If Not bQuit Then
On Error Resume Next
Set objMMMD = ActiveDocument

With objMMMD.MailMerge
' You need the full path name to the data source here...
strFullName = "C:\Documents and Settings\pjj.DOMAINX\My Documents\My
Data Sources\school_1.xls"
' You need to substitute your own column header names for "ColumnC" and
"ColumnD"
strSQL = " SELECT * FROM [" & strMmm & "$]" & _
" WHERE ucase(ColumnC) = 'PARENT'" & _
" AND cdbl(ColumnD) 0"
.OpenDataSource _
Name:=strFullName, _
sqlstatement:=strSQL
If Err.Number 0 Then
MsgBox "Could not get the data from the spreadsheet. the merge will
not be run." & _
"( Error number " & CStr(Err.Number) & ", " & Err.Description & ")"
Err.Clear
Else
On Error GoTo 0
.DataSource.FirstRecord = wdDefaultFirstRecord
.DataSource.LastRecord = wdDefaultLastRecord
.Execute Pause:=False
.DataSource.Close
' If you are merging to a new document, that becomes the
Activedocument
' and you can close the mail merge main document
objMMMD.Close savechanges:=False
Set objMMMD = Nothing
End If
End With
End If
End Sub


--
Peter Jamieson
http://tips.pjmsn.me.uk

"Jerome_Ball" wrote in message
...
I am using Word 2003. I need to create a Template and save this
template.
What I need to do is as follows:

1. Data Source is in Excel 2003.

2. Monthly worksheets, that I need to send to parent that have an amount
owed to
the company.

3. Need a way to have the user just run the Mail Merge process to create
&
print
the letters that need to be sent.

4. Need for the user to select the month they wish to report. Each month
correspond to a worksheet for the Workbook. Workbook is called
School_1.
Worksheets are "JAN", "FEB", etc.

5. The selection criteria is Column C must have "PARENT" and Column D
must
be
greater than 0.00. Need to be able to have template set up with
C1:C200 = "PARENT" and D1200 0.00.

6. When I used the wizard, I keep having to changing the field code of
the
merge
field, so that dollar amounts print properly from Column D.

7. What I would like to have it do is have the user enter the month they
wish to
report against and then have the mail merge job run, display the
output
to
allow the user to make any mods they may want and then print.

This may be easy, yet I need expert help to do this. I would like to
automate as much as possible to keep them from making mistakes and
contacting
me. Thanx in advance for your help and asistance in this process. You
could
also send any directly to .

Jerome Ball

[Jerome_Ball[_2_]]

Peter,

I am trying this and getting a little confused. If you have some time, I
could use some guidance. I have also checked out the links you sent,
but could not find information from your steps, so I am coming back to
you.

a. create and save the template
DONE

b. set up the mail merge document type
1. I go to tools-letters&mailing-Mail Merge-
2. Select document Type.
* Letters
3. Select Starting Document
* Start From a template and I get the template I created.

c. connect to the data source and lay out your fields as you need them
1. Select recipients
* Connect to my data source (Excel) and build the criteria.

d. set up the destination (for a directory, it can only be a "new document"
anyway)
1. I am lost here???????

e. in the Word VBA editor's immediate window, type
1. What I did was being up the Visual Basic Editor. I am unsure about
the immediate window and where it is located. I tried looking
around,
but I cannot find it.

f. put the following Autonew macro code in a new module in your template
1. I am taking that to be the example code you detailed. Yet, I do not
know where to create a new module in the template and what you mean
by Autonew macro code.

g. save and close your template
1. I think I would just close the Visual Basic Editor and then same this
as a template again.

- the first row in each excel sheet must contain column names.
1. Would you suggest I do the following to meet your above statement.
Cell ROW 1 COL C with the heading TYPE_SEL
Cell ROW 1 COL D with the heading AMT_DUE
and then name any other column I plan to use in the merge and
the rest of them could be left blank?

Thanx in advance for your assistance in this matter. I look
forward to hearing back.

"Peter Jamieson" wrote:

If you are starting from a .dot template, then I suggest you do the
following:
a. create and save the template
b. set up the mail merge document type
c. connect to the data source and lay out your fields as you need them
d. set up the destination (for a directory, it can only be a "new document"
anyway)
e. in the Word VBA editor's immediate window, type
activedocument.mailmerge.datasource.close

In word 2003, that should close the data source without losing info. about
your document type or destination.

f. put the following Autonew macro code in a new module in your template
g. save and close your template
h. test by using file-new, selecting the template, and providing a month
abbreviation.

Some notes:
- It's just an outline - I don't claim it all works, and you will have to
change at least some of it to get it to work at all.
- ask again about how to do the bits you are unfamiliar with. But
http://word.mvps.org is a useful resource about using macros
- if you specify sheet "feb" and there is no sheet feb, you will see an
ODBC dialog box that your users should probably cancel out of. There may be
ways around this, but VBA error trapping will not do it.
- the first row in each excel sheet must contain column names.
- the data returned from Excel will not necessarily match what was entered
unless the sheet is very simple. See http://tips.pjmsn.me.uk/t0003.htm for
details if you need them.


Sub autonew()
'
'
Dim bQuit As Boolean
Dim bRetry As Boolean
Dim objMMMD As Word.Document
Dim strMmm As String
Dim strFullName As String
Dim strSQL As String

strMmm = "mmm"
bRetry = True
While bRetry
strMmm = InputBox("Enter the 3-letter month abbreviation, e.g. e.g. jan,
or blank to quit. (Or press escape, etc.)", "Select the month", "mmm")
strMmm = LCase(Trim(strMmm))
Select Case strMmm
Case ""
MsgBox "You quit, or entered a blank month abbreviation - no merge
will be performed"
bQuit = True
bRetry = False
Case "jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep",
"oct", "nov", "dec"
' OK
bQuit = False
bRetry = False
Case Else
MsgBox "The month abbreviation needs to be one of jan, feb, mar,
etc.,"
End Select
Wend
If Not bQuit Then
On Error Resume Next
Set objMMMD = ActiveDocument

With objMMMD.MailMerge
' You need the full path name to the data source here...
strFullName = "C:\Documents and Settings\pjj.DOMAINX\My Documents\My
Data Sources\school_1.xls"
' You need to substitute your own column header names for "ColumnC" and
"ColumnD"
strSQL = " SELECT * FROM [" & strMmm & "$]" & _
" WHERE ucase(ColumnC) = 'PARENT'" & _
" AND cdbl(ColumnD) 0"
.OpenDataSource _
Name:=strFullName, _
sqlstatement:=strSQL
If Err.Number 0 Then
MsgBox "Could not get the data from the spreadsheet. the merge will
not be run." & _
"( Error number " & CStr(Err.Number) & ", " & Err.Description & ")"
Err.Clear
Else
On Error GoTo 0
.DataSource.FirstRecord = wdDefaultFirstRecord
.DataSource.LastRecord = wdDefaultLastRecord
.Execute Pause:=False
.DataSource.Close
' If you are merging to a new document, that becomes the
Activedocument
' and you can close the mail merge main document
objMMMD.Close savechanges:=False
Set objMMMD = Nothing
End If
End With
End If
End Sub


--
Peter Jamieson
http://tips.pjmsn.me.uk

"Jerome_Ball" wrote in message
...
I am using Word 2003. I need to create a Template and save this
template.
What I need to do is as follows:

1. Data Source is in Excel 2003.

2. Monthly worksheets, that I need to send to parent that have an amount
owed to
the company.

3. Need a way to have the user just run the Mail Merge process to create
&
print
the letters that need to be sent.

4. Need for the user to select the month they wish to report. Each month
correspond to a worksheet for the Workbook. Workbook is called
School_1.
Worksheets are "JAN", "FEB", etc.

5. The selection criteria is Column C must have "PARENT" and Column D
must
be
greater than 0.00. Need to be able to have template set up with
C1:C200 = "PARENT" and D1200 0.00.

6. When I used the wizard, I keep having to changing the field code of
the
merge
field, so that dollar amounts print properly from Column D.

7. What I would like to have it do is have the user enter the month they
wish to
report against and then have the mail merge job run, display the
output
to
allow the user to make any mods they may want and then print.

This may be easy, yet I need expert help to do this. I would like to
automate as much as possible to keep them from making mistakes and
contacting
me. Thanx in advance for your help and asistance in this process. You
could
also send any directly to .

Jerome Ball

[Doug Robbins - Word MVP]

The comment only applies to a Directory type mail merge main document. If
you want the merged documents to be printed straight away, set the Merge to
Printer as the destination. If you want to have the merge executed to a
document so that you can check it or for some other purpose, then select
Merge to New Document as the destination.

You get to the Immediate Window in the VBE from the VBE View menu. Any
command that you enter in that window is executed immediately that you press
the enter key after entering the command.

You would need to modify the code in

strSQL = " SELECT * FROM [" & strMmm & "$]" & _
" WHERE ucase(ColumnC) = 'PARENT'" & _
" AND cdbl(ColumnD) 0"

so that it refers to the field names that you are using.
--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Jerome_Ball" wrote in message
...
Peter,

I am trying this and getting a little confused. If you have some time, I
could use some guidance. I have also checked out the links you sent,
but could not find information from your steps, so I am coming back to
you.

a. create and save the template
DONE

b. set up the mail merge document type
1. I go to tools-letters&mailing-Mail Merge-
2. Select document Type.
* Letters
3. Select Starting Document
* Start From a template and I get the template I created.

c. connect to the data source and lay out your fields as you need them
1. Select recipients
* Connect to my data source (Excel) and build the criteria.

d. set up the destination (for a directory, it can only be a "new
document"
anyway)
1. I am lost here???????

e. in the Word VBA editor's immediate window, type
1. What I did was being up the Visual Basic Editor. I am unsure
about
the immediate window and where it is located. I tried looking
around,
but I cannot find it.

f. put the following Autonew macro code in a new module in your
template
1. I am taking that to be the example code you detailed. Yet, I do
not
know where to create a new module in the template and what you
mean
by Autonew macro code.

g. save and close your template
1. I think I would just close the Visual Basic Editor and then same
this
as a template again.

- the first row in each excel sheet must contain column names.
1. Would you suggest I do the following to meet your above
statement.
Cell ROW 1 COL C with the heading TYPE_SEL
Cell ROW 1 COL D with the heading AMT_DUE
and then name any other column I plan to use in the merge and
the rest of them could be left blank?

Thanx in advance for your assistance in this matter. I look
forward to hearing back.

"Peter Jamieson" wrote:

If you are starting from a .dot template, then I suggest you do the
following:
a. create and save the template
b. set up the mail merge document type
c. connect to the data source and lay out your fields as you need them
d. set up the destination (for a directory, it can only be a "new
document"
anyway)
e. in the Word VBA editor's immediate window, type
activedocument.mailmerge.datasource.close

In word 2003, that should close the data source without losing info.
about
your document type or destination.

f. put the following Autonew macro code in a new module in your template
g. save and close your template
h. test by using file-new, selecting the template, and providing a
month
abbreviation.

Some notes:
- It's just an outline - I don't claim it all works, and you will have
to
change at least some of it to get it to work at all.
- ask again about how to do the bits you are unfamiliar with. But
http://word.mvps.org is a useful resource about using macros
- if you specify sheet "feb" and there is no sheet feb, you will see an
ODBC dialog box that your users should probably cancel out of. There may
be
ways around this, but VBA error trapping will not do it.
- the first row in each excel sheet must contain column names.
- the data returned from Excel will not necessarily match what was
entered
unless the sheet is very simple. See http://tips.pjmsn.me.uk/t0003.htm
for
details if you need them.


Sub autonew()
'
'
Dim bQuit As Boolean
Dim bRetry As Boolean
Dim objMMMD As Word.Document
Dim strMmm As String
Dim strFullName As String
Dim strSQL As String

strMmm = "mmm"
bRetry = True
While bRetry
strMmm = InputBox("Enter the 3-letter month abbreviation, e.g. e.g.
jan,
or blank to quit. (Or press escape, etc.)", "Select the month", "mmm")
strMmm = LCase(Trim(strMmm))
Select Case strMmm
Case ""
MsgBox "You quit, or entered a blank month abbreviation - no merge
will be performed"
bQuit = True
bRetry = False
Case "jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep",
"oct", "nov", "dec"
' OK
bQuit = False
bRetry = False
Case Else
MsgBox "The month abbreviation needs to be one of jan, feb, mar,
etc.,"
End Select
Wend
If Not bQuit Then
On Error Resume Next
Set objMMMD = ActiveDocument

With objMMMD.MailMerge
' You need the full path name to the data source here...
strFullName = "C:\Documents and Settings\pjj.DOMAINX\My Documents\My
Data Sources\school_1.xls"
' You need to substitute your own column header names for "ColumnC"
and
"ColumnD"
strSQL = " SELECT * FROM [" & strMmm & "$]" & _
" WHERE ucase(ColumnC) = 'PARENT'" & _
" AND cdbl(ColumnD) 0"
.OpenDataSource _
Name:=strFullName, _
sqlstatement:=strSQL
If Err.Number 0 Then
MsgBox "Could not get the data from the spreadsheet. the merge will
not be run." & _
"( Error number " & CStr(Err.Number) & ", " & Err.Description & ")"
Err.Clear
Else
On Error GoTo 0
.DataSource.FirstRecord = wdDefaultFirstRecord
.DataSource.LastRecord = wdDefaultLastRecord
.Execute Pause:=False
.DataSource.Close
' If you are merging to a new document, that becomes the
Activedocument
' and you can close the mail merge main document
objMMMD.Close savechanges:=False
Set objMMMD = Nothing
End If
End With
End If
End Sub


--
Peter Jamieson
http://tips.pjmsn.me.uk

"Jerome_Ball" wrote in message
...
I am using Word 2003. I need to create a Template and save this
template.
What I need to do is as follows:

1. Data Source is in Excel 2003.

2. Monthly worksheets, that I need to send to parent that have an
amount
owed to
the company.

3. Need a way to have the user just run the Mail Merge process to
create
&
print
the letters that need to be sent.

4. Need for the user to select the month they wish to report. Each
month
correspond to a worksheet for the Workbook. Workbook is called
School_1.
Worksheets are "JAN", "FEB", etc.

5. The selection criteria is Column C must have "PARENT" and Column D
must
be
greater than 0.00. Need to be able to have template set up with
C1:C200 = "PARENT" and D1200 0.00.

6. When I used the wizard, I keep having to changing the field code of
the
merge
field, so that dollar amounts print properly from Column D.

7. What I would like to have it do is have the user enter the month
they
wish to
report against and then have the mail merge job run, display the
output
to
allow the user to make any mods they may want and then print.

This may be easy, yet I need expert help to do this. I would like to
automate as much as possible to keep them from making mistakes and
contacting
me. Thanx in advance for your help and asistance in this process. You
could
also send any directly to .

Jerome Ball

[Peter Jamieson]

1. Doug has provided some key pointers, but in addition...

have also checked out the links you sent,
but could not find information from your steps, so I am coming back to
you.

2. There is plenty of info. starting at....

http://word.mvps.org/FAQs/MacrosVBA/index.htm

Although it would be nice in a way to be able to install and use macros like
"magic bullets", i.e. without really understanding anything about how they
work, it is actually difficult and expensive to create macros that will
"just work" on any given system. So once you start doing stuff that involves
macros (in this case written in VBA, and also using SQL) It's advisable to
spend a bit of time exploring how things work, especially if your work is
going to be used by others. The chances are that provided macro code will
need to be tweaked in various ways before it will work at all, and the next
thing you know your users will be clamouring for modifications :-) If you
don't want to get into that situation, it's probably better to avoid doing
anything that requires you to use macros.

f. put the following Autonew macro code in a new module in your
template
1. I am taking that to be the example code you detailed. Yet, I do
not
know where to create a new module in the template and what you
mean
by Autonew macro code.

a. With your template selected in the VBA editor's Project list (in the
left-hand pane), click Insert-Module. You should see a new branch open up
your template's name in the project list. It will probably say

Modules
Module1

b. Ensure Module1 is selected.

c. The "Autonew macro code" is the VBA code I posted starting from

Sub Autonew()

and ending

End Sub

I called it "macro code" because "Sub"s in Word (and other applications) are
often referred to as "Macros"

I called it "Autonew code" simply because there are some special macros that
Word executes automatically when you create a new document based on a
template (Autonew), open a document (Autoopen), or close a document
(Autoclose)

d. Copy paste the Autonew macro code into the code pane to the right of the
Project list

e. When you try to run the code, you may find that the process of copying
the code has wrapped some of the lines. In that case you have to identify
the offending lines (the VBA editor uses some colour-coding which should
help) and put them back together


f. put the following Autonew macro code in a new module in your
template
1. I am taking that to be the example code you detailed. Yet, I do
not
know where to create a new module in the template and what you
mean
by Autonew macro code.

g. save and close your template
1. I think I would just close the Visual Basic Editor and then same
this
as a template again.

That would probably do it.

- the first row in each excel sheet must contain column names.
1. Would you suggest I do the following to meet your above
statement.
Cell ROW 1 COL C with the heading TYPE_SEL
Cell ROW 1 COL D with the heading AMT_DUE
and then name any other column I plan to use in the merge and
the rest of them could be left blank?

3. I would name all the columns. I would certainly name all the ones up to
and including the ones I wanted to use. You may be able to get away with not
naming the others but I leave you to find out. If you have a lot of columns,
you should be able to propagate names such as "field1","field2" using
standard Excel techniques. I would avoid spaces in names, but give them
readable names if possible - e.g. perhaps

RecordType (or whatever makes sense to you)
AmountDue

In that case, you would need to alter the SQL to

strSQL = " SELECT * FROM [" & strMmm & "$]" & _
" WHERE ucase(RecordType) = 'PARENT'" & _
" AND cdbl(AmountDue) 0"

If you use the names you suggested, you would need

strSQL = " SELECT * FROM [" & strMmm & "$]" & _
" WHERE ucase(TYPE_SEL) = 'PARENT'" & _
" AND cdbl(AMT_DUE) 0"


(If that does not work, you may need something like the following (using
your names):

strSQL = " SELECT T.* FROM [" & strMmm & "$] [T]" & _
" WHERE ucase(T.RecordType) = 'PARENT'" & _
" AND cdbl(T.AmountDue) 0"


--
Peter Jamieson
http://tips.pjmsn.me.uk

"Jerome_Ball" wrote in message
...
Peter,

I am trying this and getting a little confused. If you have some time, I
could use some guidance. I have also checked out the links you sent,
but could not find information from your steps, so I am coming back to
you.

a. create and save the template
DONE

b. set up the mail merge document type
1. I go to tools-letters&mailing-Mail Merge-
2. Select document Type.
* Letters
3. Select Starting Document
* Start From a template and I get the template I created.

c. connect to the data source and lay out your fields as you need them
1. Select recipients
* Connect to my data source (Excel) and build the criteria.

d. set up the destination (for a directory, it can only be a "new
document"
anyway)
1. I am lost here???????

e. in the Word VBA editor's immediate window, type
1. What I did was being up the Visual Basic Editor. I am unsure
about
the immediate window and where it is located. I tried looking
around,
but I cannot find it.

f. put the following Autonew macro code in a new module in your
template
1. I am taking that to be the example code you detailed. Yet, I do
not
know where to create a new module in the template and what you
mean
by Autonew macro code.

g. save and close your template
1. I think I would just close the Visual Basic Editor and then same
this
as a template again.

- the first row in each excel sheet must contain column names.
1. Would you suggest I do the following to meet your above
statement.
Cell ROW 1 COL C with the heading TYPE_SEL
Cell ROW 1 COL D with the heading AMT_DUE
and then name any other column I plan to use in the merge and
the rest of them could be left blank?

Thanx in advance for your assistance in this matter. I look
forward to hearing back.

"Peter Jamieson" wrote:

If you are starting from a .dot template, then I suggest you do the
following:
a. create and save the template
b. set up the mail merge document type
c. connect to the data source and lay out your fields as you need them
d. set up the destination (for a directory, it can only be a "new
document"
anyway)
e. in the Word VBA editor's immediate window, type
activedocument.mailmerge.datasource.close

In word 2003, that should close the data source without losing info.
about
your document type or destination.

f. put the following Autonew macro code in a new module in your template
g. save and close your template
h. test by using file-new, selecting the template, and providing a
month
abbreviation.

Some notes:
- It's just an outline - I don't claim it all works, and you will have
to
change at least some of it to get it to work at all.
- ask again about how to do the bits you are unfamiliar with. But
http://word.mvps.org is a useful resource about using macros
- if you specify sheet "feb" and there is no sheet feb, you will see an
ODBC dialog box that your users should probably cancel out of. There may
be
ways around this, but VBA error trapping will not do it.
- the first row in each excel sheet must contain column names.
- the data returned from Excel will not necessarily match what was
entered
unless the sheet is very simple. See http://tips.pjmsn.me.uk/t0003.htm
for
details if you need them.


Sub autonew()
'
'
Dim bQuit As Boolean
Dim bRetry As Boolean
Dim objMMMD As Word.Document
Dim strMmm As String
Dim strFullName As String
Dim strSQL As String

strMmm = "mmm"
bRetry = True
While bRetry
strMmm = InputBox("Enter the 3-letter month abbreviation, e.g. e.g.
jan,
or blank to quit. (Or press escape, etc.)", "Select the month", "mmm")
strMmm = LCase(Trim(strMmm))
Select Case strMmm
Case ""
MsgBox "You quit, or entered a blank month abbreviation - no merge
will be performed"
bQuit = True
bRetry = False
Case "jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep",
"oct", "nov", "dec"
' OK
bQuit = False
bRetry = False
Case Else
MsgBox "The month abbreviation needs to be one of jan, feb, mar,
etc.,"
End Select
Wend
If Not bQuit Then
On Error Resume Next
Set objMMMD = ActiveDocument

With objMMMD.MailMerge
' You need the full path name to the data source here...
strFullName = "C:\Documents and Settings\pjj.DOMAINX\My Documents\My
Data Sources\school_1.xls"
' You need to substitute your own column header names for "ColumnC"
and
"ColumnD"
strSQL = " SELECT * FROM [" & strMmm & "$]" & _
" WHERE ucase(ColumnC) = 'PARENT'" & _
" AND cdbl(ColumnD) 0"
.OpenDataSource _
Name:=strFullName, _
sqlstatement:=strSQL
If Err.Number 0 Then
MsgBox "Could not get the data from the spreadsheet. the merge will
not be run." & _
"( Error number " & CStr(Err.Number) & ", " & Err.Description & ")"
Err.Clear
Else
On Error GoTo 0
.DataSource.FirstRecord = wdDefaultFirstRecord
.DataSource.LastRecord = wdDefaultLastRecord
.Execute Pause:=False
.DataSource.Close
' If you are merging to a new document, that becomes the
Activedocument
' and you can close the mail merge main document
objMMMD.Close savechanges:=False
Set objMMMD = Nothing
End If
End With
End If
End Sub


--
Peter Jamieson
http://tips.pjmsn.me.uk

"Jerome_Ball" wrote in message
...
I am using Word 2003. I need to create a Template and save this
template.
What I need to do is as follows:

1. Data Source is in Excel 2003.

2. Monthly worksheets, that I need to send to parent that have an
amount
owed to
the company.

3. Need a way to have the user just run the Mail Merge process to
create
&
print
the letters that need to be sent.

4. Need for the user to select the month they wish to report. Each
month
correspond to a worksheet for the Workbook. Workbook is called
School_1.
Worksheets are "JAN", "FEB", etc.

5. The selection criteria is Column C must have "PARENT" and Column D
must
be
greater than 0.00. Need to be able to have template set up with
C1:C200 = "PARENT" and D1200 0.00.

6. When I used the wizard, I keep having to changing the field code of
the
merge
field, so that dollar amounts print properly from Column D.

7. What I would like to have it do is have the user enter the month
they
wish to
report against and then have the mail merge job run, display the
output
to
allow the user to make any mods they may want and then print.

This may be easy, yet I need expert help to do this. I would like to
automate as much as possible to keep them from making mistakes and
contacting
me. Thanx in advance for your help and asistance in this process. You
could
also send any directly to .

Jerome Ball