Hello,

I am trying to do a Mail Mege with VB6 and MS Word 2002 which will work fine
for the first one document. However, if I select ther merge document a
second time it blows on the OpenDataSource statement with the error "The
remote server machine does not exist or is unavailable". I found several
instances of this on Google but unfortunately the solution was not posted.

TIA

Vic


Here's the code:

Private Sub mnuCoverLetter_Click()

Dim strLoginName As String
Dim strMyDocuments As String
Dim WordWasNotRunning As Boolean ' Flag For final word unload
Dim strDocument As String
Dim strWhereClause As String
Dim WordApp As Word.Application
Dim WordDoc As Word.Document
Dim oApp As Word.Application

Set oApp = CreateObject("Word.Application")

On Error GoTo ErrorHandler


Set WordApp = GetObject(, "Word.Application")
Set WordDoc = WordApp.Documents.Add(strMyDocuments & "\" & strDocument)

With WordDoc
strWhereClause = "WHERE JobID = '" & frmMain.txtJobid & "'"
ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:\Documents and Settings\vic.HOME\My Documents\My Data
Sources\Hughes_Jobs vuCoverLetter.odc" _
, ConfirmConversions:=False, ReadOnly:=False,
LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
format:=wdOpenFormatAuto, Connection:= _
"Provider=MSDASQL.1;Persist Security Info=True;Extended
Properties=""DSN=HughesSupply;Description=Hughes Supply
Jobs;UID=vic;APP=Microsoft Office
XP;WSID=VIC-XP;DATABASE=Hughes_Jobs;Trusted_Connection=Yes"";I nitial
Catalog=Hughes_Jobs" _
, SQLStatement:="SELECT * FROM ""vuCoverLetter""",
SQLStatement1:=strWhereClause, _
SubType:=wdMergeSubTypeOther
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.Destination = wdSendToNewDocument

End With

WordApp.Visible = True
WordApp.Application.Activate

Set WordApp = Nothing
Set WordDoc = Nothing
Set oApp = Nothing
Exit Sub

ErrorHandler:
MsgBox "Error: " & Err.Number & Err.Description

End Sub

I was able to elimate the "Remote Server Unavailable ..." with the following
code replacement. However, now the WHERE clause in the OpenDataSource is
ignored.

Could someone tell me why?

TIA

Vic


**Code Follows **

Dim strLoginName As String
Dim strMyDocuments As String
Dim WordWasNotRunning As Boolean ' Flag For final word unload
Dim strDocument As String
Dim strWhereClause As String
Dim WordApp As Word.Application
Dim WordDoc As Word.Document

On Error GoTo ErrorHandler

strDocument = "CoverLetter.doc"
strLoginName = "Vic"
strMyDocuments = "C:\Client Databases\Hughes Supply\Word Docs\"

Set WordApp = CreateObject("Word.Application")
'WordApp.Visible = True
Set WordDoc = WordApp.Documents.Open(strMyDocuments & "\" & strDocument)

With WordDoc
strWhereClause = "WHERE JobID = '" & frmMain.txtJobid & "'"
ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:\Documents and Settings\vic.HOME\My Documents\My Data
Sources\Hughes_Jobs vuCoverLetter.odc" _
, ConfirmConversions:=False _
, ReadOnly:=False _
, LinkToSource:=True, _
AddToRecentFiles:=False _
, PasswordDocument:="" _
, PasswordTemplate:="", _
WritePasswordDocument:="" _
, WritePasswordTemplate:="" _
, Revert:=False, _
format:=wdOpenFormatAuto _
, Connection:= _
"Provider=MSDASQL.1;Persist Security Info=True;" _
& "Extended Properties=""" _
& "DSN=HughesSupply;" _
& "Description=Hughes Supply Jobs;" _
& "UID=vic;APP=Microsoft Office XP;" _
& "WSID=VIC-XP;" _
& "DATABASE=Hughes_Jobs;" _
& "Trusted_Connection=Yes"";" _
& "Initial Catalog=Hughes_Jobs" _
, SQLStatement:="SELECT * FROM [vuCoverLetter]" _
, SQLStatement1:=strWhereClause, _
SubType:=wdMergeSubTypeOther

ActiveDocument.MailMerge.MainDocumentType = wdFormLetters

End With

WordApp.Visible = True
WordApp.Application.Activate
On Error Resume Next
Set WordApp = Nothing
Set WordDoc = Nothing

Exit Sub

ErrorHandler:
MsgBox "Error: " & Err.Number & Err.Description

A possibility: SQLStatement and SQLStatement1, when concatenated, must be a
valid SQL statement. I don't think Word will insert a space for you between
the two if there is none, so you probably need one at the beginning of
strWhereClause

Peter Jamieson

"Vic Spainhower" wrote in message
...
I was able to elimate the "Remote Server Unavailable ..." with the
following code replacement. However, now the WHERE clause in the
OpenDataSource is ignored.

Could someone tell me why?

TIA

Vic


**Code Follows **

Dim strLoginName As String
Dim strMyDocuments As String
Dim WordWasNotRunning As Boolean ' Flag For final word unload
Dim strDocument As String
Dim strWhereClause As String
Dim WordApp As Word.Application
Dim WordDoc As Word.Document

On Error GoTo ErrorHandler

strDocument = "CoverLetter.doc"
strLoginName = "Vic"
strMyDocuments = "C:\Client Databases\Hughes Supply\Word Docs\"

Set WordApp = CreateObject("Word.Application")
'WordApp.Visible = True
Set WordDoc = WordApp.Documents.Open(strMyDocuments & "\" &
strDocument)

With WordDoc
strWhereClause = "WHERE JobID = '" & frmMain.txtJobid & "'"
ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:\Documents and Settings\vic.HOME\My Documents\My Data
Sources\Hughes_Jobs vuCoverLetter.odc" _
, ConfirmConversions:=False _
, ReadOnly:=False _
, LinkToSource:=True, _
AddToRecentFiles:=False _
, PasswordDocument:="" _
, PasswordTemplate:="", _
WritePasswordDocument:="" _
, WritePasswordTemplate:="" _
, Revert:=False, _
format:=wdOpenFormatAuto _
, Connection:= _
"Provider=MSDASQL.1;Persist Security Info=True;" _
& "Extended Properties=""" _
& "DSN=HughesSupply;" _
& "Description=Hughes Supply Jobs;" _
& "UID=vic;APP=Microsoft Office XP;" _
& "WSID=VIC-XP;" _
& "DATABASE=Hughes_Jobs;" _
& "Trusted_Connection=Yes"";" _
& "Initial Catalog=Hughes_Jobs" _
, SQLStatement:="SELECT * FROM [vuCoverLetter]" _
, SQLStatement1:=strWhereClause, _
SubType:=wdMergeSubTypeOther

ActiveDocument.MailMerge.MainDocumentType = wdFormLetters

End With

WordApp.Visible = True
WordApp.Application.Activate
On Error Resume Next
Set WordApp = Nothing
Set WordDoc = Nothing

Exit Sub

ErrorHandler:
MsgBox "Error: " & Err.Number & Err.Description

Peter,

Thanks for reply ... I thought I had eliminated the error on the Remote
Server not Available but I didn't. I thought the reason for this was an
object not being set to nothing but that's not the case here.

Do you see a reason for this happening?

Thanks a lot!!

Vic






"Peter Jamieson" wrote in message
...
A possibility: SQLStatement and SQLStatement1, when concatenated, must be a
valid SQL statement. I don't think Word will insert a space for you between
the two if there is none, so you probably need one at the beginning of
strWhereClause

Peter Jamieson

"Vic Spainhower" wrote in message
...
I was able to elimate the "Remote Server Unavailable ..." with the
following code replacement. However, now the WHERE clause in the
OpenDataSource is ignored.

Could someone tell me why?

TIA

Vic


**Code Follows **

Dim strLoginName As String
Dim strMyDocuments As String
Dim WordWasNotRunning As Boolean ' Flag For final word unload
Dim strDocument As String
Dim strWhereClause As String
Dim WordApp As Word.Application
Dim WordDoc As Word.Document

On Error GoTo ErrorHandler

strDocument = "CoverLetter.doc"
strLoginName = "Vic"
strMyDocuments = "C:\Client Databases\Hughes Supply\Word Docs\"

Set WordApp = CreateObject("Word.Application")
'WordApp.Visible = True
Set WordDoc = WordApp.Documents.Open(strMyDocuments & "\" &
strDocument)

With WordDoc
strWhereClause = "WHERE JobID = '" & frmMain.txtJobid & "'"
ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:\Documents and Settings\vic.HOME\My Documents\My Data
Sources\Hughes_Jobs vuCoverLetter.odc" _
, ConfirmConversions:=False _
, ReadOnly:=False _
, LinkToSource:=True, _
AddToRecentFiles:=False _
, PasswordDocument:="" _
, PasswordTemplate:="", _
WritePasswordDocument:="" _
, WritePasswordTemplate:="" _
, Revert:=False, _
format:=wdOpenFormatAuto _
, Connection:= _
"Provider=MSDASQL.1;Persist Security Info=True;" _
& "Extended Properties=""" _
& "DSN=HughesSupply;" _
& "Description=Hughes Supply Jobs;" _
& "UID=vic;APP=Microsoft Office XP;" _
& "WSID=VIC-XP;" _
& "DATABASE=Hughes_Jobs;" _
& "Trusted_Connection=Yes"";" _
& "Initial Catalog=Hughes_Jobs" _
, SQLStatement:="SELECT * FROM [vuCoverLetter]" _
, SQLStatement1:=strWhereClause, _
SubType:=wdMergeSubTypeOther

ActiveDocument.MailMerge.MainDocumentType = wdFormLetters

End With

WordApp.Visible = True
WordApp.Application.Activate
On Error Resume Next
Set WordApp = Nothing
Set WordDoc = Nothing

Exit Sub

ErrorHandler:
MsgBox "Error: " & Err.Number & Err.Description

Hi Vic,

I am trying to do a Mail Mege with VB6 and MS Word 2002 which will work fine
for the first one document. However, if I select ther merge document a
second time it blows on the OpenDataSource statement with the error "The
remote server machine does not exist or is unavailable". I found several
instances of this on Google but unfortunately the solution was not posted.

The usual cause for the message, in my experience, is that you haven't "cleaned
up after yourself". Your code has left an orphaned pointer to something in the
Word instance you were using, before, which is blocking the second execution of
your code.

The first thing in your code I see that makes me wonder is:

Set oApp = CreateObject("Word.Application")

On Error GoTo ErrorHandler

Set WordApp = GetObject(, "Word.Application")

If you use CreateObject to set oApp you certainly don't need to set an
additional object variable (WordApp) using GetObject. Try

On Error GoTo ErrorHandler
Set WordApp = CreateObject("Word.Application")
Set WordDoc = 'etc.

Remove oApp from your declarations, and the Set to Nothing near the end.

Then: you've created a document (WordDoc) and made it into the main merge
document. Why do you then proceed to use ActiveDocument? Subsitute WordDoc
everywhere you've used ActiveDocument.

And lastly - and this is probably the cause of the problem - you need to set
the object variables to nothing in the reverse order in which they were
instantiated. So put Set WordDoc = Nothing BEFORE Set WordApp = Nothing.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail :-)

Hi Cindy,

Thank you very much for the answer, I now have it resolved. I had already
done all of the things that you mentioned except the changing
"ActiveDocument" to WordDoc and I was still getting the error. After this
final change it now works correctly.

I use this same code in VBA and I've never had this problem. Why would that
be?

Thanks again Cindy!!

Vic



"Cindy M -WordMVP-" wrote in message
news:VA.0000a3a0.0098cc55@speedy...
Hi Vic,

I am trying to do a Mail Mege with VB6 and MS Word 2002 which will work
fine
for the first one document. However, if I select ther merge document a
second time it blows on the OpenDataSource statement with the error "The
remote server machine does not exist or is unavailable". I found
several
instances of this on Google but unfortunately the solution was not
posted.

The usual cause for the message, in my experience, is that you haven't
"cleaned
up after yourself". Your code has left an orphaned pointer to something in
the
Word instance you were using, before, which is blocking the second
execution of
your code.

The first thing in your code I see that makes me wonder is:

Set oApp = CreateObject("Word.Application")

On Error GoTo ErrorHandler

Set WordApp = GetObject(, "Word.Application")

If you use CreateObject to set oApp you certainly don't need to set an
additional object variable (WordApp) using GetObject. Try

On Error GoTo ErrorHandler
Set WordApp = CreateObject("Word.Application")
Set WordDoc = 'etc.

Remove oApp from your declarations, and the Set to Nothing near the end.

Then: you've created a document (WordDoc) and made it into the main merge
document. Why do you then proceed to use ActiveDocument? Subsitute WordDoc
everywhere you've used ActiveDocument.

And lastly - and this is probably the cause of the problem - you need to
set
the object variables to nothing in the reverse order in which they were
instantiated. So put Set WordDoc = Nothing BEFORE Set WordApp = Nothing.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply
in the newsgroup and not by e-mail :-)

