Can anyone help me with improving my Mail Merge macro. The code below works OK,it creates a Word 2000 doc. with labels and reads in the contents of an Excel file. I need to format the resulting labels, (Font size & Bold). Is there an elegant way to format the text field after insertion ? Alternatively, how can I get focus to the resulting Label1 document in order to format it conventionally ?
If I run the code from WORD 2000 it runs at normal speed. If I run it from within Excel, it pauses for 75 secs giving a "Starting Excel" message in the Message bar of Excel, before giving the correct result !
Public Sub CreateMailMergeLabels()
Dim oApp As Word.Application
Dim oDoc As Word.Document
Set oApp = CreateObject("Word.Application"
Set oDoc = oApp.Documents.Add
With oDoc.MailMerge
oApp.Visible = True
With .Fields
oApp.Selection.TypeParagraph
.Add oApp.Selection.Range, "LabelText"
End With
Dim oAutoText As Word.AutoTextEntry
Set oAutoText = oApp.NormalTemplate.AutoTextEntries.Add("MyLabelLayout", oDoc.Content)
oDoc.Content.Delete 'Merge fields in document no longer needed now
.MainDocumentType = wdMailingLabels
.OpenDataSource Name:="C:\kennylabels\DB.xls
oApp.MailingLabel.CreateNewDocument Name:="Zweckform 3668", Address:="", _
AutoText:="MyLabelLayout", LaserTray:=wdPrinterManualFeed
.Destination = wdSendToNewDocument
.Execute
oAutoText.Delete
End With
oDoc.Close False
oApp.Visible = True
'Prevent save to Normal template when user exits Word
oApp.NormalTemplate.Saved = True
End Sub
If I run the code from WORD 2000 it runs at normal speed. If I run it from within Excel, it pauses for 75 secs giving a "Starting Excel" message in the Message bar of Excel, before giving the correct result !
Public Sub CreateMailMergeLabels()
Dim oApp As Word.Application
Dim oDoc As Word.Document
Set oApp = CreateObject("Word.Application"
Set oDoc = oApp.Documents.Add
With oDoc.MailMerge
oApp.Visible = True
With .Fields
oApp.Selection.TypeParagraph
.Add oApp.Selection.Range, "LabelText"
End With
Dim oAutoText As Word.AutoTextEntry
Set oAutoText = oApp.NormalTemplate.AutoTextEntries.Add("MyLabelLayout", oDoc.Content)
oDoc.Content.Delete 'Merge fields in document no longer needed now
.MainDocumentType = wdMailingLabels
.OpenDataSource Name:="C:\kennylabels\DB.xls
oApp.MailingLabel.CreateNewDocument Name:="Zweckform 3668", Address:="", _
AutoText:="MyLabelLayout", LaserTray:=wdPrinterManualFeed
.Destination = wdSendToNewDocument
.Execute
oAutoText.Delete
End With
oDoc.Close False
oApp.Visible = True
'Prevent save to Normal template when user exits Word
oApp.NormalTemplate.Saved = True
End Sub