Mail merge from excel vba - select sheet problem

[jwoodland]

I have a niggling problem with automating a mail merge from an excel command button. Any help would be appreciated. I am running Office 2003. All the steps in the follwoing code work except Word propmts me to select the sheet in the workbook to merge from. I would like the process to be completely automated to prevent the user not to have anything to do with the merge.

How can I specify in the Open Data Source which sheet in the workbook the merge will be from?

Private Sub cmd_Reports_Click()

Dim MyPath As String, MyCompletePath As String

MyPath = ActiveWorkbook.Path
MyCompletePath = ActiveWorkbook.FullName

MailMerge:
fname = (MyPath & "\TestReporter_ReportsTest.doc")
Dim appWd As Word.Application
Dim WdDoc As Word.Document

Set appWd = CreateObject("Word.Application")
appWd.Visible = True
On Error Resume Next

On Error GoTo 0

With appWd
Set WdDoc = appWd.Documents.Open(MyPath & "\TestReporterMerge_NoMacro.doc")
WdDoc.Activate
WdDoc.MailMerge.OpenDataSource Name:=(MyPath & "\Test Reporter.xls"), _
ReadOnly:=True, LinkToSource:=0, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
Connection:="", SQLStatement:="", SQLStatement1:=""
With WdDoc.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute
End With
ActiveDocument.SaveAs fname
End With
WdDoc.Close
Set WdDoc = Nothing
Set appWd = Nothing
NewWindow = True
End Sub

 

[PHV]

Use a named range:
WdDoc.MailMerge.OpenDataSource Name:=(MyPath & "\Test Reporter.xls"), _
ReadOnly:=True, LinkToSource:=0, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
Connection:="[!]yourNamedRange[/!]", SQLStatement:="", SQLStatement1:=""

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886