Reply
 
Thread Tools Display Modes
  #1   Report Post  
Philippe Pons
 
Posts: n/a
Default [VBA] how to automate the mail merge process?

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


  #2   Report Post  
Doug Robbins
 
Posts: n/a
Default

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




  #3   Report Post  
Philippe Pons
 
Posts: n/a
Default

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






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
Automate a mail merge in Word 2002 from a macro in Access 2002 Ed B Mailmerge 9 July 8th 05 04:31 PM
Mail Merge - Parameter prompts twice... TL Mailmerge 6 June 17th 05 02:58 PM
mail merge with attachments AS Mailmerge 5 April 9th 05 09:49 AM
How to protect the main document in a mail merge process Antonio Ortiz Mailmerge 4 February 17th 05 09:05 PM
How to protect the main document in a mail merge process Antonio O Mailmerge 0 February 16th 05 04:29 PM


All times are GMT +1. The time now is 04:57 AM.

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"