Reply
 
Thread Tools Display Modes
  #1   Report Post  
anton
 
Posts: n/a
Default Mailmerge with multipule letters

I have a question, (Sorry for my english)

I make a lot off mailmerge in Excel and Word together ,with one letter
hundreds at de same time ,but I want to make a mailmerge with more than one
letter , example

Policynumber 1 , letter 1
policynumber 2 , letter 2
policynumber 3 , letter 1
policynumber 4 , letter 4

up to 6 or 8 letters and 600 policynumbers sometimes 100 for letter 1
sometimes 10 letter 2

of corse I can use Sort on de different letters , and then vlookup but dat
is a lot of work , for say 10 letters of letter 4

I was thinking to use somekind off code that trikkert the letter , maybe
macro to start

we don't have MS Acces at our systems


  #2   Report Post  
Peter Jamieson
 
Posts: n/a
Default

You must know how many different letters there are, so the easiest way is to
construct the letters, set up your Excel sheet as the data source in each
letter, but use the filtering options (and sort options, if necessary) to
choose the records with the matching letter type in Excel. Then merge each
letter in turn. Or write a simple macro to open each mailmerge letter in
turn and merge it.

Peter Jamieson

"anton" wrote in message
l...
I have a question, (Sorry for my english)

I make a lot off mailmerge in Excel and Word together ,with one letter
hundreds at de same time ,but I want to make a mailmerge with more than
one letter , example

Policynumber 1 , letter 1
policynumber 2 , letter 2
policynumber 3 , letter 1
policynumber 4 , letter 4

up to 6 or 8 letters and 600 policynumbers sometimes 100 for letter 1
sometimes 10 letter 2

of corse I can use Sort on de different letters , and then vlookup but dat
is a lot of work , for say 10 letters of letter 4

I was thinking to use somekind off code that trikkert the letter , maybe
macro to start

we don't have MS Acces at our systems



  #3   Report Post  
anton
 
Posts: n/a
Default

Peter what you tel me is the thing i do daily 1 letter for 600 policy , but
i want this for 10 different letters , merge this 10 times is a lot of work
, i was trying to make a macro for it but I can't filter during de macro
because of de SQL statement , I can't say I want kolom 4 and filter on
number 4

my macro looks like this , i am using word 2003 , excel 2003 system xp


Sub Macro4()
'
' Macro4 Macro
' Macro opgenomen op 4 april 2005 door anton pc
'
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"F:\backup cd\excelbestanden\Map1\Map1.xls",
ConfirmConversions:=False, _
ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="",
WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto,
_
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er ID=Admin;Data
Source=F:\backup cd\excelbestanden\Map1\Map1.xls;Mode=Read;Extended
Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet
OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLE" _
, SQLStatement:="SELECT * FROM `Blad1$`", SQLStatement1:="",
SubType:= _ XXXXX here i want to filter xxxxxxxx kolom 2 , number 4 or 5
or 6 or 7 ( letter number)
wdMergeSubTypeAccess
End Sub



"Peter Jamieson" schreef in bericht
...
You must know how many different letters there are, so the easiest way is
to construct the letters, set up your Excel sheet as the data source in
each letter, but use the filtering options (and sort options, if
necessary) to choose the records with the matching letter type in Excel.
Then merge each letter in turn. Or write a simple macro to open each
mailmerge letter in turn and merge it.

Peter Jamieson

"anton" wrote in message
l...
I have a question, (Sorry for my english)

I make a lot off mailmerge in Excel and Word together ,with one letter
hundreds at de same time ,but I want to make a mailmerge with more than
one letter , example

Policynumber 1 , letter 1
policynumber 2 , letter 2
policynumber 3 , letter 1
policynumber 4 , letter 4

up to 6 or 8 letters and 600 policynumbers sometimes 100 for letter 1
sometimes 10 letter 2

