Review Before Sending Excel Attachment in Lotus Notes

Are you still looking for a solution? Instead of NotesSession and CreateDocument, I think you'll need to use NotesUIWorkspace and ComposeDocument. As a start:

    Dim NUIWorkspace As Object
    Set NUIWorkspace = CreateObject("Notes.NotesUIWorkspace")            
    NUIWorkspace.ComposeDocument , , "Memo"

Post back if you need further help.

Create an object using CreateRichTextItem then call its EmbedObject method, specifying ActiveWorkbook.FullName along with the other required arguments.

Originally Posted by bambaataa

I'm getting an error message on the line that begins "Set Body =".... any advice?

What is the error? Always quote the exact and full error message.

Try this instead:

Option Explicit

Const EMBED_ATTACHMENT = 1454

Sub Create_UI_Email()

    Dim NUIWorkspace As Object, NUIDocument As Object, NRTattachment As Object
    
    Set NUIWorkspace = CreateObject("Notes.NotesUIWorkspace")
    NUIWorkspace.ComposeDocument , , "Memo"
    Set NUIDocument = NUIWorkspace.CurrentDocument
        
    With NUIDocument
        .FieldSetText "EnterSendTo", "email1@domain.com"
        .FieldSetText "EnterCopyTo", "email2@domain.com"
        .FieldSetText "EnterBlindCopyTo", ""
        .FieldSetText "Subject", Now & " test"
    
        .GotoField "Body"
        .InsertText "This is the email body text." & vbLf & vbLf
        
        Set NRTattachment = .document.CreateRichTextItem("Attachment")
        NRTattachment.EmbedObject EMBED_ATTACHMENT, "", ActiveWorkbook.FullName
    End With

End Sub

Originally Posted by bambaataa

1. When I run the code above, the email is created and the fields are filled out (with email1@domain.com, etc.); however, the active file is not attached. Any suggestions as to what may be going wrong?

In my tests (Notes 6.5) the file is attached, but isn't visible to the user until they send or close and reopen the email. However, I can understand why you would want the attachment to be visible in the open document as confirmation that the file has been attached, so I've spent some time investigating and writing the code to achieve this. Part of the solution involves the VBA SendKeys command, which isn't reliable so the solution might not always work for you, however it always worked in my tests.

2. Occasionally (maybe 20% of the time), I get this error message: "Run-time Error '91': Object variable or With block variable not set." The error occurs on the line ".FieldSetText "EnterSendTo", "email1@domain.com" -- any idea what might be causing this and why it only happens some of the time?

That error must mean that the NUIDocument object is undefined (Nothing). It could be caused by VBA attempting to access the new document before the Lotus Notes UI has created it. I've added a check which loops until NUIDocument is not Nothing, waiting for up to 10 seconds.

Here is the complete code:

Option Explicit

Const EMBED_ATTACHMENT = 1454

Sub Create_UI_Email()

    Dim NUIWorkspace As Object, NUIDocument As Object, NRTattachment As Object
    Dim NUIDatabase As Object
    Dim timeout As Date
    Dim docID As String
    
    Set NUIWorkspace = CreateObject("Notes.NotesUIWorkspace")
    Set NUIDatabase = NUIWorkspace.CurrentDatabase
    
    'Create a UI document
    
    NUIWorkspace.ComposeDocument , , "Memo"
    
    'Get the UI document from the workspace, waiting for up to 10 seconds
    
    timeout = Now + TimeValue("00:00:10")
    Do
        Set NUIDocument = NUIWorkspace.CurrentDocument
        If NUIDocument Is Nothing Then Application.Wait Now + TimeValue("00:00:01")
    Loop Until Not NUIDocument Is Nothing Or Now > timeout
    
    If Not NUIDocument Is Nothing Then
    
        With NUIDocument
        
            'Set email fields
            
            .FieldSetText "EnterSendTo", "email1@domain.com, email2@domain.com"
            .FieldSetText "EnterCopyTo", "email3@domain.com"
            .FieldSetText "EnterBlindCopyTo", ""
            .FieldSetText "Subject", Now & " test"
        
            'Insert email body text
            
            .GoToField "Body"
            .InsertText "This is the email body text." & vbLf & vbLf
            
            'Insert attachment - the active workbook
            
            Set NRTattachment = .Document.CreateRichTextItem("Attachment")
            NRTattachment.EmbedObject EMBED_ATTACHMENT, "", ActiveWorkbook.FullName
                    
            'Attachment doesn't appear in the UI document, although it is present in the sent or saved email.  We must close
            'the document and open it again to make the attachment visible to the user
            
            'Get the universal ID of this UI document so that we can open the same document later
            
            docID = .Document.UniversalID
            
            'Save and close this UI document
            
            .Save
            .Close
            
        End With
    
        'Close causes a Lotus Notes window to display
        '  Caption: Send Mail
        '  Do you want to send, save or discard your changes? Choose Cancel to continue editing
        '  5 Buttons: Send & Save; Send Only; Save Only; Discard; Cancel
        
        'Press v key for Save Only
        
        Application.Wait Now + TimeValue("00:00:02")
        AppActivate "Send Mail"
        SendKeys "v"
    
        'Open the newly created document again, thus making the attachment visible to the user
        
        Set NUIDocument = NUIDatabase.Database.GetDocumentByUNID(docID)
        NUIWorkspace.EditDocument True, NUIDocument

    Else
    
        MsgBox "Timeout occurred waiting for document to be created in Lotus Notes workspace"
        
    End If
    
End Sub

Again, this could be a timing issue - VBA being too fast for the Lotus Notes UI. Try adding a wait of 1 or more seconds before the GetDocument and/or EditDocument, like this:

        'Open the newly created document again, thus making the attachment visible to the user
        
        Application.Wait Now + TimeValue("00:00:01")
        Set NUIDocument = NUIDatabase.Database.GetDocumentByUNID(docID)
        Set NUIDocument = NUIWorkspace.EditDocument(True, NUIDocument)
        'NUIDocument.GoToField "Subject"
        NUIDocument.GoToField "Body"

In the above code, EditDocument is being called as a function so that GoToField can be invoked to place the cursor in one of the email fields.

I can't think of anything else which could cause the error (never happened on my system). If the wait doesn't fix it, you'll have to do some basic debugging - set breakpoints, step through the code, examine variables (e.g. verify that NUIDocument is not Nothing).