How do I get fix the 462 error: Remote Server Machine does not exist - Outlook/Excel 2003 VBA

OS: Windows XP SP2
Office: 2003 Professional

Hi,

I have a macro in outlook that will take the selected mail, request some use input, and use this to find the destination directory and filename to save this mail as, it will also add a sequential number to the filename taken from an excel correspondence log, and record the details of the email in said log.

It works fine when I run it, when I try to run it again it errors out with:
"Run-time Error 462:  The Remote Server machine does not exist or is not available"

I thought this was something to do with Excel.exe process remaining running after I had run the macro first time, but I added in a shell call to a batch file that kills off the excel process, to see if this was the case, and that makes no difference.

Here's the code snippet

(It bugs out on the 63rd line:)



Sub SaveFile()
Shell "C:\KillExcel.bat"
Dim objExcel As New Excel.Application
Dim AppExc As Excel.Application
Dim theSel As Outlook.Selection, _
    itm As MailItem, _
    itmAttachment As Attachment, _
    strFilename As String
Dim ProjectNo As String
Dim Type1 As String
Dim Disc As String
Dim SeqNo As String
Dim SeqNo1 As String
    Dim Logfile1 As String
    Dim Logfile0 As String
    Dim Logfile2 As String
    Dim Logfile3 As String
    Dim Filename As String
    Dim Date1 As String
    Dim From1 As String
    Dim to1 As String
            
        
 
 
 
    Logfile = ""
    Logfile1 = InputBox("Project Number?")
    Logfile0 = InputBox("WBS Code?")
    Logfile2 = InputBox("Incoming, Internal or Outgoing?", , "Incoming, Internal or Outgoing")
    Disc = InputBox("2 digit discipline code?")
    Filename = Logfile1 & " " & Logfile2 & " " & "Correspondence Register.xls"
    Logfile3 = "M:\Projects\" & Logfile1 & "-" & Logfile0 & "\1.00.000 Project Management\1.00.030 CORRESPONDENCE\" & Logfile2
    Logfile = Logfile3 & "\" & Filename
    
        
    ProjectNo = ""
    ProjectNo = Logfile1
    If Logfile2 = "Incoming" Then
        Type1 = "IN"
        Else
        If Logfile2 = "Outgoing" Then
        Type1 = "OUT"
        Else
        Type1 = "INT"
    End If
    End If
    
 
    objExcel.Workbooks.Open Logfile
    
    
    Dim nRow As String
    
    nRow = 6
    
Location1:
    nRow = nRow + 1
    nCell = "B" & nRow
    
 
    objExcel.Sheets("Sheet1").Select
    Range(nCell).Select
    If ActiveCell.Value = "" Then
    lRow = nRow - 1
    GoTo Location2
    Else
    GoTo Location1
    End If
 
Location2:
 
    lCell = "B" & lRow
    Range(lCell).Select
    SeqNo1 = ActiveCell.Value
    SeqNo = SeqNo1 + 1
    lRow = lRow + 1
    lCell = "B" & lRow
     
 
    Set theSel = Application.ActiveExplorer.Selection
    If theSel.Count = 0 Then
        Exit Sub
    Else
        If theSel.Count > 1 Then
        Exit Sub
    Else
        For Each itm In theSel
        
            'Taking subject out and removing any unusable character's from it.
            Range("Z99").Select
            ActiveCell.FormulaR1C1 = ";:\"
            Range("Z98").Select
            ActiveCell.FormulaR1C1 = """"
            Range("Z97").Select
            ActiveCell.FormulaR1C1 = "'/"
            Range("Z100").Select
            ActiveCell.FormulaR1C1 = itm.Subject
            Range("Z97:Z100").Select
            Cells.Replace What:=":", Replacement:=" ", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            Cells.Replace What:=";", Replacement:=" ", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            Cells.Replace What:="/", Replacement:=" ", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            Cells.Replace What:="\", Replacement:=" ", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            Cells.Replace What:="""", Replacement:=" ", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
                Cells.Replace What:="'", Replacement:=" ", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            Range("Z100").Select
            Subject1 = ActiveCell.Value
            
            'Back to mail program
            From1 = itm.SenderName
            to1 = itm.To
            Date1 = itm.SentOn
                        
            strFilename1 = ProjectNo & "-" & Type1 & "-" & Disc & "-" & "0" & SeqNo & "__" & Subject1 & ".msg"
            strFilename = Logfile3 & "\" & strFilename1
            itm.SaveAs strFilename, olMSG
 
        Next
        Set Item = Nothing
        Set theSel = Nothing
    End If
    End If
    
    Range(lCell).Select
    ActiveCell.Offset(0, -1).Select
    ActiveCell.FormulaR1C1 = ProjectNo & "-" & Type1 & "-" & Disc & "-"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = SeqNo
    rCell = "F" & lRow
    Range(rCell).Select
    ActiveCell.FormulaR1C1 = strFilename1
    Range(lCell).Select
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = Date1
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = From1
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = to1
ActiveWorkbook.Save
 
 
objExcel.Workbooks.Close
Set AppExc = Nothing
Shell "C:\KillExcel.bat"
 
 
 
 
 
 
End Sub

Open in new window

LVL 2
Conor_NewmanAsked:
Who is Participating?
 
