The remote server machine does not exist or is unavailable on Mail Merge

V

Vic Spainhower

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"";Initial
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
 
V

Vic Spainhower

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
 
P

Peter Jamieson

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
 
V

Vic Spainhower

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 said:
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 said:
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
Click to expand...
Click to expand...
 
C

Cindy M -WordMVP-

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.
Click to expand...
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 :)
 
V

Vic Spainhower

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
 
C

Cindy M -WordMVP-

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?
Click to expand...
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 :)
 
V

Vic Spainhower

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

The remote server machine does not exist or is unavailable (Error 462) 3
Error 462 - Remote server machine does not exist or is unavailable 12
The RPC server is unavailable. (Exception from HRESULT: 0x800706BA 0
Word Automation Error 462 The remote server machine does not exist or is unavailable 1
Runtime Error 462 with PreferredWidth (table) 2
Jonathan west - Word 2000 still does not print 2
Programmed (VBA) mail merge not working in Office 2007 0
Mail Merge - Albert D. Kallal 2

Top