Reply
 
Thread Tools Display Modes
  #1   Report Post  
Vic Spainhower
 
Posts: n/a
Default The remote server machine does not exist or is unavailable on Mail Merge

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


  #2   Report Post  
Vic Spainhower
 
Posts: n/a
Default

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


  #3   Report Post  
Peter Jamieson
 
Posts: n/a
Default

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




  #4   Report Post  
Vic Spainhower
 
Posts: n/a
Default

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






  #5   Report Post  
Cindy M -WordMVP-
 
Posts: n/a
Default

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 :-)



  #6   Report Post  
Vic Spainhower
 
Posts: n/a
Default

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 :-)



  #7   Report Post  
Cindy M -WordMVP-
 
Posts: n/a
Default

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 :-)

  #8   Report Post  
Vic Spainhower
 
Posts: n/a
Default

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 :-)



Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I eliminate numerous "Mail Merge..." under my tool bar? JulieMae Microsoft Word Help 2 January 9th 05 10:45 PM
Mail merge toolbar unavailable after SP3 R Scott Lowden Mailmerge 1 December 1st 04 06:24 PM
How do I mail merge when only merge document is shown in tools? DaveAS Microsoft Word Help 2 December 1st 04 03:45 PM
Mail Merge Problem Cindy M -WordMVP- Mailmerge 0 November 30th 04 05:25 PM
Using Hyperlinks in Mail Merge IF...THEN...ELSE Statements Mark V Mailmerge 8 November 30th 04 01:31 PM


All times are GMT +1. The time now is 08:43 AM.

Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"