Rory ArchibaldConnect With a Mentor Commented:
When automating Excel (or other Office apps) from another application, you should never use unqualified references to objects like ActiveSheet or Range. Try using this code instead (after ensuring all Excel.exe instances have been killed)
Regards,
Rory
Sub SaveFile()
'   Shell "C:\KillExcel.bat"
   Dim objExcel As New Excel.Application
   Dim wbk As Excel.Workbook
   Dim wks As Excel.Worksheet
   Dim theSel As Outlook.Selection, itm As MailItem, itmAttachment As Attachment
   Dim ProjectNo As String, strFilename As String
   Dim Type1 As String
   Dim Disc As String
   Dim SeqNo As String
   Dim SeqNo1 As String
   Dim Logfile1 As String
   Dim Logfile0 As String
   Dim Logfile2 As String
   Dim Logfile3 As String
   Dim Filename As String
   Dim Date1 As String
   Dim From1 As String
   Dim to1 As String
            
    Logfile = ""
    Logfile1 = InputBox("Project Number?")
    Logfile0 = InputBox("WBS Code?")
    Logfile2 = InputBox("Incoming, Internal or Outgoing?", , "Incoming, Internal or Outgoing")
    Disc = InputBox("2 digit discipline code?")
    Filename = Logfile1 & " " & Logfile2 & " " & "Correspondence Register.xls"
    Logfile3 = "M:\Projects\" & Logfile1 & "-" & Logfile0 & "\1.00.000 Project Management\1.00.030 CORRESPONDENCE\" & Logfile2
    Logfile = Logfile3 & "\" & Filename
    
        
    ProjectNo = ""
    ProjectNo = Logfile1
    If Logfile2 = "Incoming" Then
        Type1 = "IN"
        Else
        If Logfile2 = "Outgoing" Then
        Type1 = "OUT"
        Else
        Type1 = "INT"
    End If
    End If
    
 
    Set wbk = objExcel.Workbooks.Open(Logfile)
    
    
    Dim nRow As String
    
    nRow = 6
    
Location1:
    nRow = nRow + 1
    nCell = "B" & nRow
    
 
    Set wks = wbk.Sheets("Sheet1")
    If wks.Range(nCell).Value = "" Then
    lRow = nRow - 1
    GoTo Location2
    Else
    GoTo Location1
    End If
 
Location2:
 
    lCell = "B" & lRow
 
    SeqNo1 = wks.Range(lCell).Value
    SeqNo = SeqNo1 + 1
    lRow = lRow + 1
    lCell = "B" & lRow
     
 
    Set theSel = Application.ActiveExplorer.Selection
    If theSel.Count = 0 Then
        Exit Sub
    Else
        If theSel.Count > 1 Then
        Exit Sub
    Else
        For Each itm In theSel
        
            'Taking subject out and removing any unusable character's from it.
            With wks
               .Range("Z99").FormulaR1C1 = ";:\"
               .Range("Z98").FormulaR1C1 = """"
               .Range("Z97").FormulaR1C1 = "'/"
               .Range("Z100").FormulaR1C1 = itm.Subject
               With .Range("Z97:Z100")
                  .Cells.Replace What:=":", Replacement:=" ", LookAt:=xlPart, SearchOrder:=xlByRows, _
                     MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
                  .Cells.Replace What:=";", Replacement:=" ", LookAt:=xlPart, SearchOrder:=xlByRows, _
                     MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
                  .Cells.Replace What:="/", Replacement:=" ", LookAt:=xlPart, SearchOrder:=xlByRows, _
                     MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
                  .Cells.Replace What:="\", Replacement:=" ", LookAt:=xlPart, SearchOrder:=xlByRows, _
                     MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
                  .Cells.Replace What:="""", Replacement:=" ", LookAt:=xlPart, SearchOrder:=xlByRows, _
                     MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
                  .Cells.Replace What:="'", Replacement:=" ", LookAt:=xlPart, SearchOrder:=xlByRows, _
                     MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
               End With
                Subject1 = .Range("Z100").Value
            End With
            'Back to mail program
            From1 = itm.SenderName
            to1 = itm.To
            Date1 = itm.SentOn
                        
            strFilename1 = ProjectNo & "-" & Type1 & "-" & Disc & "-" & "0" & SeqNo & "__" & Subject1 & ".msg"
            strFilename = Logfile3 & "\" & strFilename1
            itm.SaveAs strFilename, olMSG
 
        Next
        Set Item = Nothing
        Set theSel = Nothing
    End If
    End If
    
    With wks.Range(lCell)
      .Offset(0, -1).FormulaR1C1 = ProjectNo & "-" & Type1 & "-" & Disc & "-"
      .FormulaR1C1 = SeqNo
      .Offset(0, 1).FormulaR1C1 = Date1
      .Offset(0, 2).FormulaR1C1 = From1
      .Offset(0, 3).FormulaR1C1 = to1
   End With
    rCell = "F" & lRow
    wks.Range(rCell).FormulaR1C1 = strFilename1
   wbk.Save
   wbk.Close False
   objExcel.Quit
   Set wks = Nothing
   Set wbk = Nothing
   Set objExcel = Nothing
End Sub

Open in new window

0
 
Conor_NewmanAuthor Commented:
Absolutely Perfect, thanks a million.
0
 
Conor_NewmanAuthor Commented:
Perfect solution.  Thank you very much. :) Works like a charm now.
0
Free tool for managing users' photos in Office 365

Easily upload multiple users’ photos to Office 365. Manage them with an intuitive GUI and use handy built-in cropping and resizing options. Link photos with users based on Azure AD attributes. Free tool!

 
Rory ArchibaldCommented:
Glad to help - thanks for the grade.
Rory
0
 
RiazUlHassanCommented:
Thank alot mate, i was looking into this problem from two days, and I have found the very right solution here.
0
 
britavaCommented:
I have got the same error message at following code:
error 426

Public bufferItem As Outlook.ContactItem

Set bufferItem = Outlook.CreateItem(olContactItem)

in outlook 2007
this problem is not occurring on all machines
0
All Courses

From novice to tech pro — start learning today.