EtienneCilliers
asked on
Manipulate and send a copy of current Lotus Notes email using VBA
I am using Lotus Notes from VBA to send out personalised reports to recipients. I have the application working in Outlook (from VBA - Excel) but now need to port this to Lotus.
The way it works is that the user sets up a generic "template" email (with attachments, formatting, etc) for the report in Notes as a normal email edit. The user leaves this email to be the "current" item, then accesses the VBA program (in Excel). The VBA program must now pick up the "current" email, copy it, add the specific report for that participant, address it and send.
I have found code to help with the basics (addressing/attaching), but I need help to
1) Find the "current" email that is open in Notes.
2) Get reference to a copy of it to be able to do the report attaching and sending.
Any help/suggestions/code appreciated. Incidentally, for Outlook, I use <<objOutlook>>.Activeinspe ctor.Curre ntItem to get hold of the current item.
The way it works is that the user sets up a generic "template" email (with attachments, formatting, etc) for the report in Notes as a normal email edit. The user leaves this email to be the "current" item, then accesses the VBA program (in Excel). The VBA program must now pick up the "current" email, copy it, add the specific report for that participant, address it and send.
I have found code to help with the basics (addressing/attaching), but I need help to
1) Find the "current" email that is open in Notes.
2) Get reference to a copy of it to be able to do the report attaching and sending.
Any help/suggestions/code appreciated. Incidentally, for Outlook, I use <<objOutlook>>.Activeinspe
ASKER
Thanks very much.
I haven't been able to try it, but I stepped thru and it seems solid (I would have preferred a more elegant solution, but hey).
Some questions :
- how do you check for attachments in the template and copy those accross?
- will the .copy retain any formatting in the "template" email body?
Tks
I haven't been able to try it, but I stepped thru and it seems solid (I would have preferred a more elegant solution, but hey).
Some questions :
- how do you check for attachments in the template and copy those accross?
- will the .copy retain any formatting in the "template" email body?
Tks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Etienne,
You don't need to re-open the question just to get more info.
> when it gets to line .copy, it throws an error "Automation Error - The server through an exception"
I believe that's "threw an exception."
I would run through -- not threw :) -- the steps manually, using the UI and keyboard/menu directly. It could be there is something about the template specificaly that precludes copying.
1) If open document is in read mde, choose Actions->EditDocument
2) Makea note to yourself of the contents of the Subject field
3) Click inside the body area
4) Choose Edit->SelectAll
5) Choose Edit->Copy
Do you get an error?
You can also "mix and match." Place an EXIT SUB above the form.copy, and the Notes client will be left in the state where it was ready to copy.
ONE IMPORTANT OBSERVATION:
The OLE automation has been know to fail if the Notes clientis minimized, and occasionally, even if it is simply not inteh foreground. You may wish to use SendKeys to send an Alt-F10 or Alt-Space,X (both maximize the window) to teh client, or use Windows API calls to force teh windo open and top.
You don't need to re-open the question just to get more info.
> when it gets to line .copy, it throws an error "Automation Error - The server through an exception"
I believe that's "threw an exception."
I would run through -- not threw :) -- the steps manually, using the UI and keyboard/menu directly. It could be there is something about the template specificaly that precludes copying.
1) If open document is in read mde, choose Actions->EditDocument
2) Makea note to yourself of the contents of the Subject field
3) Click inside the body area
4) Choose Edit->SelectAll
5) Choose Edit->Copy
Do you get an error?
You can also "mix and match." Place an EXIT SUB above the form.copy, and the Notes client will be left in the state where it was ready to copy.
ONE IMPORTANT OBSERVATION:
The OLE automation has been know to fail if the Notes clientis minimized, and occasionally, even if it is simply not inteh foreground. You may wish to use SendKeys to send an Alt-F10 or Alt-Space,X (both maximize the window) to teh client, or use Windows API calls to force teh windo open and top.
ASKER
yes it was "threw" - blame that on caffeine :)
Tried the completely manual approach and it worked fine.
Put in an AppActivate to bring Notes to the fore, same error.
Stopped code after .SelectAll. Did manual copy worked fine.
Thanks for the help.
Tried the completely manual approach and it worked fine.
Put in an AppActivate to bring Notes to the fore, same error.
Stopped code after .SelectAll. Did manual copy worked fine.
Thanks for the help.
Well, that IS odd!
Perhaps we should switch to using exclusively back-end classes? No OLE automation involved?
Perhaps we should switch to using exclusively back-end classes? No OLE automation involved?
ASKER
Would the approach then be to get the user to save the email in the "drafts" folder and then work it from there using the backend classes?
I think I am ok with the setting up and copying code, but how will the code look to get hold of the emails in the DRAFTS folder to allow the code to show the titles to the user to make a selection? I have seen there is a "AllDocuments" method of the NotesDatabase object, but this is too general or I don't know how to parameterize it. I have done some searching and can't find a useful reference for this.
I think I am ok with the setting up and copying code, but how will the code look to get hold of the emails in the DRAFTS folder to allow the code to show the titles to the user to make a selection? I have seen there is a "AllDocuments" method of the NotesDatabase object, but this is too general or I don't know how to parameterize it. I have done some searching and can't find a useful reference for this.
Actually, what would probably be better is to use the built-in "stationery" view, and as the user to create templates there.
What you want to use, if you are usingteh drafts folder, is something like:
Dim docIDs() As String
Dim docSubjects As String
Set view = mailfile.getView("($Drafts )")
Set entries = view.allEntries
If entries.count = 0 Then
'abort ... I leave this up to you
End If
Redim docIDs(entries.Count-1)
Redim docSubjects(entries.Count- 1)
Set entry = entries.getFirstEntry
Do Until entry Is Nothing
docIDs(i) = entry.noteID
docSubjects(i) = entry.columnValues(5) 'based on standard R5 mail template column order
i = i + 1
Set entry = entries.getNextEntry(entry )
Loop
Then display the docSubjects array, and for whatever indexNumber is returned from teh subject array, use mailFile.getDocumentByID(d ocIDs(inde xNumber)) to retrieve the document
If you want to use stationery, then you can do the same thing using .getView("Stationery") instead of "($Drafts)"
Note however that there are two kinds of stationery, Mail Stationery and Personal Stationery. The only real difference between them is that Personal Stationery allows a field ABOVE the body and another field BELOW the body, for you to add a header and/or footer. If you want to limit the choices to Mail Stationery, then modify the code a bit. It is only a three-line change:
Set view = mailfile.getView("($Drafts )")
Set entries = view.allEntries
becomes:
Set view = mailfile.getView("Statione ry")
Set entries = view.getAllEntriesByKey("M emo Stationery")
--------------
Once you have your back end document (via mailfile.getDocumentByID), your code becomes:
Set doc = mailfile.getDocumentByID(d ocIDs(user SelectedIn dexNumber) )
Set newMessage = doc.copyToDatabase(mailFil e) 'copying to my own database
newMessage.replaceItemValu e "SendTo" , "some recipient"
newMessage.getFirstItem("B ody").Embe dObject 1454 , "" , "c:\somefile.doc"
newMessage.Send false
newMessage.Save true,true
What you want to use, if you are usingteh drafts folder, is something like:
Dim docIDs() As String
Dim docSubjects As String
Set view = mailfile.getView("($Drafts
Set entries = view.allEntries
If entries.count = 0 Then
'abort ... I leave this up to you
End If
Redim docIDs(entries.Count-1)
Redim docSubjects(entries.Count-
Set entry = entries.getFirstEntry
Do Until entry Is Nothing
docIDs(i) = entry.noteID
docSubjects(i) = entry.columnValues(5) 'based on standard R5 mail template column order
i = i + 1
Set entry = entries.getNextEntry(entry
Loop
Then display the docSubjects array, and for whatever indexNumber is returned from teh subject array, use mailFile.getDocumentByID(d
If you want to use stationery, then you can do the same thing using .getView("Stationery") instead of "($Drafts)"
Note however that there are two kinds of stationery, Mail Stationery and Personal Stationery. The only real difference between them is that Personal Stationery allows a field ABOVE the body and another field BELOW the body, for you to add a header and/or footer. If you want to limit the choices to Mail Stationery, then modify the code a bit. It is only a three-line change:
Set view = mailfile.getView("($Drafts
Set entries = view.allEntries
becomes:
Set view = mailfile.getView("Statione
Set entries = view.getAllEntriesByKey("M
--------------
Once you have your back end document (via mailfile.getDocumentByID),
Set doc = mailfile.getDocumentByID(d
Set newMessage = doc.copyToDatabase(mailFil
newMessage.replaceItemValu
newMessage.getFirstItem("B
newMessage.Send false
newMessage.Save true,true
ASKER
Thanks again for the comprehensive help.
BUT, new problem:
I get a VBA error 458 at line ... = entry.columnValues(5). ( 458: Variable uses an Automation type not supported in Visual Basic. )
[I assumed that 'Dim docSubjects As String' sb 'Dim docSubjects() As String' based on the usage?]
Any ideas?
BUT, new problem:
I get a VBA error 458 at line ... = entry.columnValues(5). ( 458: Variable uses an Automation type not supported in Visual Basic. )
[I assumed that 'Dim docSubjects As String' sb 'Dim docSubjects() As String' based on the usage?]
Any ideas?
Interesting. Notes passes an array of variants (which may be an array of arrays) for columnValues. For some reason, VB was unable to handle it in this case. We can bypass columnVluaes, and go directly to the underlying field:
= entry.document.getItemValu e("Subject ")(0)
= entry.document.getItemValu
ASKER
It works!
q - thanks. I would like to allocate more points since apart from your persistence, you have exposed two techniques (one that works, and one that should work perhaps with some more tinkering).
If this is possible, how can I do that?
q - thanks. I would like to allocate more points since apart from your persistence, you have exposed two techniques (one that works, and one that should work perhaps with some more tinkering).
If this is possible, how can I do that?
Not necessary.
Dim workspace as Object
Set workspace = CreateObject("Notes.NotesU
Dim form as Object 'notesUiDocument
Set form = workspace.currentDocument
Dim subject as String
subject = form.fieldGetText("Subject
form.editMode = true 'property side-effect forces edit mode
form.gotoField "Body"
form.selectAll
form.copy
'Now, we have the document content in the clipboard, and we know the subject
Set form = workSpace.composeDocument (,,"Memo") 'now we are in a new memo
form.FieldSetText "SendTo" , "some recipient"
form.FieldSetText "Subject" , Subject
form.gotoField "Body"
form.Paste
'Now, we have an issue. We need to attach the file, but the object model does not provide a "UI" method for attaching the file
'So, we save the document, retain its ID, close it, and use the back-end object model to add the attachment and send
form.Save
Dim doc as notesDocument
Set doc = form.document
Dim docID as String
docID = document.noteID
Dim mailFile as Object
Set mailFile = document.parentDatabase
form.Close
Set doc = mailFile.getDocumentByID(d
Dim body as Object
Set body = doc.getFirstItem("Body")
body.EmbedObject 1454 , "" , "c:\somefile.doc"
doc.Send False 'False prevents use of aan esoteric feature of Notes
doc.Save True , False
Best regards,
qwaletee