VBA and Lotus Notes 1

[automaticbaby]

I have searched endlessly for some help with this, but so far I have not come across anything useful (or maybe I'm missing something). From Access, I am trying to open a database in Lotus Notes, open a new document and fill in some of the fields and attache a document. I have been able to do it all except for attaching the document. Here is the code I have:

    Dim dbname As String
    Dim dbpath As String
    Dim NotesUIWorkspace As Object
    Dim NotesUIDocument As Object    
    Dim object As NotesEmbeddedObject
    Dim notesrtf As Object
    Dim strVariable as String
    Dim strDoc as string

    Set NotesUIWorkspace = CreateObject("Notes.NotesUIWorkspace")

    dbname = "db_notes.nsf"
    dbpath = "chi_server/Service Team"

    Call NotesUIWorkspace.OpenDatabase(dbpath, dbname, "By Issue Date (2)", , False, False)

    Set NotesUIDocument = NotesUIWorkspace.COMPOSEDOCUMENT(dbpath, dbname, "Document")
	' need to figure out how to copy and paste to below using rich text
    Call NotesUIDocument.FIELDSETTEXT("Body", strDoc)
    Call NotesUIDocument.GOTOFIELD("Body")
    NotesUIDocument.Selection.PasteSpecial
    
    Call NotesUIDocument.FIELDSETTEXT("Document", Format(Now(), "YY") & "/" & strVariable)
    Call NotesUIDocument.FIELDSETTEXT("Categories", "Miscellaneous")
    Call NotesUIDocument.FIELDSETTEXT("Product", "API")
    Call NotesUIDocument.FIELDSETTEXT("Recipient", "All Clients")
    Call NotesUIDocument.FIELDSETTEXT("Subject", "Number " & strVariable & " " & "Title")
    Call NotesUIDocument.FIELDSETTEXT("Type", "Memo Release")
    
	' doesn't work
    Call NotesUIDocument.GOTOFIELD("FuncSpec")
    Call NotesUIDocument.FIELDSETTEXT("FuncSpec", "G:\Memo\123.doc")
    ' doesn't work
    Set object = NotesUIDocument.EmbedObject(1454, "", "G:\Memo\123.doc")
    ' this will attach the entire document (IE copy and paste) to the field
    Call NotesUIDocument.CreateObject("WordDocument", "", "G:\Memo\123.doc")
	' doesn't work
    Call notesrtf.EmbedObject(1454, "", "G:\Memo\123.doc", "Attachment")
	' doesn't work
    Call NotesUIDocument.CreateObject("Attachment", "G:\Memo\123.doc")

There is a comment in the code, where I am also looking to paste from the word doc to a field in Lotus Notes and I want to keep all the formating. Help with that would be appreciated.

Thanks in advance!
Albert

 

[HughLerwill]

>I have searched endlessly ...

... but did you try searching this forum for 'lotus'; in 4th place is thread707-1172968 which contains stuff which you may find useful.

 

[automaticbaby]

I should have added that I searched endlessly and all I can find is info on how to send an email with an attachment using Lotus Notes.

I cannot make the code you linked to work with what I'm doing. I don't really know much about Lotus, but, for example, the like in that code that reads

Session.Initialize ("password")

causes issues - Object does not support this property or method

and

Maildb.OPENMAIL

only works for opening a new email.

I've tried multiple ways with similar code, but with no success attaching a file to a field in the new document.

I appreciate your help.
Albert

 

[taupirho]

In your code the line that says

Call notesrtf.EmbedObject(1454, "", "G:\Memo\123.doc", "Attachment")

is similar to one that I use in an XL spreadsheet app that I have which works for me.

However although I see a

Dim notesrtf As Object

I cannot see where you are making the is object point to anything with an equivalent

set notesrtf = ...

So it no wonder your line of code doesn't work that tries to embed a document into this object !


I don't have access here but for interest my XL VBA code does something like this.

Dim s As Object

Dim db As Object

Dim doc As Object

Dim attach As Object

Dim embedobj As Object

Dim annot_text As NotesRichTextItem

Dim bod As Object
Dim style As Object

Dim NUIWorkSpace As Object
Dim NUIdoc As Object

Const EMBED_ATTACHMENT = 1454

Set s = CreateObject("Notes.NotesSession")
Set NUIWorkSpace = CreateObject("Notes.NotesUIWorkspace")

Set db = s.GetDatabase("mydbname_goes_here", "my nsf file_ref_goes_here")


If db.IsOpen = False Then

db.Openmail

End If

Set doc = db.CreateDocument

Set bod = doc.CreateRichTextItem("Body")

Set style = s.CreateRichTextStyle

filename = "c:\whatever"

With bod
.AddNewLine 2
Call bod.EmbedObject(EMBED_ATTACHMENT, "", filename)
End With


etc .....



In order to understand recursion, you must first understand recursion.

 

[automaticbaby]

taupirho,

Thanks for your help.

Let me start off by saying I'm very confused. I'm trying to attach a link to a document in Lotus Notes (so that in the end, in the field of my choosing, there is a Word icon that when I click on it will open the document where it is actually stored), but all I keep getting are answers that involve (what appear to me) how to send an email with an attachment. Maybe if I understood that it would make sense to me.

The version of Lotus Notes we use does not include an email client and while the code example you provided does not get an error (though I've yet to run it all the way through) it appears to do nothing. What I mean is that nothing shows up on the screen whereas the code I use does. Maybe that's normal, but I'm not familiar with coding for Lotus.

The reason I have not run the full code is because the database this would write to would be visible by our clients and they would not be happy to see test after test document when they are looking at these online.

So, if setting notesrtf is the way to go, how would I do that? Any suggestions would be greatly appreciated.

Thanks!
Albert

 

[taupirho]

Unfortunately my LN appears to be closely tied to the email client. Antway, stepping through my code I don't see anything onscren until after this line (which isn't in my original fragment of code I posted)

Set NUIdoc = NUIWorkSpace.EDITDOCUMENT(True, doc)

The above line is just after the section

With bod
.AddNewLine 2
Call bod.EmbedObject(EMBED_ATTACHMENT, "", filename)
End With

And after this line is called, the document appears with the attached file shown. After this I send the document out but I guess you would want to just save it. Let me know how you get on.




In order to understand recursion, you must first understand recursion.

 

[automaticbaby]

taupirho,

Your new line works to help view the form/document and I was able to incorporate most of my old code into the code you provided. I'm still missing something though because it's not creating the link. Everything I read uses Attachment as a variable or a field name. I don't know when I need to use that as a variable or a field name, but my field name is FuncSpec. Any ideas on how to make it work?

Here's what I have so far:

    Dim s As Object
    Dim db As Object
    Dim doc As Object
    Dim attach As Object
    Dim EmbedObj As Object
    Dim annot_text As NotesRichTextItem
    Dim bod As Object
    Dim style As Object
    Dim NUIWorkSpace As Object
    Dim NUIdoc As Object
    
    Const EMBED_ATTACHMENT = 1454
    
    Set s = CreateObject("Notes.NotesSession")
    Set NUIWorkSpace = CreateObject("Notes.NotesUIWorkspace")
    Set db = s.GetDatabase("SunCpMk_CHI_Gen5/SunGard Capital Markets", "Notes_DBs\csb.nsf")
    
    If db.IsOpen = False Then
        db.OPENMAIL
    End If
    strVariable = "123"
    
    Set doc = db.CreateDocument
    Set bod = doc.CreateRichTextItem("FuncSpec")
    Set style = s.CreateRichTextStyle
    fileName = "G:\CSB\CDB\CDB123.doc"
'opens the form/document
    Set NUIdoc = NUIWorkSpace.EDITDOCUMENT(True, doc)
    Call NUIdoc.GOTOFIELD("Body")
    Call NUIdoc.PASTE
    Call NUIdoc.FIELDSETTEXT("Document", Format(Now(), "YY") & "/" & strVariable)
    Call NUIdoc.FIELDSETTEXT("Categories", "Miscellaneous")
    Call NUIdoc.FIELDSETTEXT("Product", "GMI")
    Call NUIdoc.FIELDSETTEXT("Recipient", "All Clients")
    Call NUIdoc.FIELDSETTEXT("Subject", "CSB #" & strVariable & " " & "Title")
    Call NUIdoc.GOTOFIELD("FuncSpec")
' none of the below work
' these represent different attempts to paste the link
    Call bod.EmbedObject(EMBED_ATTACHMENT, "", fileName)
    Call bod.EmbedObject(1454, "", "G:\Memo\123.doc")
    Set EmbedObj = bod.EmbedObject(1454, "", "G:\Memo\123.doc", "FuncSpec")
    Set EmbedObj = bod.EmbedObject(EMBED_OBJECT, "", "G:\Memo\123.doc")

 

[taupirho]

I think the order of how you do things is important.

If I do (as you are doing)

Set NUIdoc = NUIWorkSpace.EDITDOCUMENT(True, doc)
....
....
Call bod.EmbedObject(1454, "", filename)

It doesn't work

However,

Call bod.EmbedObject(1454, "", filename)
.....
.....
Set NUIdoc = NUIWorkSpace.EDITDOCUMENT(True, doc)

does work for me, so I would try that.






In order to understand recursion, you must first understand recursion.

 

[automaticbaby]

That worked! Awesome!!!! Thank you SO much for your help!!

This is not a big deal, but when the link shows up, it looks like a generic gray icon and not the icon of the Word document. Any ideas on how to fix that?