Need to have an excel sheet in a new notes document

antibia used Ask the Experts™
In a view i have a "compose new document" button that, by now, uses the @command([compose]) command. In a ritch text field i need a predefined excel spreadsheet (preferably in another document but an external file would be fine as well). Anyone got an idea how i can accomplish this?

I'm using Notes 5.0.7, native client, Win32

Thanks in advance,
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I have a ready made code for it at my office once i go there i will paste it okay ?

If there is anyone with code before that then they are lucky to bag your points.


Does it have to be in a rich text field?  Because what I do is in the design of my form I choose INSERT/OBJECT/EXCEL WORKSHEET and then whenever somebody creates a document using this form they will see the predefined worksheet and can edit it but it is not in a rich text field, it sits right on the form.
Ok i am at office and here comes my code.  This code brings a view of documents with attachments.  So a selection is made on which attachment is to be made.  This attachment is used to be populated in the new document and presented in the mailbox ready to be mailed.

Instead of the mailbox routine you could use your own db, form and documents...

Here it comes.
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

b := b;
Filepath := @LeftBack(@Subset(@DbName ; -1); "\\") + "\\" + "purchaserequests.nsf";
a := @Prompt([YESNO];"Confirm :"; "Do you want to attach templates to this mail ?");
@If( a = 1 ;
@Set("a" ; @PickList( [Custom] : [Single] ; @Subset(@DbName ; 1) : Filepath ; "(StandardTemplates)" ; "Template Selection" ; "Select a template type from the list below" ; 2 ));
@SetEnvironment("TemplateDb" ; FilePath);
@SetEnvironment("TemplateUNID" ; a ) ;
@PostedCommand([ToolsRunMacro] ; "(SendSupplierMail)")
           ); @PostedCommand([ToolsRunMacro] ; "(SendMail)")

This is the button code.  the filepath will be the one from which the attachment will be pulled from. In my case its another database.

TemplateUNID will be the unique ID of the selected document from the piclist
Here comes the sendsuppliermail agent that will attach the attachment. Again you gotto modify the mail form and mail db stuff according to your need.
Dim ss As NotesSession
Dim ws As NotesUIWorkspace
Dim curdb As NotesDatabase
Dim maildb As NotesDatabase
Dim addbook As NotesDatabase

Dim addbookview As NotesView

Dim dc As NotesDocumentCollection
Dim persondoc As NotesDocument
Dim doc As NotesDocument
Dim maildoc As NotesDocument

Dim uidoc As NotesUIDocument

Dim un As NotesName

Dim server As String
Dim filepath As String
Dim mailserver As String

Set ss = New NotesSession
     Set ws = New NotesUIWorkspace
     Set un = New NotesName(ss.UserName)
     Set curdb = ss.CurrentDatabase
     server = curdb.Server
     AttachFile = ""
     TemplatedbName = ss.GetEnvironmentString("TemplateDb")
     TemplateUNID = ss.GetEnvironmentString("TemplateUNID")
     TemplateName = ss.GetEnvironmentString("TemplateName")
     If Trim(TemplatedbName) = "" Or Trim(TemplateUNID) = "" Or Trim("TemplateName") = "" Then
          AttachFile = "No"
          AttachFile = "Yes"
     End If    
     If AttachFile = "Yes" Then
          Dim templdoc As NotesDocument
          Dim templdb As New NotesDatabase(server , TemplatedbName )
          Dim fileitem As NotesItem    
          Set templdoc = templdb.GetDocumentByUNID(TemplateUNID)
          If (templdoc Is Nothing) Then
               Msgbox "Please select a template using 'Select Template' action button"
               Exit Sub
          End If
          Set fileitem = templdoc.GetFirstItem("Files")    
     End If    
     Set dc = curdb.UnProcessedDocuments
     If dc.Count <= 0 Then
          Print "No documents selected"
          Exit Sub
          Set doc = dc.GetFirstDocument
          ctr = 0
          While Not doc Is Nothing              
               Redim Preserve Recipients(ctr)
               Recipients(ctr) = doc.Email(0)                            
               ctr = ctr + 1
               Set doc = dc.GetNextDocument(doc)
     End If
     server_file = Evaluate(|@MailDbName|)
     mailserver = server_file(0)
     filepath = server_file(1)
     Set maildb = New NotesDatabase( mailserver , filepath )
     Set maildoc = maildb.CreateDocument    
     maildoc.Form = "Memo"
     maildoc.SendTo = Recipients
     If AttachFile = "Yes" Then
          Dim bodyitem As New NotesRichTextItem( maildoc , "Body")
          Call bodyitem.AppendRTItem(fileitem)
          Flag = maildoc.ComputeWithForm(True , False)
          If flag Then
               Call maildoc.Save(True , False)    
               univ = maildoc.UniversalID
               Delete maildoc
          End If
     End If
     Set maildoc = maildb.GetDocumentByUnid(univ)
     Set uidoc = ws.EditDocument( True , maildoc )  
     Call uidoc.GotoField("Subject")
     Call uidoc.Reload

Here is an idea which can be implemented into the view action or agent

Sub Click(Source As Button)
     Dim s As New NotesSession    
     Dim ws As New NotesUIWorkspace
     Dim uidoc As NotesUIDocument
     Dim rt As NotesRichTextItem
     Set uidoc = ws.ComposeDocument( "", "", "EXCEL FORM" )
     uidoc.GotoField( "RT" ) ' The name of the Richtext field
     Set hv = uidoc.CreateObject( "XLOBJ", "" , "c:\Book.xls")
End Sub



Not the nicest solution but extremely small and efficient - liked that one very much, is already implemented and works fine - has the side advantage that users can easily edit the templates by themselves :)
@arunkumar: brilliant, but to some extent overkill for the small thing i'm building. Thanx anyway :)
@snocross: Wonderful idea, just brilliant. Would have taken it but because the object field is inside the mask itself it is impossible to update the previes after i edited the excel sheet. Would be fine if i only needed an icon but that's not the point.
Thanks anyone for helping :)

Dont worry i never burdened myself. I just C&P from one of my app though.

As long as you have a solution....

Hi Sno-Bro!
Have you started drinking pineapple juice yet ?

Edit Templates ? Does the user have designer access to the template ! Dangerous.

Keep it to the point is my policy. Thanks for the points.


No, but to the Excel Sheets used as templates in new notes documents. Thanx to you :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial