Hello,

I would like to control mailmerge by code. Addresses are in an xl file.
I recorded the whole process with the macro recorder.
However, when I run the macro, I do get the four letters (4 addresses
selected in the sql statement)
but the addresses fields remain desperately blank!
If you could put me on thetrack to solve this, I would thank you!(4 days
since I'm trying to fix it!)
Philippe.

Here's the code snippet I got with the macro recorder:

Sub Publi4()
'
' Publi4 Macro
' Macro enregistrée le 22/07/2005 par Philippe Pons
'
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"F:\Documents and
Settings\Administrateur\Bureau\SAVATOU_Chambéry\Do ssiers techniques\Essai
analyse Excel\Analyse de données3.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:\Documents and
Settings\Administrateur\Bureau\SAVATOU_Chambéry\Do ssiers techniques\Essai
analyse Excel\Analyse de données3.xls;Mode=Read;Extended
Properties=""HDR=YES;IMEX=1;"";Jet OLE" _
, SQLStatement:="SELECT * FROM `'Adresses relances$'`WHERE coll_id
in (1, 2, 3, 4)", SQLStatement1:="" _
, SubType:=wdMergeSubTypeAccess
ActiveDocument.Fields.Add Range:=Selection.Range, Type:= _
wdFieldAddressBlock, Text:= _
"\f ""_FIRST0_ _LAST0_ _SUFFIX0_" & Chr(13) &
"_COMPANY_" & Chr(13) & "_STREET1_" & Chr(13) & "_STREET2_" &
Chr(13) & "_POSTAL_ _CITY_" & Chr(13) & "_STATE_"" \l 1036 \c
0 \e """""
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
ActiveWindow.ActivePane.VerticalPercentScrolled = 9
End Sub

Sorry, but it is crazy to try and create a mailmerge maindocument from
scratch using VBA. Automate the attachment of the datasource and the
execution of the merge if you will, but in 4 days you could have done it
manually countless times over.

Also, I would suggest that you do not use the AddressBlock field (which is
probably the cause of your problem). Far better to just insert the
individual fields in the configuration that you want them.

--
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
"Philippe Pons" wrote in message
...
Hello,

I would like to control mailmerge by code. Addresses are in an xl file.
I recorded the whole process with the macro recorder.
However, when I run the macro, I do get the four letters (4 addresses
selected in the sql statement)
but the addresses fields remain desperately blank!
If you could put me on thetrack to solve this, I would thank you!(4 days
since I'm trying to fix it!)
Philippe.

Here's the code snippet I got with the macro recorder:

Sub Publi4()
'
' Publi4 Macro
' Macro enregistrée le 22/07/2005 par Philippe Pons
'
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"F:\Documents and
Settings\Administrateur\Bureau\SAVATOU_Chambéry\Do ssiers techniques\Essai
analyse Excel\Analyse de données3.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:\Documents and
Settings\Administrateur\Bureau\SAVATOU_Chambéry\Do ssiers techniques\Essai
analyse Excel\Analyse de données3.xls;Mode=Read;Extended
Properties=""HDR=YES;IMEX=1;"";Jet OLE" _
, SQLStatement:="SELECT * FROM `'Adresses relances$'`WHERE coll_id
in (1, 2, 3, 4)", SQLStatement1:="" _
, SubType:=wdMergeSubTypeAccess
ActiveDocument.Fields.Add Range:=Selection.Range, Type:= _
wdFieldAddressBlock, Text:= _
"\f ""_FIRST0_ _LAST0_ _SUFFIX0_" & Chr(13) &
"_COMPANY_" & Chr(13) & "_STREET1_" & Chr(13) & "_STREET2_" &
Chr(13) & "_POSTAL_ _CITY_" & Chr(13) & "_STATE_"" \l 1036
\c
0 \e """""
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
ActiveWindow.ActivePane.VerticalPercentScrolled = 9
End Sub

Thanks Dough, will try follow your recomendations.
Philippe

"Doug Robbins" a écrit dans le message de
...
Sorry, but it is crazy to try and create a mailmerge maindocument from
scratch using VBA. Automate the attachment of the datasource and the
execution of the merge if you will, but in 4 days you could have done it
manually countless times over.

Also, I would suggest that you do not use the AddressBlock field (which is
probably the cause of your problem). Far better to just insert the
individual fields in the configuration that you want them.

--
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
"Philippe Pons" wrote in message
...
Hello,

I would like to control mailmerge by code. Addresses are in an xl file.
I recorded the whole process with the macro recorder.
However, when I run the macro, I do get the four letters (4 addresses
selected in the sql statement)
but the addresses fields remain desperately blank!
If you could put me on thetrack to solve this, I would thank you!(4 days
since I'm trying to fix it!)
Philippe.

Here's the code snippet I got with the macro recorder:

Sub Publi4()
'
' Publi4 Macro
' Macro enregistrée le 22/07/2005 par Philippe Pons
'
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"F:\Documents and
Settings\Administrateur\Bureau\SAVATOU_Chambéry\Do ssiers
techniques\Essai
analyse Excel\Analyse de données3.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:\Documents and
Settings\Administrateur\Bureau\SAVATOU_Chambéry\Do ssiers
techniques\Essai
analyse Excel\Analyse de données3.xls;Mode=Read;Extended
Properties=""HDR=YES;IMEX=1;"";Jet OLE" _
, SQLStatement:="SELECT * FROM `'Adresses relances$'`WHERE
coll_id
in (1, 2, 3, 4)", SQLStatement1:="" _
, SubType:=wdMergeSubTypeAccess
ActiveDocument.Fields.Add Range:=Selection.Range, Type:= _
wdFieldAddressBlock, Text:= _
"\f ""_FIRST0_ _LAST0_ _SUFFIX0_" & Chr(13) &
"_COMPANY_" & Chr(13) & "_STREET1_" & Chr(13) & "_STREET2_" &
Chr(13) & "_POSTAL_ _CITY_" & Chr(13) & "_STATE_"" \l 1036
\c
0 \e """""
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
ActiveWindow.ActivePane.VerticalPercentScrolled = 9
End Sub