Hi Vic,

Thank you very much for the answer, I now have it resolved. I had already
done all of the things that you mentioned except the changing
"ActiveDocument" to WordDoc and I was still getting the error. After this
final change it now works correctly.

I use this same code in VBA and I've never had this problem. Why would that
be?

I'm not certain, but it probably has something to do with how object variables
(don't) go out of scope. Take a look again at the code you posted: you tell
VB6 to use ActiveDocument, but you don't tell it where to find ActiveDocument,
so VB6 has to guess (it should be WordApp.ActiveDocument). And in order to
make the association, it probably has to build another connection to the Word
application in the background - one to which you have no access and therefore
can't clear.

When you use ActiveDocument in Word you don't have to qualify it with the
application object. Word *knows* you mean it to work locally.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :-)

Cindy,

Thank you for your explaination - it is very clear to me now why it was
failing! I knew that it had something to do with a reference and I was using
Process Explore to attempt to see what it was but not successfully.

You help is very much appreciated.

Vic



"Cindy M -WordMVP-" wrote in message
news:VA.0000a3cb.0025c520@speedy...
Hi Vic,

Thank you very much for the answer, I now have it resolved. I had already
done all of the things that you mentioned except the changing
"ActiveDocument" to WordDoc and I was still getting the error. After this
final change it now works correctly.

I use this same code in VBA and I've never had this problem. Why would
that
be?

I'm not certain, but it probably has something to do with how object
variables
(don't) go out of scope. Take a look again at the code you posted: you
tell
VB6 to use ActiveDocument, but you don't tell it where to find
ActiveDocument,
so VB6 has to guess (it should be WordApp.ActiveDocument). And in order to
make the association, it probably has to build another connection to the
Word
application in the background - one to which you have no access and
therefore
can't clear.

When you use ActiveDocument in Word you don't have to qualify it with the
application object. Word *knows* you mean it to work locally.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :-)