Sending Lotus/IBM Notes email from Excel VBA - embedded chart image and attachment, specify From address

Question

I am new to a Notes environment, so I've spent a lot of time reading here and other forums in an attempt to learn how to VBA an email via Lotus/IBM Notes.

There seems to be 2 main approaches; I am using the NotesUI method, since one of the requirements for the email is to embed an image of a portion of the Excel worksheet, as well as attaching the file itself.

At this stage, I have functioning code which achieves this (more often than not!) from the email address of the person who runs the macro. I can claim no credit for this - it has been borrowed with gratitude from the web.

However, the team has a shared email account, from which I wish to send the email.

I have seen some discussion of Principal and being able to specify a FromName, but I've so far been unable to achieve this last part.

The code I am using to successfully send an email with an attachment and an image of a section of my worksheet is below - and tips on how to modify the existing code to send from another email address would be most welcomed!

    Sub SendEmbedMail(mailTo As String, stSubject As String, _
        copyData As Range, Optional msgBeforeEmbed As String, _
        Optional msgAfterEmbed As String, Optional attachFile As Workbook)

    Dim Notes As Object, db As Object, WorkSpace As Object
    Dim UIdoc As Object, UserName As String, MailDbName As String
    Dim AttachMe As Object, EmbedObj As Object

    'Create & Open New Document
    Set WorkSpace = CreateObject("Notes.NotesUIWorkspace")
    Call WorkSpace.COMPOSEDOCUMENT(, , "Memo")
    Set UIdoc = WorkSpace.CURRENTDOCUMENT
    Call UIdoc.inserttext(mailTo)
    Call UIdoc.gotofield("Body")
    Call UIdoc.inserttext(msgBeforeEmbed)
    copyData.CopyPicture
    Call UIdoc.Paste
    Call UIdoc.inserttext(msgAfterEmbed)
    Call UIdoc.gotofield("Subject")
    Call UIdoc.inserttext(stSubject)
    If Not attachFile Is Nothing Then
        Set AttachMe = UIdoc.Document.CreateRichTextItem("Attachment")
        Set EmbedObj = AttachMe.EmbedObject(1454, vbNullString, _ 
                  attachFile.FullName, "Attachment")
    End If
    Call UIdoc.Send(0, mailTo)

    End Sub

Answer 1

Writing the document directly to the server's mail.box file is often used as a solution for this. See the first answer to this question, and look at the code of the OpenNTF Team Mailbox project for more details. Doing this generally won't completely remove the sender's info from the message, though. (It may vary depending on the Notes and Domino versions, but Notes has never been fully cooperative with spoofing attempts done this way.)

If you want to completely hide the identity of the sender, the approach that works best is to use an agent running on the server to send the message - or in your case, to re-send it. The agent can be signed by a generic Notes id instead of the developer's or the server's id, so by the time the actual send to the actual recipient occurs, the end-user isn't part of the process. In your case, you could accomplish that by creating a mail-in database and changing your VBA code from Call UIdoc.inserttext(mailTo) to Call UIdoc.inserttext("My mail-in database name goes here") but you'll also have to put your mailTo value somewhere, otherwise the agent won't know where to re-send it. You can do that by adding a line of code like this:

Call UIdoc.Document.ReplaceItemValue("actualRecipient",mailTo)

Your agent can be set up to run after new mail arrives in the mail-in database, and it can then clean up the message by removing the From and ReplyTo and INETFrom (if they exist - see here) items, and setting the SendTo and Principal and INETFrom fields. Omitting the basic framework code for the agent (some examples here) and assuming that doc is the variable that contains the NotesDocument that you are re-sending, the actual working part of the agent could be similar to this:

doc.RemoveItem("From")
doc.RemoveItem("InetFROM")
doc.RemoveItem("ReplyTo")
if doc.hasItem("actualRecipient") then
  doc.ReplaceItemValue("SendTo",doc.actualRecipient(0))
  doc.RemoveItem("actualRecipient")
else
' here you'll want to do something sensible if a message arrives in the mail-in
' database but it doesn't have an actualRecipient; i.e., it wasn't sent by your
' VBA code!
End if
Call doc.ReplaceItemValue("Principal","support@company.com@Your Notes Domain Goes Here <support@company.com>")
Call doc.ReplaceItemValue("INETFrom", "support@company.com")
Call doc.Send()

Answer 2

You could also do this using back-end classes (not UI). I wrote a class to help with creating emails, it includes changing the from-address to anything you like: http://blog.texasswede.com/lotusscript-mail-notification-class/

You just have to add a method to insert a picture in the text, you can use the approach described by Thomas Hampel at http://blog.tomcat2000.com/blog/tomcat2000.nsf/dx/notesapi-import-pictures-into-richtext-fields-using-backend-classes.htm.