MS Word Macro to create Mailing Labels in Word

[pincivma]

Hi there

I'm OK at writing macros in Excel but not in MS Word. I have created a spread sheet with a few fields and records in Excel. I then opened up MS Word and recorded a macro to create Mailing Labels. I used a short cut key Ctrl+E to run the macro. I then saved the mailing labels and the other MS Word that comes with mailing labels. You know the one I mean. The macro worked fine. I then e-mailed the 2 MS Word files and the Excel file to my office.

Note: I did not save the macro within my normal.dot. I Accessed "Tools">>"Templates and Add-ins" from the menu bar. From there I clicked on the "Organizer" button and then selected the "Macros" tab. From there I copied the macro from the Normal.dot to another file and then just sent the files to my computer at work. I put my security setting to low. I changed the path of the file in the macro in my office computer. The macro did not work. For your information I have attached the recorded macro in MS Word

Sub Labels()
'
' Labels Macro
' Macro recorded 26/11/2007 by Pinciveroma
'
ActiveDocument.MailMerge.MainDocumentType = wdMailingLabels
ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:\Documents and Settings\Pinciveroma\PO RECEIPTS.xls" _
, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="", SQLStatement:="", SQLStatement1 _
:=""
Application.MailingLabel.DefaultPrintBarCode = False
Application.MailingLabel.CreateNewDocument Name:="", Address:="", AutoText _
:="ToolsCreateLabels1", LaserTray:=wdPrinterManualFeed
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With
End Sub


Has anyone out there tried to automate mailing lables in MS Word??

Mario
http://www.theofficeexperts.com/forum/images/statusicon/user_online.gif http://www.theofficeexperts.com/forum/images/buttons/report.gif (http://www.theofficeexperts.com/forum/report.php?p=30980)

[TonyJollans]

When you say "did not work", what exactly did it do?

Do you have all the bits - the AutoText, for example, - on your work PC?

[pincivma]

Hi TonyJolans

Here is what happend. I opend my files at the office and ran the macro. I got the following error message.

"The method or property is not available because the current mail merge main document has no merge fields".

When I hit the debug button, the line

.Execute Pause:=True

was highlighted.

I do not know word macros at all. I thought that they would work just as well as macros recorded in Excel but apparently they do not.

The macro was attached to the word document. I was careful to save both the word documents and the Excel spread sheet.

Mario

[TonyJollans]

Where do you think the Merge Fields are coming from?

[pincivma]

The data source is coming from the Excel spread sheet called PO RECEIPTS.xls.

When the "Create Labels" pop up screen comes up during the recording of the macro, I then insert merge fields. I saved this document as IR.doc.

The IR is the first field on the mail merge list.

?IR_NO?
?IR_PART_NO?
?IR_PO_NO?
?IR_QUANTITY?


?Next Record??IR_NO?
?IR_PART_NO?
?IR_PO_NO?
?IR_QUANTITY?


?Next Record??IR_NO?
?IR_PART_NO?
?IR_PO_NO?
?IR_QUANTITY?


?Next Record??IR_NO?
?IR_PART_NO?
?IR_PO_NO?
?IR_QUANTITY?

[TonyJollans]

So you are doing CreateNewDocument and expecting all that to be in it just becuse you saved it somewhere else?

Sorry, that reads a bit badly, but try to think through what you did and what you are doing and what you expect to happen - particularly with respect to documents.

[pincivma]

Now I'm confused even more. All I did was I used the macro recorder to record a macro in MS word to create mailing labels. The key strokes were then translated into VBA by the macro recorder. Before I saved the 2 word files that accompany the mailing labels, I copied the macro from the Normal.dot to another file and then just sent the 2 files with the Excel file to my computer at work. Make sure that the path is correct in the other computer.

I saw that the macro that I recorded was there in my office computer when I opened the word file. However, when I run the macro I get an error as shown in my previous post.

The best way for you to understand what I'm talking about is to test it out yourself. Create a worksheet with 3 or 4 fields and populate those fields with 3 or 4 records. Open MS Word Go to Tools - Macro - Macros - Record New Macro and then go to Mail Merge and choose mailing labels. Follow the wizard's instructions. Stop the macro when you have created your mailing labels.

Then using the organizer button copy the macro from the Normal.dot to another file and then just sent the files to another computer. (Note: if the Organizer button does not appear when you click on Tools- Macro - Macros then see my previous posts that explains how to add the organizer button). Run the macro and then you will see what the problem is. I'm 100% certain that you will get an error when you run the macro from a different computer. If you can fix your error then you can also help me fix mine.

Mario

[TonyJollans]

I'm not disagreeing with you. I know you get an error.

There are potentially three documents in Word:


