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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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