Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...
MS Office Forum / Word / Mailmerge and Fax / August 2006

Tip: Looking for answers? Try searching our database.

Auto Mail Merge

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
The Blues - 02 Aug 2006 12:54 GMT
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.
Reply to this Message
Peter Jamieson - 02 Aug 2006 14:20 GMT
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.
Reply to this Message
The Blues - 02 Aug 2006 15:37 GMT
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?

> 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
[quoted text clipped - 41 lines]
>
> Cheers.
Reply to this Message
Peter Jamieson - 02 Aug 2006 15:51 GMT
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?

Peter Jamieson wrote:
> 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
[quoted text clipped - 43 lines]
>
> Cheers.
Reply to this Message
The Blues - 02 Aug 2006 16:52 GMT
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.

> 1. What does it actually do? Do you see an error message?
>
[quoted text clipped - 56 lines]
> >
> > Cheers.
Reply to this Message
Peter Jamieson - 02 Aug 2006 17:14 GMT
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.

Peter Jamieson wrote:
> 1. What does it actually do? Do you see an error message?
>
[quoted text clipped - 59 lines]
> >
> > Cheers.
Reply to this Message
The Blues - 03 Aug 2006 17:22 GMT
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.

> 1. Do the  <<Next Record>><<Addressblock>> fields propagate successfully? If
> not, are you using a tablet PC?
[quoted text clipped - 124 lines]
> > >
> > > Cheers.
Reply to this Message

 Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.