Could someone help me out with the code that would be necessary to
automate the following request: Thanks
Underwriter business developer will generate Excel file........
In the Address TAB a button is being added that will contain VBA code
that will execute the following:
Count number of records (Range A7.......En)
Open Word
Create a 30 cell table much like the one attached
Insert Agency field Name
Insert Agency Address
Insert Agency City
Insert Agency State
Insert Agency Zip
Font Size 8
Format:Centered
Propagate fields to all Cells
************************************************************
Just some notes and the code that I think is breaking. It works fine
up to this point:
When executing the code that creates the mailing labels from the VBA
dev environment it works without any problems. However, when I launch
the code using the button on the "Address" worksheet it fails. I
believe the issue is that launching the code using the button results
in Excel having the focus at a key point when Word needs it. It
refuses to propagate the mailing labels when it is not the "active"
application. I have tried a couple of things to work around this,
including the snippet that is currently in the module
(wrdApp.Activate). The error occurs at the
WordBasic.MailMergePropagateLabel command. I'm hoping that you can
provide the last line I need to get this out the door.
Sub Merge_Click()
' Dim wrdSelection As Word.Selection
' Dim wrdMailMerge As Word.MailMerge
' Dim wrdMergeFields As Word.MailMergeFields
' Create name for temp data document
tmpName = "P:\Cincinnati\Downtown\Specialty\Inland Marine\P&IM
Management Report\MPG\" & _
Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) &
"_data.doc"
' Create an instance of Word, and make it visible.
Set wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True
' Add a new document.
Set wrdDoc = wrdApp.Documents.Open("P:\Cincinnati\Downtown\Specialty
\Inland Marine\P&IM Management Report\MPG\Label_Template.doc")
' Set wrdDoc = wrdApp.Documents.Add
wrdDoc.Select
Set wrdSelection = wrdApp.Selection
Set wrdMailMerge = wrdDoc.MailMerge
' Create the MailMerge Data file.
CreateMailMergeDataFile
' Continue on with MailMerge
wrdApp.Activate
wrdDoc.MailMerge.MainDocumentType = wdMailingLabels
wrdDoc.MailMerge.OpenDataSource Name:=tmpName, _
ConfirmConversions:=False, ReadOnly:=False,
LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="", SQLStatement:="",
SQLStatement1 _
:="", SubType:=wdMergeSubTypeOther
wrdDoc.Fields.Add Range:=wrdSelection.Range, Type:=wdFieldMergeField
_
, Text:="""Producer_Name"""
wrdSelection.TypeParagraph
wrdDoc.Fields.Add Range:=wrdSelection.Range, Type:=wdFieldMergeField
_
, Text:="""Producer_Address"""
wrdSelection.TypeParagraph
wrdDoc.Fields.Add Range:=wrdSelection.Range, Type:=wdFieldMergeField
_
, Text:="""Producer_City"""
wrdSelection.TypeText Text:=", "
wrdDoc.Fields.Add Range:=wrdSelection.Range, Type:=wdFieldMergeField
_
, Text:="""Producer_State"""
wrdSelection.TypeText Text:=" "
wrdDoc.Fields.Add Range:=wrdSelection.Range, Type:=wdFieldMergeField
_
, Text:="""Producer_ZIP"""
WordBasic.MailMergePropagateLabel
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
' Close the original form document.
wrdDoc.Close False
' Hide Mail Merge toolbar
wrdApp.CommandBars("Mail Merge").Visible = False
' Release references.
Set wrdSelection = Nothing
Set wrdMailMerge = Nothing
Set wrdDoc = Nothing
Set wrdApp = Nothing
' Clean up the temp file.
Kill tmpName
' Notify user we are done.
MsgBox "Mail Merge Complete.", vbMsgBoxSetForeground
End Sub
