Auto Mail Merge

[The Blues]

Hi,

Ive recorded a macro to mail merge data from excel. When the macro is
run it only gets as far as the end of step 4, and displays the
following on the labels -

«Next Record»«AddressBlock»

It doesnt complete step 5 "Preview Labels" or step 6 "Complete the
merge"

Any ideas ??

Cheers.

 

[Peter Jamieson]

Something to bear in mind is that the macro recorder does not record
something that will necessarily replay every step you took in Word. Some
actions in Word do not have equivalent VBA commands and they don't get
recorded.

Open the VBA editor and have a look at the macro you recorded.

I haven't checked, but the chances are that it has a lot of stuff that
a. sets up the merge type and merge data source
b. sets up the label layout and fields

Typically you don't need either (a) or (b) once you have already done it -
what you need to do is save the mail merge main document you have created -
when you re-open it, all that stuff will still be there. The merge
destination (printer or new document) can be saved in there too.

What you probably will not see in the macro is a statement like

ActiveDocument.MailMerge.Execute

and that's the statment you need to add in order to get the labels to merge
(either to printer or a new document).

If you need to set the destination, set e.g.

ActiveDocument.MailMerge.Destination = wdMergeToPrinter

or whatever.

Peter Jamieson

Hi,

Ive recorded a macro to mail merge data from excel. When the macro is
run it only gets as far as the end of step 4, and displays the
following on the labels -

«Next Record»«AddressBlock»

It doesnt complete step 5 "Preview Labels" or step 6 "Complete the
merge"

Any ideas ??

Cheers.

 

[The Blues]

Hi,

Thanks for that, I've added the ' .Execute', statement at the end of
the script but, unfortunately, I get the same result.

I assume it is to go at the end?

 

[Peter Jamieson]

1. What does it actually do? Do you see an error message?

2. Can you copy the text of the macro into a message in this thread please?

Peter Jamieson
Hi,

Thanks for that, I've added the ' .Execute', statement at the end of
the script but, unfortunately, I get the same result.

I assume it is to go at the end?

 

[The Blues]

There's no error message, it simply still displays the «Next
Record»«AddressBlock» message as previous.

Ive pasted the script below.


Sub Merge()
'
' Merge Macro

Application.MailingLabel.DefaultPrintBarCode = False
Application.MailingLabel.CreateNewDocument Name:="AOne 28171",
Address:="", AutoText:="ToolsCreateLabels3",
LaserTray:=wdPrinterTractorFeed, _
ExtractAddress:=False, PrintEPostageLabel:=False, Vertical:=False

ActiveDocument.MailMerge.MainDocumentType = wdMailingLabels
ActiveDocument.MailMerge.OpenDataSource Name:="C:\test.csv", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True,
_
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="", SQLStatement:="",
SQLStatement1 _
:="", SubType:=wdMergeSubTypeOther
ActiveDocument.Fields.Add Range:=Selection.Range, Type:= _
wdFieldAddressBlock, Text:= _
"\f ""<<_TITLE0_ >><<_FIRST0_>><< _LAST0_>><< _SUFFIX0_>>" &
Chr(13) & "<<_COMPANY_," & Chr(13) & ">><<_STREET1_" & Chr(13) &
">><<_STREET2_" & Chr(13) & ">><<_CITY_" & Chr(13) & ">><<_STATE_" &
Chr(13) & ">><<_POSTAL_>><<" & Chr(13) & "_COUNTRY_>>"" \l 2057 \c 2 \e
""U"
WordBasic.ActiveDocument.MailMergePropagateLabel
ActiveDocument.MailMerge.Execute

End Sub




Thanks for all your help.

Regards.

 

[Peter Jamieson]

1. Do the <<Next Record>><<Addressblock>> fields propagate successfully? If
not, are you using a tablet PC?

2. if you enable the MailMerge Toolbar (e.g. using
Tools|Customize|Toolbars), after all the propagates have been done, are the
icons after the first two icons in the toolbar greyed out? If so, you may
need to set a registry item as described in the following article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;825765

If not, can you create a macro with /just/ the following lines and see what
happens when you run it:

Sub testexec()
ActiveDocument.MailMerge.Destination = wdSendToNewDocument
ActiveDocument.MailMerge.Execute
End Sub

(NB I think I got the name of "wdSendToPrinter" wrong before)

Peter Jamieson


There's no error message, it simply still displays the «Next
Record»«AddressBlock» message as previous.

Ive pasted the script below.


Sub Merge()
'
' Merge Macro

Application.MailingLabel.DefaultPrintBarCode = False
Application.MailingLabel.CreateNewDocument Name:="AOne 28171",
Address:="", AutoText:="ToolsCreateLabels3",
LaserTray:=wdPrinterTractorFeed, _
ExtractAddress:=False, PrintEPostageLabel:=False, Vertical:=False

ActiveDocument.MailMerge.MainDocumentType = wdMailingLabels
ActiveDocument.MailMerge.OpenDataSource Name:="C:\test.csv", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True,
_
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="", SQLStatement:="",
SQLStatement1 _
:="", SubType:=wdMergeSubTypeOther
ActiveDocument.Fields.Add Range:=Selection.Range, Type:= _
wdFieldAddressBlock, Text:= _
"\f ""<<_TITLE0_ >><<_FIRST0_>><< _LAST0_>><< _SUFFIX0_>>" &
Chr(13) & "<<_COMPANY_," & Chr(13) & ">><<_STREET1_" & Chr(13) &
">><<_STREET2_" & Chr(13) & ">><<_CITY_" & Chr(13) & ">><<_STATE_" &
Chr(13) & ">><<_POSTAL_>><<" & Chr(13) & "_COUNTRY_>>"" \l 2057 \c 2 \e
""U"
WordBasic.ActiveDocument.MailMergePropagateLabel
ActiveDocument.MailMerge.Execute

End Sub




Thanks for all your help.

Regards.

 

[The Blues]

Hi,

Ive found the problem, I had omitted to insert headers into the source
document csv file so the macro couldnt identify what to transfer onto
the label.

Thanks for all your help anyway, it was much appreciated.

Regards.