Notes注册为一个OLE自动化服务器,提供了下面两个OLE自动化对象:
Notes.NotesUIWorkspace提供对Domino UI对象的
Notes.NotesSession提供对Domino对象(后台对象)的访问
外部程序可以创建对自动化对象的一个引用,然后按照对象层级进行访问。
Notes客户端必须安装在VBA程序的同一个机器上。Notes作为一个OLE自动化服务器时以独立的进程运行。从其它电脑上复制整个Notes客户端的文件夹也可以使用Lotus Notes,但当使用CreateObject方法获取NotesSession对象时会出现429错误,提示ActiveX部件不能创建对象。如图:
解决这个问题,需要注册一些Notes部件。假如Notes安装在Program Files下,可以在C:Program FilesLotusNotes目录中找到NotesW32.reg文件,双击该文件即可。
OLE自动化使用后绑定,你必须先创建(例如用CreateObject)一个Notes.NotesUIWorkspace或Notes.NotesSession对象,然后按照对象层级使用可用的方法。例如,如果你想打开一个Domino后台数据库,先创建Notes.NotesSession OLE自动化对象,然后使用NotesSession的GetDatabase方法设置一个引用变量。
在VBA中,将所有Domino对象的引用变量设置为Object类型。当使用完对象后,将引用变量设置为Nothing以清除所占用的内存。
使用后绑定不能方便的浏览Notes的对象、方法和属性,可以在VBE窗口中打开引用对话框(工具->引用),点击“浏览”按钮,在C:Program FilesLotusNotes目录下找到Notes32.tlb文件(或domobj.tlb)文件然后添加引用(Notes32.tlb是Lotus Notes Autamation Class库,domobj.tlb是Lotus Domino Objects库),这样在对象浏览器中可以分别在Lotus和Domino库中查找所以得对象、方法和属性。
下面给出一些在Excel操纵Lotus Notes的示例。
1. 获取当前用户名
Sub GetUserName()
Dim aNotes
' 创建NoteSession对象
Set aNotes = CreateObject("Notes.NotesSession")
' 获取用户名
MsgBox aNotes.UserName
Set aNotes = Nothing
End Sub
2. 访问数据库
Sub AccessDataBase()
Dim aNotes
Dim aDataBase
Dim strTemp As String
Set aNotes = CreateObject("Notes.NotesSession")
' 获取当前数据库
Set aDataBase = aNotes.CURRENTDATABASE
' 获取服务器上指定数据库
'Set aDataBase = aNotes.GetDatabase("server/subfolder", "mail/yourname")
' 获取本地联系人数据库
'Set aDataBase = aNotes.GetDatabase("", "names")
' 获取本地数据库
'Set aDataBase = aNotes.GetDataBase("", "c:worktemp.nsf")
strTemp = "共有邮件:" & aDataBase.AllDocuments.Count
strTemp = strTemp & vbCrLf & "数据库标题:" & aDataBase.Title
strTemp = strTemp & vbCrLf & "数据库文件:" & aDataBase.Filename
strTemp = strTemp & vbCrLf & "数据库" & IIf(aDataBase.IsOpen, "已打开", "未打开")
MsgBox strTemp
Set aNotes = Nothing
Set aDataBase = Nothing
Set aDocument = Nothing
End Sub
3. 判断数据库是否打开
Sub OpenDataBase()
Dim aNotes
Dim aDataBase
Dim strTemp As String
Set aNotes = CreateObject("Notes.NotesSession")
Set aDataBase = aNotes.GetDataBase("", "")
If aDataBase.IsOpen Then
MsgBox "Is Open"
Else
MsgBox "Not Open"
End If
aDataBase.Open "", "c:worktemp.nsf"
If aDataBase.IsOpen Then
MsgBox aDataBase.Title & "已打开", , aDataBase.Filename
Else
MsgBox "未打开", , aDataBase.Filename
End If
Set aNotes = Nothing
Set aDataBase = Nothing
End Sub
这种打开方式只是用来提供后续操作,并不是表示在Notes窗口中打开该数据库。
4. 使用UIWorkspace对象打开指定数据库
Sub AccessUI()
Dim aNotesUI
Set aNotesUI = CreateObject("Notes.NotesUIWorkspace")
Call aNotesUI.OpenDataBase("", "c:worktemp.nsf")
Set aNotesUI = Nothing
End Sub
使用UIWorkspace对象则可以在Notes窗口中打开指定数据库。
5. 根据模板创建本地邮件数据库
Sub CreateMailDatabase()
Dim aNotes
Dim aDataBase
Dim dbTemp
Dim strTemp As String
Set aNotes = CreateObject("Notes.NotesSession")
' 指定模板文件
Set dbTemp = aNotes.GetDataBase("", "mail6.ntf")
' 第一个参数指定服务器名称,如果为空表示生成本地数据库
' 第二个参数表示数据库文件名称
' 第三个参数表示是否继承未来的设计变化
Set aDataBase = dbTemp.CREATEFROMTEMPLATE("", "TestDB", True)
aDataBase.Title = "Just Test"
Set aNotes = Nothing
Set dbTemp = Nothing
Set aDataBase = Nothing
End Sub
这样创建的数据库并没有添加到Notes的Workspace中,但在C:Program FilesLotusNotesData目录下生成TestDB.nsf数据库文件。
6. 连接UIDocument和Document
Sub ConnectUIandDoc()
Dim workspace As Object
Dim uidoc As Object
Dim doc As Object
Dim db As Object
Set workspace = CreateObject("Notes.NotesUIWorkspace")
' 设置uidoc为NotesUIDocument对象
Set uidoc = workspace.CURRENTDOCUMENT
' 将NotesUIDocument对象赋给NotesDocument对象
Set doc = uidoc.DOCUMENT
Set db = doc.PARENTDATABASE
MsgBox "Parent database: " & db.Title
Set db = Nothing
Set doc = Nothing
Set uidoc = Nothing
Set workspace = Nothing
End Sub
7. 列出当前数据库中所有的文档(默认打开邮件数据库,列出所有邮件)。
Sub ListAllDocument()
Dim aNotes
Dim aDataBase
Dim aDC
Dim aDoc
Dim i As Integer
Set aNotes = CreateObject("Notes.NotesSession")
Set aDataBase = aNotes.CURRENTDATABASE
Set aDC = aDataBase.AllDocuments
Debug.Print aDC.Count ' 所有文档总数
Set aDoc = aDC.GETFIRSTDOCUMENT ' 获取第一个邮件
i = 1
While Not (aDoc Is Nothing)
Cells(i, 1) = aDoc.GETFIRSTITEM("From").text ' 发件人
Cells(i, 2) = aDoc.GETFIRSTITEM("Subject").text ' 标题
i = i + 1
Set aDoc = aDC.GetNextDocument(aDoc) ' 获取下一个邮件
Wend
Set aNotes = Nothing
Set aDataBase = Nothing
Set aDC = Nothing
Set aDoc = Nothing
End Sub
8. 列出收件箱中所有的邮件
Sub ListInboxDocument()
Dim aNotes
Dim aDataBase
Dim aView
Dim aDoc
Dim i As Integer
Set aNotes = CreateObject("Notes.NotesSession")
Set aDataBase = aNotes.CURRENTDATABASE
Set aView = aDataBase.getview("($Inbox)") ' 获取收件箱
Debug.Print aView.ALLENTRIES.Count ' 所有文档总数
Set aDoc = aView.GETFIRSTDOCUMENT
i = 1
While Not (aDoc Is Nothing)
Cells(i, 1) = aDoc.GETFIRSTITEM("From").text
Cells(i, 2) = aDoc.GETFIRSTITEM("Subject").text
i = i + 1
Set aDoc = aView.GetNextDocument(aDoc)
Wend
Set aNotes = Nothing
Set aDataBase = Nothing
Set aView = Nothing
Set aDoc = Nothing
End Sub
可以使用Set aView = aDataBase.getview(“($Sent)”)获得发件箱。
9. 列出当前数据库中所有的视图
Sub GetAllViews()
Dim aNotes
Dim aDataBase
Dim aView
Dim j As Integer
Set aNotes = CreateObject("Notes.NotesSession")
Set aDataBase = aNotes.CURRENTDATABASE
j = 1
For Each aView In aDataBase.views
Cells(j, 1) = aView.Name
Cells(j, 2) = aView.IsFolder
j = j + 1
Next
Set aNotes = Nothing
Set aDataBase = Nothing
Set aView = Nothing
End Sub
如果是邮件数据库可以列出诸如收件箱、发件箱、草稿箱和垃圾箱等等视图的名称。
10. 列出指定文档的所有Item
Sub ListDocItems()
Dim aNotes
Dim aDataBase
Dim aDocument
Dim aView
Dim aItem
Dim i
Set aNotes = CreateObject("Notes.NotesSession")
Set aDataBase = aNotes.CURRENTDATABASE
Set aView = aDataBase.getview("($Inbox)")
If (aView Is Nothing) Then
MsgBox "Inbox view don't exist!"
Else
Set aDocument = aView.GETFIRSTDOCUMENT
i = 1
For Each aItem In aDocument.Items
Cells(i, 1) = aItem.Type
Cells(i, 2) = aItem.Name
Cells(i, 3) = aItem.text
i = i + 1
Next
End If
Set aNotes = Nothing
Set aDataBase = Nothing
Set aView = Nothing
Set aDocument = Nothing
Set aItem = Nothing
End Sub
这些Item构成一个文档或者说一个邮件,这些Item的内容也可以使用类似aDoc.GETFIRSTITEM(“From”).Text的方法来获取。
11. 列出邮件正文中的所有嵌入对象,包括Excel工作表、附件等。(正文中嵌入的图片不知道怎么获得)
Sub ListItemofBody()
Dim aNotes
Dim aDataBase
Dim aDocument
Dim aView
Dim aItem
Dim oEmb
Set aNotes = CreateObject("Notes.NotesSession")
Set aDataBase = aNotes.CURRENTDATABASE
Set aView = aDataBase.getview("($Inbox)")
If (aView Is Nothing) Then
MsgBox "Inbox view don't exist!"
Else
' 本例中只列出第一个邮件的嵌入对象
Set aDocument = aView.GETFIRSTDOCUMENT
Set rtItem = aDocument.GETFIRSTITEM("Body")
Debug.Print rtItem.text
If (rtItem.Type = 1) Then
For Each oEmb In rtItem.EmbeddedObjects
Debug.Print oEmb.Type & " " & oEmb.Name
Next
End If
End If
Set aNotes = Nothing
Set aDataBase = Nothing
Set aView = Nothing
Set aDocument = Nothing
Set aItem = Nothing
End Sub
其中嵌入对象的类型包括:
EMBED_ATTACHMENT (1454)
EMBED_OBJECT (1453)
EMBED_OBJECTLINK (1452)
12. 搜索指定条件邮件
Sub SearchDocument()
Dim aNotes
Dim aDatabase
Dim aDC
Dim aDoc
Dim i As Integer
Dim dt
Set aNotes = CreateObject("Notes.NotesSession")
Set aDatabase = aNotes.CURRENTDATABASE
' 指定日期
Set dt = aNotes.CREATEDATETIME("03/03/10")
Set aDC = aDatabase.Search("@Contains(Subject;""FW"")", dt, 0)
'Set aDC = aDatabase.Search("@Contains(Subject;""FW"")", Nothing, 0)
Set aDoc = aDC.GETFIRSTDOCUMENT()
i = 1
While Not (aDoc Is Nothing)
Cells(i, 1) = aDoc.GETFIRSTITEM("From").text
Cells(i, 2) = aDoc.GETFIRSTITEM("Subject").text
i = i + 1
Set aDoc = aDC.GetNextDocument(aDoc)
Wend
Set aNotes = Nothing
Set aDatabase = Nothing
Set aDC = Nothing
Set aDoc = Nothing
Set dt = Nothing
End Sub
aDatabase.Search(“@Contains(Subject;”"FW”")”, dt, 0)表示搜索数据库中从dt所表示的日期开始的主题包含“FW”字符串的邮件。
语法:
Set notesDocumentCollection = notesDatabase.Search( formula$, notesDateTime, maxDocs% )
参数formula$:使用@function公式定义搜索条件;
参数notesDateTime:开始时间,如果设置Nothing则表示没有开始时间。
参数mxDocs%:返回的最大邮件数。设为0时表示匹配所有邮件。
13. 下载附件
Sub GetAttachement()
Dim aNotes
Dim aDataBase
Dim aDocument
Dim aView
Dim rtItem
Dim oEmb
Set aNotes = CreateObject("Notes.NotesSession")
Set aDataBase = aNotes.CURRENTDATABASE
Set aView = aDataBase.getview("($Inbox)")
If (aView Is Nothing) Then
MsgBox "Inbox view don't exist!"
Else
' 本例只是处理第一个邮件
Set aDocument = aView.GETFIRSTDOCUMENT
Debug.Print aDocument.GETFIRSTITEM("From").text ' 发送者
Debug.Print aDocument.GETFIRSTITEM("Subject").text ' 主题
Debug.Print aDocument.GETFIRSTITEM("posteddate").text ' 发送时间
Set rtItem = aDocument.GETFIRSTITEM("Body")
If (rtItem.Type = 1) Then
For Each oEmb In rtItem.EmbeddedObjects
If (oEmb.Type = 1454) Then ' 附件类型
Call oEmb.ExtractFile("c:" & oEmb.Source)
End If
Next
End If
End If
Set aNotes = Nothing
Set aDataBase = Nothing
Set aView = Nothing
Set aDocument = Nothing
End Sub
14. 设置签名档
Sub SetSignature()
Dim aNotes
Dim aDataBase
Dim aProf
Set aNotes = CreateObject("Notes.NotesSession")
Set aDataBase = aNotes.CURRENTDATABASE
Set aProf = aDataBase.GetProfileDocument("CalendarProfile")
Call aProf.ReplaceItemValue("EnableSignature", "1")
'Call aProf.ReplaceItemValue("SignatureOption", "1")
'Call aProf.ReplaceItemValue("Signature_1", "In god we trust")
Call aProf.ReplaceItemValue("SignatureOption", "2")
Call aProf.ReplaceItemValue("Signature_2", "C:test.jpg")
Call aProf.ComputeWithForm(True, False)
Call aProf.Save(True, False)
Set aNotes = Nothing
Set aDataBase = Nothing
Set aProf = Nothing
End Sub
语句aProf.ReplaceItemValue(“EnableSignature”, “1″)表示勾选“Automatically append a signature to the bottom of my outgoing mail messages”。”0″表示不勾选。
语句Call aProf.ReplaceItemValue(“SignatureOption”, “1″)表示勾选“Text”。
语句Call aProf.ReplaceItemValue(“Signature_1″, “In god we trust”)表示设置文本签名档内容。
语句Call aProf.ReplaceItemValue(“SignatureOption”, “2″)表示勾选“HTML or Image File”。
语句Call aProf.ReplaceItemValue(“Signature_2″, “C:test.jpg”)设置HTML或Image文件路径名,也可以使用文本文件。
签名档设置如图:
15. 发送邮件时添加签名档
Sub SendEmailwithSignature()
Dim aNotes
Dim aDatabase
Dim aDocument
Dim strSign As String
Set aNotes = CreateObject("Notes.NotesSession")
Set aDatabase = aNotes.CURRENTDATABASE
strSign = aDatabase.GetProfileDocument("CalendarProfile") _
.GETITEMVALUE("Signature")(0)
Set aDocument = aDatabase.CREATEDOCUMENT
aDocument.Subject = "test"
aDocument.SendTo = "test@xxx.com"
aDocument.Form = "Memo"
aDocument.Body = "This is a test to using Signature." & VbCrLf & VbCrLf & strSign
aDocument.SAVEMESSAGEONSEND = True
aDocument.PostedDate = Now
' 发送邮件
'Call aDocument.SEND(False)
' 保存邮件
Call aDocument.Save(True, False)
Set aNotes = Nothing
Set aDatabase = Nothing
Set aDocument = Nothing
End Sub
即使已经设置了签名档,使用NotesSession发送邮件时,邮件中仍然没有包括签名档,需要读取签名档内容并添加到正文中。这里只是读取文本内容的签名档。
15. 发送带附件的邮件
Sub SendEmailbyNotesWithAttachement()
Dim nNotes As Object
Dim nDatabase As Object
Dim nDocument As Object
Dim nRTItem As Object
Dim aSend As Variant
On Error GoTo errHandle
' 使用数组表示多个接收者
aSend = VBA.Array("test1@xxx.com", "test2@xxx.com")
Set nNotes = CreateObject("Notes.NotesSession")
Set nDatabase = nNotes.CURRENTDATABASE
Set nDocument = nDatabase.CREATEDOCUMENT
nDocument.Subject = "Test"
nDocument.SendTo = aSend
nDocument.Form = "Memo"
nDocument.SAVEMESSAGEONSEND = True
nDocument.PostedDate = Now
Set nRTItem = nDocument.CREATERICHTEXTITEM("Body")
Call nRTItem.AddNewLine(2)
Call nRTItem.AppendText("This is a test mail with a attachement")
Call nRTItem.AddNewLine(1)
Call nRTItem.EMBEDOBJECT(1454, "", "c:test.txt")
Call nDocument.SEND(False)
Set nNotes = Nothing
Set nDatabase = Nothing
Set nDocument = Nothing
Set nRTItem = Nothing
Exit Sub
errHandle:
Set nNotes = Nothing
Set nDatabase = Nothing
Set nDocument = Nothing
Set nRTItem = Nothing
MsgBox Err.Description
End Sub
这样运行的结果在正文中会显示如下图的内容。
如果在Set nRTItem语句前设置nDocument.Body属性,代码将出错并提示“Rich text item body already exists”错误。例如下面的代码将产生错误。
Set nDocument = nDatabase.CREATEDOCUMENT
nDocument.Subject = "Test"
nDocument.SendTo = aSend
nDocument.Form = "Memo"
nDocument.SAVEMESSAGEONSEND = True
nDocument.PostedDate = Now
nDocument.body = "This is a test using body"
Set nRTItem = nDocument.CREATERICHTEXTITEM("Body")
这时可以修改Set nRTItem语句来创建另外一个名称的RichText Item。如下:
Set nDocument = nDatabase.CREATEDOCUMENT
nDocument.Subject = "Test"
nDocument.SendTo = aSend
nDocument.Form = "Memo"
nDocument.SAVEMESSAGEONSEND = True
nDocument.PostedDate = Now
nDocument.body = "This is a test using body"
Set nRTItem = nDocument.CREATERICHTEXTITEM("Body1")
但是这样的结果是RichText Item中AppendText的内容不能显示,仅能显示附件。如图:
也可以将Set nRTItem语句移动到前面,如下:
Set nDocument = nDatabase.CREATEDOCUMENT
Set nRTItem = nDocument.CREATERICHTEXTITEM("Body")
nDocument.Subject = "Test"
nDocument.SendTo = aSend
nDocument.Form = "Memo"
nDocument.SAVEMESSAGEONSEND = True
nDocument.PostedDate = Now
nDocument.body = "This is a test using body"
代码仍能运行,同上例一样RichText Item中AppendText的内容不能显示,仅能显示附件。
这个方法有一个问题是正文中有两个相同名称的Item,一个是Text类型的Item,另外一个是RichText类型的Item。而Lotus只有GetFirstItem但没有GetNextItem。使用程序方法的话,程序限制只能获取第一个Item的内容,也就是Text类型的Item。要想获取第2个Item的内容,需要先删除第一个Item,然后保存Document,然后再使用GetFirstItem方法获取第2个 Item(现在是第1个了)的附件。
所以建议不要使用相同名称的Item。
17. 选择工作表范围发送邮件
Sub SendRange()
Dim nNotes As Object
Dim nDatabase As Object
Dim nDocument As Object
Dim aSend As Variant
Dim rngSel As Range
Dim doClip As DataObject
Set rngSel = Application.InputBox("请选择工作表范围:", "选择", , , , , , 8)
If rngSel Is Nothing Then Exit Sub
' 复制单元格内容到剪贴板
rngSel.Copy
Set doClip = New DataObject
doClip.GetFromClipboard
Application.CutCopyMode = False
On Error GoTo errHandle
aSend = VBA.Array("test1@xxx.com", "test2@xxx.com")
Set nNotes = CreateObject("Notes.NotesSession")
Set nDatabase = nNotes.CURRENTDATABASE
Set nDocument = nDatabase.CREATEDOCUMENT
nDocument.Subject = "Test"
nDocument.SendTo = aSend
nDocument.Form = "Memo"
nDocument.body = "This is test which include a range selection from Excel" & vbCrLf & doClip.GetText
nDocument.SAVEMESSAGEONSEND = True
nDocument.PostedDate = Now
Call nDocument.SEND(False)
Set nNotes = Nothing
Set nDatabase = Nothing
Set nDocument = Nothing
Set dtClip = Nothing
Exit Sub
errHandle:
Set nNotes = Nothing
Set nDatabase = Nothing
Set nDocument = Nothing
Set dtClip = Nothing
MsgBox Err.Description
End Sub
18. 将选择内容以图片方式发送
Sub SendFormatData()
Dim aNotesUI
Dim aDocUI
Dim strTo As String
Dim strCC As String
Dim strSub As String
Dim strBody As String
Dim rngSel As Range
Set rngSel = Application.InputBox("请选择工作表范围:", "选择", , , , , , 8)
If rngSel Is Nothing Then Exit Sub
rngSel.Copy
Set aNotesUI = CreateObject("Notes.NotesUIWorkspace")
Set aDocUI = aNotesUI.COMPOSEDOCUMENT("", "c:worktemp.nsf", "Memo")
Set aDocUI = aNotesUI.CURRENTDOCUMENT
strTo = "test1@xxx.com"
strCC = "test2@xxx.com"
strSub = "test"
strBody = "Here is a test with picture from Excel"
' 设置Field的内容
Call aDocUI.FIELDSETTEXT("EnterSendTo", strTo) ' 收件人
Call aDocUI.FIELDSETTEXT("EnterCopyTo", strCC) ' CC
Call aDocUI.FIELDSETTEXT("Subject", strSub) ' 主题
' 添加正文内容
Call aDocUI.FIELDAPPENDTEXT("Body", vbCrLf & strBody & vbCrLf)
' 将选择的内容复制到正文中
Call aDocUI.GOTOFIELD("Body")
Call aDocUI.Paste
' 保存邮件
Call aDocUI.Save
' 发送邮件
'Call aDocUI.Send(True)
Application.CutCopyMode = False
Set aDocUI = Nothing
Set aNotesUI = Nothing
Set rngSel = Nothing
End Sub
FieldSetText方法是设置文档中指定Field(也可以说是Item)的值,如果该Field中已经存在内容,则被覆盖。
FieldAppendText方法是在文档中指定的Field上添加内容,而不去除已有内容。
如果复制图表的话,可以将
Set rngSel = Application.InputBox("请选择工作表范围:", "选择", , , , , , 8)
If rngSel Is Nothing Then Exit Sub
rngSel.Copy
修改成:
If ActiveChart Is Nothing Then Exit Sub
ActiveChart.CopyPicture xlScreen, xlPicture, xlScreen
如果要复制工作表中插入的图片的话,则修改代码为(其中Shape需自己更改)为:
ActiveSheet.Shapes(2).Select
Selection.Copy
关于VBA使用Lotus Notes的学习资料并不是很多。感觉Lotus Domino Design帮助文件中关于LotusScript/COM/OLE Classes部分是最好的参考资料。
这里有个很早以前找到的LotusScript for Visual Basic Programmers的PDF文件,英文版的,虽然是Lotus Notes 4的,也很有帮助。
如果觉得我的文章对您有用,请点赞。您的支持将鼓励我继续创作!
赞2
添加新评论0 条评论