of corse I can use Sort on de different letters , and then vlookup but
dat is a lot of work , for say 10 letters of letter 4

I was thinking to use somekind off code that trikkert the letter , maybe
macro to start

we don't have MS Acces at our systems





  #4   Report Post  
Peter Jamieson
 
Posts: n/a
Default

You need to put a different "WHERE" clause in the SQLStatement for each
merge. The "Subtype" parameter has a different function and is not well
documented.

For example, if you have a column called "Lettertype" in your Excel sheet
and it has values 1,2,3,4,5,6,7,8,9,10 depending on the letter, you could
use

SQLStatement="SELECT * FROM `Blad1$` WHERE Lettertype = 1"

in the first letter,

SQLStatement="SELECT * FROM `Blad1$` WHERE Lettertype = 2"

in the second letter, and so on. If Lettertype is a text field, you need to
put single quotes around the values, e.g.

SQLStatement="SELECT * FROM `Blad1$` WHERE Lettertype = 'A'"

Notice that these quotes are different from the ones around Blad1$.

You may also need to put quotes around Lettertype in some cases. These would
be the same type of quotes as you have around Blad1$.

You can /probably/ simplify your OpenDataSource to the following:

ActiveDocument.MailMerge.OpenDataSource Name := _
"F:\backup cd\excelbestanden\Map1\Map1.xls", _
Connection:= "", _
SQLStatement:="SELECT * FROM `Blad1$` WHERE Lettertype = 1"

However, I leave it to you to find out what actually works.

Peter Jamieson

"anton" wrote in message
...
Peter what you tel me is the thing i do daily 1 letter for 600 policy ,
but i want this for 10 different letters , merge this 10 times is a lot of
work , i was trying to make a macro for it but I can't filter during de
macro because of de SQL statement , I can't say I want kolom 4 and filter
on number 4

my macro looks like this , i am using word 2003 , excel 2003 system xp


Sub Macro4()
'
' Macro4 Macro
' Macro opgenomen op 4 april 2005 door anton pc
'
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"F:\backup cd\excelbestanden\Map1\Map1.xls",
ConfirmConversions:=False, _
ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="",
WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto,
_
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er ID=Admin;Data
Source=F:\backup cd\excelbestanden\Map1\Map1.xls;Mode=Read;Extended
Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet
OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLE" _
, SQLStatement:="SELECT * FROM `Blad1$`", SQLStatement1:="",
SubType:= _ XXXXX here i want to filter xxxxxxxx kolom 2 , number 4 or
5 or 6 or 7 ( letter number)
wdMergeSubTypeAccess
End Sub



"Peter Jamieson" schreef in bericht
...
You must know how many different letters there are, so the easiest way is
to construct the letters, set up your Excel sheet as the data source in
each letter, but use the filtering options (and sort options, if
necessary) to choose the records with the matching letter type in Excel.
Then merge each letter in turn. Or write a simple macro to open each
mailmerge letter in turn and merge it.

Peter Jamieson

"anton" wrote in message
l...
I have a question, (Sorry for my english)

I make a lot off mailmerge in Excel and Word together ,with one letter
hundreds at de same time ,but I want to make a mailmerge with more than
one letter , example

Policynumber 1 , letter 1
policynumber 2 , letter 2
policynumber 3 , letter 1
policynumber 4 , letter 4

up to 6 or 8 letters and 600 policynumbers sometimes 100 for letter 1
sometimes 10 letter 2

of corse I can use Sort on de different letters , and then vlookup but
dat is a lot of work , for say 10 letters of letter 4

I was thinking to use somekind off code that trikkert the letter , maybe
macro to start

we don't have MS Acces at our systems







  #5   Report Post  
anton
 
Posts: n/a
Default

Thank you very much for your fast help
i wil try and let you now what will work

"Peter Jamieson" schreef in bericht
...
You need to put a different "WHERE" clause in the SQLStatement for each
merge. The "Subtype" parameter has a different function and is not well
documented.

