Review Before Sending Excel Attachment in Lotus Notes

[bambaataa]

Hello,

I am trying to incorporate a piece of code that will attach an Excel file to a Lotus Notes email. I have found a code that works beautifully except for one thing: I do NOT want the email to send out automatically. I'd like the code to activate Lotus Notes and then let the user write a full message, change recipients, decide if they'd like to send it or not, etc. Surprisingly, it is not as easy as deleting the last few lines which sends the email (that seems to break the code altogether).

Could someone please help me troubleshoot this issue?

Thanks,

Evan

Please Login or Register  to view this content.

[Chippy]

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.

[bambaataa]

Very helpful, Chippy. Thanks a bunch. Any sense on how to attach the active workbook to the email once the "memo" has been created?

Thanks again,

Evan

[Chippy]

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

[bambaataa]

Sorry if this is a bit dense, but I'm not familiar with creating a rich text item or calling its EmbedObject method. Could you please help me with the code for attaching the workbook? I have named the file "DestWB" earlier in my code.

This works to create a new document, but I'm getting an error message on the line that begins "Set Body =".... any advice?

Please Login or Register  to view this content.

[Chippy]

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.

[bambaataa]

Thanks for this. I'm having two issues, though.

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

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", "[email protected]" -- any idea what might be causing this and why it only happens some of the time?

Thanks,

Evan

[Chippy]

1. When I run the code above, the email is created and the fields are filled out (with [email protected], 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", "[email protected]" -- 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.

[bambaataa]

Great!

We are 99% there. The email is created, the attachment is appended, and the email is saved as a draft. However, when the line "NUIWorkspace.EditDocument True, NUIDocument" begins to run, I get the following error message:

Run-time error '-2147417848(80010108)':
Automation error
The object invoked has disconnected from it's clients

Any ideas what is causing this? Everything is working correctly and the email is sitting in the "Drafts" folder--I just need it to open up again.

Thanks

Evan

[Chippy]

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

[bambaataa]

Very, very helpful. Thanks for all of the assistance!

[bca.khmer]

dear sir,
why it error on
Set NRTattachment = .Document.CreateRichTextItem("Attachment")
NRTattachment.EmbedObject EMBED_ATTACHMENT, "", ActiveWorkbook.FullName
that cause my mail didn't have any attachment.