MailMerge using Visual Basic ActiveX

[ref]

I am running a Mail Merge initiated from a DTS package using Visual Basic
ActiveX
The word document for the mail merge uses a datasource (.odc file) against
a sql server 2000 database.
Now if I run the mailmerge from the directory where the word doc and .odc
file reside, it works fine. If I run the DTS package that invokes the below
script locally it also runs fine, however it fails when it runs as a
scheduled job at the statement .Destination = wdSendToNewDocument in the
script below. I suspect it's an issue with referencing that datasouce. Can
someone suggest the code I would need to establish the database connection
and sql statement within the below script versus relying on the connection to
the .odc file

Thanks in advance

'************************************************************' Visual Basic
ActiveX Script
'************************************************************Function Main()

Dim WordFileTemplateName
Dim WordFileOutputName
Dim appword

WordFileTemplateName = "\\xyz\MailMerge\Hold\BillMM.doc"
WordFileOutputName = "\\xyz\MailMerge\Hold\Billout.doc"

Set appword = CreateObject("word.application")
appword.Visible = False
appword.Documents.Open WordFileTemplateName
With appword.ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
.Execute Pause=True
End With

With appword.ActiveDocument
.SaveAs(WordFileOutputName)
End With
appword.Quit (False) 'without saving
set appword = nothing
Main = DTSTaskExecResult_Success
End Function

 

[Peter Jamieson]

You are almost certainly correct that, in essence, the document has failed
to connect to the data source, but there are a few possibilities, e.g.:
a. yes, you just need to add the necessary code to connect. In this case
I'd suggest that the following method call might be enough:
appword.ActiveDocument.MailMerge.OpenDataSource _
Name:="the full path name of the .odc file"
However, you might need
appword.ActiveDocument.MailMerge.OpenDataSource _
Name:="the full path name of the .odc file", _
SQLStatement:="your SQL statement, eg. SELECT * FROM mytable"
b. however, it may be that all you need do is take account of the following
article or maybe you need (a) as well(ignore the heading, just try the
registry change if you can, unless you've done that already of course, and
see what happens):
http://support.microsoft.com/kb/825765
c. when /you/ run the script, you have the necessary permissions to access
the relevant data in the SQL Server database, but the scheduled job runs
under a login/username that does not have those permissions. In that case
you either need to change that user's permissions or find some way to run
the job under a user that does have the necessary permissions.
d. there is indeed something in the .odc that works for "you", but not for
the scheduled job. However
- I don't think this is likely
- there is no possibility of telling without seeing the content of the
..odc. You could paste it in a message here - if you do, just leave out the
stuff below "</odc:OfficeDataConnection>"

Peter Jamieson

 

[ref]

Peter
The issue was indeed that the scheduled job was not finding the .odc file as
is was residing in a folder on the network. When I put a copy of the .odc
file on the server's c drive it worked fine. I did not try it yet but I am
sure that your suggestion would be the best solution ==>
appword.ActiveDocument.MailMerge.OpenDataSource _
Name:="the full path name of the .odc file"
Thanks