save a mail merge template

[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 (e-mail address removed).

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

 

[Jerome_Ball]

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.

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

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 (e-mail address removed).

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

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.

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

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 (e-mail address removed).

Jerome Ball