You start out with one before you begin - this really has nothing to do with the merge.
You create a skeleton record for the Merge - this is a separate document because you told the wizard to make it so (and the recorded code translates that as Application.MailingLabel.CreateNewDocument ...
You then have the result of the merge in yet another document based on the second one.What (I think) you have done is (a) not recorded building the skeleton and (b) copied the recorded code to the second document. When you copied the code and ran it on another computer what you had as the second document became the first in the new environment - and the new second one was just blank.

I'm not a mail merge expert and can't tell you off the top of my head what the precise code should be - I'll have a play with it later and post back when I know what I'm talking about.

[TonyJollans]

I've had a play with this and I couldn't record what you managed but I have to return to my first question ...

> Do you have all the bits - the AutoText, for example, - on your work PC?

I presume that you set up an AutoText containing the mergefields and you have not copied that across to your work PC?

[pincivma]

I will wait for your next post, once you figured it out.

Mario

[TonyJollans]

Can you please check out the AutoText. You seem to be ignoring all mention of it.

[pincivma]

When I record the macro, I do not use the autotext feature at all.

Mario

[TonyJollans]

As I think I've already said, Mail Merges are not my strongest suit but there ia an AutoText in your recorded code and it appears to be the source of your merge skeleton.

If you have it all in your document, try deleting this line and see if it helps
Application.MailingLabel.CreateNewDocument Name:="", Address:="", AutoText _
:="ToolsCreateLabels1", LaserTray:=wdPrinterManualFeed

[pincivma]

I deleted that line and I got the same error as before. That line has to be in there for the macro to run. The autotext line was recorded when I was recording the macro. It must be noted that I did not key in Insert - Autotext form the tools menu.

Don't get me wrong. The macro works perfectly if I recorded it either at my home computer or the office computer. It does not work when I transport the macro from one computer to another. And that is exactly the problem I'm trying to solve.

I guess there is no one out there that can help me with this problem.

Mario

[TonyJollans]

Although at the moment I don't know why you get the AutoText, I am sure it is a necessary component of the process and if you want to copy a fully functional process from one computer to another you must also copy the Autotext.

How, and at what point in the process, did you originally get the merge skeleton record into the document?

[pincivma]

I did not save the macro within my normal.dot. I Accessed "Tools">>"Templates and Add-ins" from the menu bar. From there I clicked on the "Organizer" button and then selected the "Macros" tab. From there I copied the macro from the Normal.dot right side of the pop up screen to Document1 left hand side of the pop up screen. I then e-mailed the files to my computer at work. I put my security setting to low. I changed the path of the file in the macro in my office computer.

The macro in my office computer is identical to the one that worked on my home computer.

Mario

[TonyJollans]

We don't seem to be communicating at all here.

I understand what you did with the macro. What I don't understand is how you got the mergefields into the document.

[pincivma]

No Idea. I Just recorded the macro.

[TonyJollans]

No idea???

Did this lot all appear by magic?

?IR_NO?
?IR_PART_NO?
?IR_PO_NO?
?IR_QUANTITY?


?Next Record??IR_NO?
?IR_PART_NO?
?IR_PO_NO?
?IR_QUANTITY?


?Next Record??IR_NO?
?IR_PART_NO?
?IR_PO_NO?
?IR_QUANTITY?


?Next Record??IR_NO?
?IR_PART_NO?
?IR_PO_NO?
?IR_QUANTITY?


I'm afraid I'm stuck and will have to defer to someone else. I don't know what you have on your machine that causes this to happen.

[pincivma]

No, it did not appear by magic. It is part of the recorded macro. These are my recorded steps:

I opened MS Word and clicked on
Tools - Maco - Record New macro - OK
Tools - Mail Merge - Create - Mailing Labels - Active Window - Get Data
Open Data Source

I then found my Excel file and opened it and clicked OK

I then clicked on Set Up Main Document

A pop up screen appeared that had all the label options. I just chose the first label and Clicked on OK

Next I clicked on Insert Merge Field. All the fields that are on the Excel spreadheet appeared and I entered them one at a time into a blank screen. I then clicked on Merge. A dialoque box opened up and I clicked on Merge again.

After a few seconds, an MS Word document appeared with the mailing labels. I stopped the macro recorder. I then saved the file as Labels.doc and closed the file. As soon as the file containing the mailing labels closed, another word file looking like the following appeared.

?IR_NO?
?IR_PART_NO?
?IR_PO_NO?
?IR_QUANTITY?


?Next Record??IR_NO?
?IR_PART_NO?
?IR_PO_NO?
?IR_QUANTITY?


?Next Record??IR_NO?
?IR_PART_NO?
?IR_PO_NO?
?IR_QUANTITY?


?Next Record??IR_NO?
?IR_PART_NO?
?IR_PO_NO?
?IR_QUANTITY?


I then saved this file as IR.doc. So now I have 2 saved word files. When I run the macro it works perfectly. But when I send the 2 word files and the Excel file to a different computer it does not work.

Mario

[TonyJollans]

I gather you are using Word 2000 and I can't recreate exactly what you have done but ...

Next I clicked on Insert Merge Field. All the fields that are on the Excel spreadheet appeared and I entered them one at a time into a blank screen.

This bit is not recorded.

What is recorded is this:

Application.MailingLabel.DefaultPrintBarCode = False
Application.MailingLabel.CreateNewDocument Name:="", Address:="", AutoText _
:="ToolsCreateLabels1", LaserTray:=wdPrinterManualFeed


I don't know how you've got this - I don't know if you have some special label set up or some add-in that does it, or if I'm just missing something obvious - but, as I've been saying all along, it appears as though the AutoText is important and it is one thing you have not copied. Can you look and see if you have an Autotext called "ToolsCreateLabels1" and, if so, what it contains.

=============================

If I start with a document containing

?IR_NO?
?IR_PART_NO?
?IR_PO_NO?
?IR_QUANTITY?

etc.

and run your code with the one line above removed it works correctly but you say you have tried this and it still fails so I really am at a loss.

[pincivma]

I am using Word 2000. During the macro record, there is a pop up screen that has all the Avery labels on it. If I choose the first Avery label the macro text that is recorded is

Application.MailingLabel.CreateNewDocument Name:="", Address:="", AutoText _
:="ToolsCreateLabels1", LaserTray:=wdPrinterManualFeed

If I choose a second Avery label, I get

Application.MailingLabel.CreateNewDocument Name:="", Address:="", AutoText _
:="ToolsCreateLabels2", LaserTray:=wdPrinterManualFeed

So I am assuming that the AutoText is creating the mailing labels and giving them names such as "ToolsCreateLabels1" and "ToolsCreateLabels2".
The reason why "ToolsCreateLabels2" is because I had already a line from a previous recorded macro that had that I recorded that had "ToolsCreateLabels1"

Does that make sense to you??

Mario