VBA to send email using stationery and save it in sent items

HCL Domino Domino Forum
1

Hello, I’ve tried everything I can think of and scoured the web for ideas on how to resolve this.

What I want to do:

Send an excel file on our lan using an existing stationery in Lotus Notes from Excel. The stationery has a list of recipients, a subject and some text in the body. The file will be attached and sent using the stationery. Once sent I expect an entry in the sent items to show up.

We use Excel 2010 and Lotus Notes client 7.03

I have the following code does essentially what I want except that I can’t get the sent email to show up in the sent items. Actually I can get it to show up in the sent items but it creates a duplicate stationery in the Stationery view as well and if I delete the copy in the Stationery view the sent item copy is simultaneously deleted. When I view the properties I see that they both have the same NoteID.

Note: sStationeryName contains the NoteID of the stationery and it passed via another sub

Function SendUsingStationery(sStationeryName As String, sAttachmentPath As String) As Boolean

Dim nSess As NotesSession

Dim nDir As NotesDbDirectory

Dim nDb As NotesDatabase

Dim nDoc As NotesDocument

Dim nView As NotesView

Dim nEntries As NotesViewEntryCollection

Dim nViewEntry As NotesViewEntry

Dim nAtt As NotesRichTextItem

Dim x As Integer

Dim nCopyDoc As NotesDocument

'On Error GoTo ErrorHandler

Set nSess = CreateObject(“Lotus.NotesSession”) 'New:{29131539-2EED-1069-BF5D-00DD011186B7}

Call nSess.Initialize

Set nDir = nSess.GetDbDirectory(“”)

Set nDb = nSess.GetDatabase(“”, “”, False)

    Set nDoc = nDb.GetDocumentByID(sStationeryName)

    

    Set nCopyDoc = nDb.CreateDocument

    

    Call nDoc.CopyAllItems(nCopyDoc, True)

    

    With nCopyDoc

        

        .RemoveItem ("Attachment")

            

        Set nAtt = .CreateRichTextItem("Attachment")

         

        With nAtt

            Call .EmbedObject(1454, "", sAttachmentPath) '1454 = Constant for EMBED_ATTACHMENT

        End With

        

        Call .ReplaceItemValue("PostedDate", Now()) <--- This was listed on the web as a solution to make it show up in sent items but doesn't seem to help.

        .SaveMessageOnSend = True  <-- This will save the message in the sent folder but also saves a duplicate in the Stationery view. 

        .PutInFolder ("Sent")  <-- I tried this as a way to force the saved copy to be moved to the Sent Items but doesn't seem to help

        .send (False)

                    

    End With



Set nDb = Nothing

Set nSess = Nothing

Set nDir = Nothing

Set nDoc = Nothing

Set nView = Nothing

Set nCopyDoc = Nothing



SendUsingStationery = True

Exit Function

ErrorHandler:

SendUsingStationery = False

End Function

At first I was not creating a copy of te stationery and found that the attachment that I added was actually being saved in the Stationery itself in the stationery view. I also noticed that when i didn’t create a new document and copyallitems into that new document that the NoteID of the copy in the Sent folder was identical to the copy in the Stationery view. It was essentially the same file in 2 different views so any time I would send another attachment there was only 1 copy of the email in the sent items that was just perpetually being updated. Instead of multiple sent entries.

I seem to be missing somethingabout the way that Notes works here. Any advice would be greatly appreciate.

As a note, when I just go into Notes and double click on the stationery, attach a file manually and hit send, a copy of the sent item is saved in the Sent folder with a new NoteID and the original Stationery is not modified. That is what I would expect to happen from the code above but is not happening.

2

Subject: VBA to send email using stationery and save it in sent items

Part of the problem is that you are thinking of Notes documents as “files” and views/folders as “directories”. They’re not. A Notes document is more analogous to a database record or a JavaScript object (it’s really nothing more than a collection of name/value pairs when all is said and done). A view is a tabular representation of documents that is the result of a static query; any document that satisfies the view’s selection formula will appear in the view. A folder is similar to a view, but has no selection formula; documents can be arbitrarily put into folders. But that doesn’t mean they are necessarily removed from other folders they appear in; the removal is a separate action.

A document can appear in arbitrarily many views and folders, the same way that a database row can appear in arbitrarily many SQL query results. (The only real difference being that Notes views tend to be static and long-lived rather than temporary run-time constructs.) A document cannot be moved out of a view without modifying it in such a way that it no longer satisfies that view’s selection formula, or moved into a view without making it satisfy that view’s selection formula.

Most of what you are trying to do is normally handled by code that runs in the UI from events on the form. By doing it all in the back end, you are skipping all of the UI code. As you’ve noticed, if the stationery document is sent rather than saved as modified stationery, a new document is created. The new document is missing the value that tells Notes that it belongs in the stationery view, and the mail router adds the PostedDate value (provided the document is saved to the mail database) to tell Notes to select it into the Sent view.

I don’t have a copy of Domino Designer handy to tell you what values to use or what code you may be missing, but you can find most of that sort of information using the Design tab of the Propeties dialog (it’s the one with the triangle and T-square) looking for field names and values.

3

Subject: RE: VBA to send email using stationery and save it in sent items

Thank you, I think you just helped me realize where I might be going wrong.

I just remembered that there is a flag set to identify a memo as as stationery which causes it to show up in the stationery view. My current code is creating an exact copy of the stationery by using the copyallitems method including that stationery flag, which is why the copy I’m sending ends up showing in the stationery view as well. I think if I reset that flag on the new document it will fix my problem. I’ll have to try it when I get to work. Sometimes it’s not code at all, it’s just a lack of understanding how the system works.

4

Subject: VBA to send email using stationery and save it in sent items

I was able to get the code working and I thought i would post the solution in case anyone else wanted to know.

I removed the 2 field that told notes that the document was a stationery using the lines below.

This now works exactly as I want it to. A copy of the stationery is created and an mail is sent with the attached file and there is an entry shown in the sent items. And now there is no duplicate in the Stationery view.

.RemoveItem(“IsMailStationery”)

.RemoveItem(“MailStationeryName”)

5

Subject: RE: VBA to send email using stationery and save it in sent items

You weren’t getting a duplicate in the Stationery view - it was the same document. That’s why it disappeared from both places when you deleted it.