Creating Multiple emails on Lotus:- Run time error '-2147023170 (800706be0':

[PChas]

Hello All.
This is my first post so firstly I'd just like to say thank you to you all for the help you've already given me by answer other peoples' questions over the year. I've searched for the solution to my latest problem and can't find the answer.

I am trying use vba to create multiple (lotus) emails with different attachments and send them to different people. The problem I am having is that after the first mail is (successfully) sent, sooner or later an error message will occur:
"Run time error '-2147023170 (800706be0':
Automation error
The remote procedure call failed."

After this Lotus Notes won't work and I need to restart my computer.
Clearly I need to remove something from the memory, or quit out of a session or something, but I don't know any more than that.


Here's my code in it's entirety:

Option Explicit
Const EMBED_ATTACHMENT As Long = 1454
Const stPath As String = "F:\Shared\All Departments\Pricing & Promotions\Pricing\New Reporting 2\Peter C\Jason"
Const stSubject As String = "Duty File"
Dim Cell As Range
Const vaCopyTo As Variant = "[EMAIL="my@email.co.uk"]my@email.co.uk[/EMAIL]"
Sub Send_Active_Sheet()
Dim stFileName As String
Dim vaRecipients As Variant
Dim WinUser As String
Dim noSession As Object
Dim noDatabase As Object
Dim noDocument As Object
Dim noEmbedObject As Object
Dim noAttachment As Object
Dim stAttachment As String
Dim a As Integer
Dim firstrow As Integer
Dim lastrow As Integer
Dim DrinkType As String
Dim vendorname As String
Dim vaMsg As String
Application.ScreenUpdating = False
For Each Cell In Range("Vendors")
    'Start the Notes session.
    Set noSession = CreateObject("Notes.NotesSession")
    'Start the actual Notes database.
    Set noDatabase = noSession.GETDATABASE("Mailwell/BCC", "mail" & WinUser & ".nsf")
    'If Lotus Notes is not open then open the mail-part of it.
    If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
    vaMsg = Range("ac2").Value
    Windows("Annual Audit S.xlsb").Activate
    vendorname = Cell.Value
    firstrow = Cell.Offset(0, 1).Value
    lastrow = Cell.Offset(0, 2).Value
    vaRecipients = Cell.Offset(0, 3).Value
    DrinkType = Range("r" & firstrow).Value
    Workbooks.Open Filename:= _
        stPath & "" & DrinkType & "" & vendorname & ".xlsb"
    'Copy the active sheet to a new temporarily workbook.
    With ActiveSheet
      .Copy
    End With
 
    stAttachment = stPath & "" & DrinkType & "" & vendorname & ".xls"
 
    'Save and close the temporarily workbook.
    With ActiveWorkbook
       .SaveAs stAttachment
       .Close
    End With
 
    WinUser = Environ("USERNAME") ' Windows username
    'Create the e-mail and the attachment.
    Set noDocument = noDatabase.CreateDocument
    Set noAttachment = noDocument.CreateRichTextItem("stAttachment")
    Set noEmbedObject = noAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment)
 
   'Add values to the created e-mail main properties.
    noDocument.Form = "Memo"
    noDocument.SendTo = vaRecipients
    noDocument.CopyTo = vaCopyTo
    noDocument.Subject = stSubject
    noDocument.Body = vaMsg
    noDocument.SaveMessageOnSend = True
    noDocument.PostedDate = Now()
    noDocument.Send 0, vaRecipients
    'Delete the temporarily workbook.
    Kill stAttachment
    Windows(vendorname & ".xlsb").Close
  
    'Release objects from memory.
    Set noEmbedObject = Nothing
    Set noAttachment = Nothing
    Set noDocument = Nothing
    Set noDatabase = Nothing
    Set noSession = Nothing
Next Cell
MsgBox "The e-mail has successfully been created and distributed", vbInformation
Application.ScreenUpdating = True
End Sub

[\code]



It falls over the second time it loops through "noDocument.Send 0, vaRecipients"

Oddly, when I shut down and sign back in it has sent the final (failed) message.



Any help would be very much appreciated.

 

[Norie]

I think you want to do the opposite actually, ie keep things in memory/connected.

To do that you need to move either all of this code, or only specific parts of it, outside the array.

Set noEmbedObject = Nothing
Set noAttachment = Nothing
Set noDocument = Nothing
Set noDatabase = Nothing
Set noSession = Nothing

 

[PChas]

Thanks for replying Norie, I think you're right about this.
It makes sense to keep "noDatabase" and "noSession" out of the loop, I'm not sure about the other two.

However, if I do this, I get a different error message (same line):
"Run-time error '462':
The remote server machine does not exist or is unavailable"

I'm running this on a citrix server (for work), so is this causing an issue?