DTS Mailmerge

[ref17]

I need to extract data from a sql server database and put the results via a mail merge into a Word document. Is this something that can be automated within a DTS package. I have been trying to look for some examples over the net without any luck

 

[AlexCuse]

Do you have a mail merge document saved somewhere, with the proper source queries and everything set up?

If so, I have some code written in Access VBA that could be converted to VBScript very easily. Let me know.

Alex

Ignorance of certain subjects is a great part of wisdom

 

[ref17]

Not as of yet. I was just researching to get examples to help me to determine what the best approach would be and if using DTS would be part of the solution.

 

[AlexCuse]

As long as you have a mail merge document (or a few if you want to choose one programmatically) set up, I think DTS would be a solid option. Let me know what you decide to do.

Alex

Ignorance of certain subjects is a great part of wisdom

 

[ref17]

Well I created a word mail merge today. It connects to a sql server database, runs the appropriate sql and I get thr result that I am looking for. The issue is how do I automate this. I thought a DTS package would be a possible solution... I need some help, not sure how to proceed

 

[AlexCuse]

Just post back here if you don't hear from me by noon tomorrow. I have some code that will help you automate, but can't post right now (gotta get home!)

Talk to you in the AM

Alex

Ignorance of certain subjects is a great part of wisdom

 

[ref17]

alex, if you have a sample script that would be great

 

[AlexCuse]

Thanks for the reminder I am going to test it in a package real quick, just to make sure it works in VBScript. Back in a couple.

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Here you go dude. SOrry it took so long, had to mess with it a bit to change from Access VBA.

One note - You need to modify your registry to automate any mail merges with word 2003. Here is a link on that:

http://support.microsoft.com/default.aspx?scid=KB;EN-US;825765

Also, I have only tried running this with a PC initiating the package (read - I have not scheduled this as a job). If you are calling it from an app you should be fine.

Without further ado,

Function Main()

Dim WordFileTemplateName    'As String
Dim WordFileOutputName    'As String
Dim appword 'As Object

WordFileTemplateName = "s:\somedir\mailmerge.doc"
WordFileOutputName = "c:\test.doc"

    Set appword = CreateObject("word.application")
    appword.Visible = True
    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

You will need to use this from an ActiveX Script task of course.

Hope it helps,

Alex



Ignorance of certain subjects is a great part of wisdom

 

[ref17]

Thanks.....
I gave it a try and I got the following error
error code:0
error source=microsoft word
error description: requested object is not available
error on line 18
.Destination = wdSendToNewDocument

 

[AlexCuse]

Did you modify your registry? (might need to do it on Server, although I don't think we have...)

It sounds like you are getting the SQL warning, and then the doc is not opened in mail merge mode.

Ignorance of certain subjects is a great part of wisdom

 

[ref17]

well if I need to chg the registry to get this to work then I am out of luck 'cause the admin's won't do or allow that. On to finding another solution
Thanks for your time and help

 

[AlexCuse]

You should ask, it is a very minor change. I did not think I'd be able to get it approved but they said no problem. It only affects MS Word.

Also, you are not going to be able to automate a mail merge document through any means without making the change.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[ref17]

Ok, I made a change to the registry on my local machine to at least do a proof of concept. I ran the package from my local machine. Now it created the file ok, however when the package runs it opens word, does it's thing and then closes word before ending the package. Just concerned when this run from the server the opening and closing of word might be an issue.

 

[AlexCuse]

I think your DBA might be able to help with that. I have never used that code in a scheduled job, so I can't really offer any more. One thing you could consider I guess is setting up a batch file (to run on a server that does have word installed) that calls the DTS?

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom