Use Word template for mail merge

[megmoo75]

I am tring to do a mail merge between an Excel file and a Word template in vbscript within a DTS package. First, I get an error when I try and compile with the code:

doc.MailMerge.OpenDataSource Name:=strPath & strDataSource

I get a VB Script compilation error saying "Expected Statement."

Am I not declaring the data source correctly? Is my syntax wrong? Any suggestions would be great appreciated.

Here is the code I have so far:

Function Main()
Dim strPath
Dim strDataSource
Dim strTemplate

Dim doc
Dim wrdApp

Set wrdApp = CreateObject("Word.Application&quot

strPath = "C:\MailingLabel\"
strTemplate = "MailingLabelTemplate.dot"
strDataSource = "Address.xls"

' Start Word using mailmerge template
Set doc = wrdApp.Documents.Add(strPath & strTemplate)

'CreateDataDoc strDataSource

' Do the mail merge to a new document.
doc.MailMerge.OpenDataSource Name:=strPath & strDataSource
doc.MailMerge.Destination = wdSendToNewDocument
doc.MailMerge.SuppressBlankLines = True

doc.DataSource.FirstRecord = wdDefaultFirstRecord
doc.DataSource.LastRecord = wdDefaultLastRecord

If docMailMerge.State = wdMainAndDataSource Then
doc.MailMerge.Execute
End if

' Display the mail merge document
wrdApp.Visible = True

Set doc = Nothing
Set wrdApp = Nothing

Main = DTSTaskExecResult_Success
End Function

 

[PHV]

You can't use named parameters in VBScript. You can try this:

doc.MailMerge.OpenDataSource strPath & strDataSource

Hope This Help
PH.

 

[megmoo75]

That worked for that step - thank you so much!!!!

However, now I'm getting an error on the lines:

doc.DataSource.FirstRecord = wdDefaultFirstRecord
doc.DataSource.LastRecord = wdDefaultLastRecord

The error is:
Object doesn't support this property or method: 'doc.DataSource'

Any ideas?

 

[megmoo75]

OK, I think I found the problem with the lines I identified in case anyone references this thread. It should be:

doc.MailMerge.DataSource.FirstRecord = wdDefaultFirstRecord
doc.MailMerge.DataSource.LastRecord = wdDefaultLastRecord

I'm not sure everything is working as it should, but this cleared up the errors I was getting.

 

[megmoo75]

Figured I'd update the thread with the script that I got to work in case anyone else has the same problem. I replaced the named constants with the actual values for those named constants since I couldn't get the reference to the named constants to work.


Function Main()
Dim strPath
Dim strDataSource
Dim strTemplate

Dim doc
Dim wrdApp

Set wrdApp = CreateObject("Word.Application&quot


strPath = "C:\MailingLabel\"
strTemplate = "MailingLabelTemplate.dot"
strDataSource = "Address.xls"

'Start Word using mailmerge template
Set doc = wrdApp.Documents.Add(strPath & strTemplate)


'Do the mail merge to a new document.
doc.MailMerge.OpenDataSource strPath & strDataSource

'checking value of named constant: wdSendToNewDocument
doc.MailMerge.Destination = 0

doc.MailMerge.SuppressBlankLines = True

'setting to values of named constants: wdDefaultFirstRecord and wdDefaultLastRecord
doc.MailMerge.DataSource.FirstRecord = 1
doc.MailMerge.DataSource.LastRecord = -16

'checking value of named constant: wdMainAndDataSource
If doc.MailMerge.State = 2 Then doc.MailMerge.Execute


'Do not display the mail merge document
wrdApp.Visible = False

'Save mail merge document
wrdApp.ActiveDocument.SaveAs strPath & "LabelsSuccess.doc"

Set doc = Nothing
Set wrdApp = Nothing

Main = DTSTaskExecResult_Success
End Function

 

[PHV]

I will add the following line after the SaveAs:

wrdApp.Quit