For example, if you have a column called "Lettertype" in your Excel sheet
and it has values 1,2,3,4,5,6,7,8,9,10 depending on the letter, you could
use

SQLStatement="SELECT * FROM `Blad1$` WHERE Lettertype = 1"

in the first letter,

SQLStatement="SELECT * FROM `Blad1$` WHERE Lettertype = 2"

in the second letter, and so on. If Lettertype is a text field, you need
to put single quotes around the values, e.g.

SQLStatement="SELECT * FROM `Blad1$` WHERE Lettertype = 'A'"

Notice that these quotes are different from the ones around Blad1$.

You may also need to put quotes around Lettertype in some cases. These
would be the same type of quotes as you have around Blad1$.

You can /probably/ simplify your OpenDataSource to the following:

ActiveDocument.MailMerge.OpenDataSource Name := _
"F:\backup cd\excelbestanden\Map1\Map1.xls", _
Connection:= "", _
SQLStatement:="SELECT * FROM `Blad1$` WHERE Lettertype = 1"

However, I leave it to you to find out what actually works.

Peter Jamieson

"anton" wrote in message
...
Peter what you tel me is the thing i do daily 1 letter for 600 policy ,
but i want this for 10 different letters , merge this 10 times is a lot
of work , i was trying to make a macro for it but I can't filter during
de macro because of de SQL statement , I can't say I want kolom 4 and
filter on number 4

my macro looks like this , i am using word 2003 , excel 2003 system xp


Sub Macro4()
'
' Macro4 Macro
' Macro opgenomen op 4 april 2005 door anton pc
'
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"F:\backup cd\excelbestanden\Map1\Map1.xls",
ConfirmConversions:=False, _
ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="",
WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False,
Format:=wdOpenFormatAuto, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er ID=Admin;Data
Source=F:\backup cd\excelbestanden\Map1\Map1.xls;Mode=Read;Extended
Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet
OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLE" _
, SQLStatement:="SELECT * FROM `Blad1$`", SQLStatement1:="",
SubType:= _ XXXXX here i want to filter xxxxxxxx kolom 2 , number 4 or
5 or 6 or 7 ( letter number)
wdMergeSubTypeAccess
End Sub



"Peter Jamieson" schreef in bericht
...
You must know how many different letters there are, so the easiest way
is to construct the letters, set up your Excel sheet as the data source
in each letter, but use the filtering options (and sort options, if
necessary) to choose the records with the matching letter type in Excel.
Then merge each letter in turn. Or write a simple macro to open each
mailmerge letter in turn and merge it.

Peter Jamieson

"anton" wrote in message
l...
I have a question, (Sorry for my english)

I make a lot off mailmerge in Excel and Word together ,with one letter
hundreds at de same time ,but I want to make a mailmerge with more than
one letter , example

Policynumber 1 , letter 1
policynumber 2 , letter 2
policynumber 3 , letter 1
policynumber 4 , letter 4

up to 6 or 8 letters and 600 policynumbers sometimes 100 for letter 1
sometimes 10 letter 2

of corse I can use Sort on de different letters , and then vlookup but
dat is a lot of work , for say 10 letters of letter 4

I was thinking to use somekind off code that trikkert the letter ,
maybe macro to start

we don't have MS Acces at our systems









Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
enter an excel module global variable value in a mailmerge letter windowed Mailmerge 1 March 22nd 05 10:31 PM
Textboxes in Mailmerge? zSplash Mailmerge 5 March 22nd 05 10:01 PM
Not all the checked names are merged into letters Bea Mailmerge 1 February 1st 05 02:00 AM
Mailmerge in Word, date format in merged doc brownsox Mailmerge 2 January 14th 05 01:29 PM
Included Property in Word 2003 MailMerge Alessio Mailmerge 1 December 23rd 04 12:26 PM


All times are GMT +1. The time now is 11:19 PM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"