[VBA] how to automate the mail merge process?

P

Philippe Pons

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\Dossiers 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="""";User ID=Admin;Data
Source=F:\Documents and
Settings\Administrateur\Bureau\SAVATOU_Chambéry\Dossiers 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
 
D

Doug Robbins

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
 
Top