Sending multiple emails through Lotus Notes from Excel VBA

I had done some extensive searching myself.  The links you mentioned, unfortunately do not addess my questions.

Ok, it seems that you are calling a UIDOC to open and paste clipboard contents into a Notes Rich Text field, rather than building the mail document from the back end, and attaching or embedding object.

That will cause the paste to be a picture rather than an Excel Object that can be opened and edited.

Also, you have to close the uidoc :)

when you set:

 Set oDoc = uiDoc.DOCUMENT

       Call oDoc.Save(True, True)

you can also set: uidoc.close(true), to close the open document.  The "true" will force the close.

However, you might get a "Do you want to Save" message in the UI, since you also have to manipulate a "SaveOptions" field and set it to "0" before you call the close, and then delete the field after the document is closed, else, subsequently opening and editing the document will prevent users from saving it.

I am attempting to send out Pay statements to the employee email addresses.  I setup a template in Excel to accomplish this task.  The request has been to display the info as a rich text object in the Body rather than as an attachment.  I'm not sure that my code is the best approach.  I would appreciate some feedback.  It appears I only have Excel to work with and Lotus Notes, but if there is a better, cheap solution, I'm game.  Maybe I should just use the same memo, and do a clear before each paste. What do you think?

Can't seem to get the Save prompt to disappear.   I do not want a copy saved in my inbox.

Call uiDoc.Save
Set oDoc = uiDoc.DOCUMENT
oDoc.saveOptions = "0"
oDoc.send
Call uiDoc.Close(True)
??? Delete field

If you are starting with Excel there should be no need to go through Lotus Notes. Instead why not get Excel to email each employee directly. You should be able to find what you need to do that in this link:

http://www.rondebruin.nl/sendmail.htm

In the end, all the examples use some sort of mail client, usually refers to Outlook.  We only have Lotus Notes installed.  

OK, understood. Back to the drawing board!

Thanks, that was helpful.  I suppose the Paste as Special (rich text) is also not available.  I don't know a lot about Lotus Notes, but is it possible to do what I am attempting, in Lotus Notes?  
1.   Create recordset using ODBC  
2.   Populate a Lotus Memo / Form (Template)
3.   Send email to recipient
or ...
Is it possible for Lotus Notes to scan for a particular Subject line and do it's own processing, ie. copy / paste attachment into body and then send to recipient

Thanks
 

You can do just about everything you mentioned, except embedding an object and have it appear the way you want.  Notes is very flexible.  This is just one of the frustrating situations where it will not do exactly what you want, but there is always a work-around...

If you're using Notes R6 or later, you could use VBA to re-create the spreadsheet as an HTML table using MIME or RichText (just like creating a report from Excel using VBA).  I prefer this way over using the embedded object (even if we could control the display), because you have complete control over the appearance of the email.  Of course, if you need the table to function as a spreadsheet (ie: perform computations), this workaround is not for you.  But if you just need a static report, and the spreadsheet is in a somewhat standard format, this could be what you're looking for.

Bill, essentially true.   The problem is exporting a range in excel as a file that can be used to embed.   There is no direct way to do it, and the copy cells>>paste, is a clever solution if you can get the UIDOC calls to work in Excel VB.   They don't seem to be available :)

To use the embed object, you have to be a bit more clever with Excel, and grab the picture and paste that picture into a CHART, which you can then save to a file in Excel VB.  (I can be wrong, this is TOL).  Once you have a saved file, you can use that file to embed object as a picture using the 1453 call.

Popluating a richtext item using the table and formatting is another way to go, as Bill explains.

I'm thinking another way might be to construct a HTML file via a TEXT file in Excel VB, and then embed that, assuming the richtext paragraph style is available via COM.

Would have to test to be sure.  :)  Whatcha think?

I didn't know that about the chart workarond.  Thanks marilyng!  I still prefer to stay in the backend, though.

If I go with the copy / paste method, how do I handle the loop?  Can I send the message, clear the body, setup the next message, send that message etc.  without having the SEND .. SAVE  message appearing.

Just one of the reasons I try to avoid the front-end workaround.

You can prevent the save/send message, however, by saving the uidoc, setting doc.SaveOptions to "0", then calling uidoc.Close().  Just don't forget to remove the SaveOptions field before calling the backend Send function.

Ok, I'm a few posts behind now, in order for it to stay totally in the back end you either have to create the table in the rich text field, or paste the selection into a Chart and export the chart as an image.  Then you can   embed the exported file into the Notes Rich Text field without having to call UIDOC.

Either way, your current solution using the UIDOC with a paste function, needs a beefy computer :)

May we ask what you have in the excel file that can't be totally transferred into a Notes Database?  Sounds like you're using the excel file as a database, anyway. :)

I am creating Pay Statements that need to be mailed to each employee.  The Excel file reads the Payroll Database (an outside system), and creates a sheet formatted for email purposes.  The employees can of course have different email clients.  I had also looked at creating a PDF file and sending that.  I only have Lotus Notes available for emailing.  I could possible get access to do some development in the Lotus side of things.  Any thoughts?  Thanks.    

If it was me, I would try marilyng's chart workaround.  If I was not happy with that, I would parse the Excel sheet and create my own table using MIME.

I did some more testing and found that the Copy and Paste method created some fairly huge files.  I decided to create PDF files and send them as an attachment.  It's slower but appears to work.  I appreciate both your comments.  Most likely my initial post was not a good universal approach when dealing with different email clients.  However, I would like to award the points to both of you.  Question is, which posts do I mark as the solution?  Thanks.