da29647
asked on
Add an attachment to Lotus Notes through VBA
I have succesfully created a link to Lotus Notes via Excel's VBA. I am then able to create an e-mail out of the Excel environment.
Here is what I have so far:
Sub emailer()
Dim SESSION As Object
Dim db As Object
Dim doc As Object
Dim ws As Object
'Examples of definitions
'this needs to be changed for YOU!
Server = "x"
Recipient = "x.nsf"
Set SESSION = CreateObject("Notes.NotesS ession")
Set ws = CreateObject("Notes.NotesU IWorkspace ")
Call ws.opendatabase(Server, Recipient)
Set db = SESSION.GetDatabase(Server , Recipient)
Set doc = db.CreateDocument()
send_to = Cells(1, 1)
subject_out = Cells(2, 1)
body_out = Cells(3, 1)
doc.sendto = send_to
doc.form = "Main Topic"
doc.Subject = subject_out
doc.body = body_out
doc.send (True)
Call doc.Save(True, False)
End Sub
The above will take certain cells from the Excel spreadsheet and set them equal to the subject, mail to , etc...
How do I add an attachment to the e-mail as well?
Is it as simple as doc.something?
Thanks.
Here is what I have so far:
Sub emailer()
Dim SESSION As Object
Dim db As Object
Dim doc As Object
Dim ws As Object
'Examples of definitions
'this needs to be changed for YOU!
Server = "x"
Recipient = "x.nsf"
Set SESSION = CreateObject("Notes.NotesS
Set ws = CreateObject("Notes.NotesU
Call ws.opendatabase(Server, Recipient)
Set db = SESSION.GetDatabase(Server
Set doc = db.CreateDocument()
send_to = Cells(1, 1)
subject_out = Cells(2, 1)
body_out = Cells(3, 1)
doc.sendto = send_to
doc.form = "Main Topic"
doc.Subject = subject_out
doc.body = body_out
doc.send (True)
Call doc.Save(True, False)
End Sub
The above will take certain cells from the Excel spreadsheet and set them equal to the subject, mail to , etc...
How do I add an attachment to the e-mail as well?
Is it as simple as doc.something?
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect!
Only to have all in place;
Here a copy from above link.
You MUST declare the EMBED_ATTACHMENT manifest constant
in your Visual Basic project with an integer value of 1454. That will
eliminate the error message. Here's the example:
Private Const EMBED_ATTACHMENT As Integer = 1454
Private Sub SendMailWithAttachment()
Dim s As Object
Dim db As Object
Dim doc As Object
Dim rtItem As Object
Dim Server As String, Database As String
Dim strError As String
Screen.MousePointer = vbHourglass
'Here's the server name and database name
Server = "ServerName"
Database = "mail\mailfilename"
' start up Lotus Notes and get object handle
Set s = CreateObject("Notes.NotesS ession")
Set db = s.GETDATABASE(Server, Database)
On Error GoTo ErrorLogon
' See if user is logged on yet;
' if not, the error handler will
' kick in!
Set doc = db.CREATEDOCUMENT
On Error GoTo 0
doc.Form = "Memo"
'Send an EMail to myself
doc.SendTo = "JohnDoe@whatever.com"
doc.Subject = "This is a test message"
' this will build the text part of your mail message
Set rtItem = doc.CREATERICHTEXTITEM("Bo dy")
Call rtItem.APPENDTEXT("The body of the message goes here!")
Call rtItem.ADDNEWLINE(1)
'Attach a document!
Call rtItem.EmbedObject(EMBED_A TTACHMENT, "",
"C:\AutoExec.bat")
Call doc.SEND(False) 'Make sure this parameter stays false
' set all object handles to nothing to release memory
Set doc = Nothing
Set db = Nothing
Set s = Nothing
Set rtItem = Nothing
Screen.MousePointer = vbDefault
MsgBox "Mail has been sent!", vbInformation
Exit Sub
ErrorLogon:
If Err.Number = 7063 Then
MsgBox "Please login to Lotus Notes first!", vbCritical
Set doc = Nothing
Set db = Nothing
Set s = Nothing
Set rtItem = Nothing
Screen.MousePointer = vbDefault
Exit Sub
Else
strError = "An Error has occurred on your system:" & vbCrLf
strError = strError & "Err. Number: " & Err.Number & vbCrLf
strError = strError & "Description: " & Err.Description
MsgBox strError, vbCritical
Set doc = Nothing
Set db = Nothing
Set s = Nothing
Set rtItem = Nothing
Screen.MousePointer = vbDefault
Exit Sub
End If
End
Regards,
stamp
Here a copy from above link.
You MUST declare the EMBED_ATTACHMENT manifest constant
in your Visual Basic project with an integer value of 1454. That will
eliminate the error message. Here's the example:
Private Const EMBED_ATTACHMENT As Integer = 1454
Private Sub SendMailWithAttachment()
Dim s As Object
Dim db As Object
Dim doc As Object
Dim rtItem As Object
Dim Server As String, Database As String
Dim strError As String
Screen.MousePointer = vbHourglass
'Here's the server name and database name
Server = "ServerName"
Database = "mail\mailfilename"
' start up Lotus Notes and get object handle
Set s = CreateObject("Notes.NotesS
Set db = s.GETDATABASE(Server, Database)
On Error GoTo ErrorLogon
' See if user is logged on yet;
' if not, the error handler will
' kick in!
Set doc = db.CREATEDOCUMENT
On Error GoTo 0
doc.Form = "Memo"
'Send an EMail to myself
doc.SendTo = "JohnDoe@whatever.com"
doc.Subject = "This is a test message"
' this will build the text part of your mail message
Set rtItem = doc.CREATERICHTEXTITEM("Bo
Call rtItem.APPENDTEXT("The body of the message goes here!")
Call rtItem.ADDNEWLINE(1)
'Attach a document!
Call rtItem.EmbedObject(EMBED_A
"C:\AutoExec.bat")
Call doc.SEND(False) 'Make sure this parameter stays false
' set all object handles to nothing to release memory
Set doc = Nothing
Set db = Nothing
Set s = Nothing
Set rtItem = Nothing
Screen.MousePointer = vbDefault
MsgBox "Mail has been sent!", vbInformation
Exit Sub
ErrorLogon:
If Err.Number = 7063 Then
MsgBox "Please login to Lotus Notes first!", vbCritical
Set doc = Nothing
Set db = Nothing
Set s = Nothing
Set rtItem = Nothing
Screen.MousePointer = vbDefault
Exit Sub
Else
strError = "An Error has occurred on your system:" & vbCrLf
strError = strError & "Err. Number: " & Err.Number & vbCrLf
strError = strError & "Description: " & Err.Description
MsgBox strError, vbCritical
Set doc = Nothing
Set db = Nothing
Set s = Nothing
Set rtItem = Nothing
Screen.MousePointer = vbDefault
Exit Sub
End If
End
Regards,
stamp
ASKER
I appreciate the help. As far as the first comment went, there is just enough difference between Lotus Script and VBA that I couldn't figure it out. Thanks anyway.
Dave
Dave
Ok Dave,
here your code extended:
Dim s
Dim db
Dim ws
Dim doc
Dim rtItem
Server = "intraxx/SRV/Org"
Recipient = "mail/file.nsf"
Const EMBED_ATTACHMENT = 1454
Set s = CreateObject("Notes.NotesS ession")
Set ws = CreateObject("Notes.NotesU IWorkspace ")
Call ws.opendatabase(Server, Recipient)
Set db = s.GetDatabase(Server, Recipient)
Set doc = db.CreateDocument()
'send_to = Cells(1, 1)
'subject_out = Cells(2, 1)
'body_out = Cells(3, 1)
send_to = "JohnDoe@whatever.com"
subject_out = "New mail Subject"
body_out = "The body of the message goes here!"
doc.sendto = send_to
doc.form = "Memo"
doc.Subject = subject_out
Set rtItem = doc.CREATERICHTEXTITEM("Bo dy")
Call rtItem.APPENDTEXT(body_out )
Call rtItem.ADDNEWLINE(1)
'Attach a document!
Call rtItem.EmbedObject(EMBED_A TTACHMENT, "","C:\AUDITOR3.INI")
Call doc.send (False)
doc = Null
ws = Null
db = Null
s = Null
This is only WindowsScriptingHost/Visua lBasicScri p, but it worked on my WinNT mashin.
Regards,
stamp
here your code extended:
Dim s
Dim db
Dim ws
Dim doc
Dim rtItem
Server = "intraxx/SRV/Org"
Recipient = "mail/file.nsf"
Const EMBED_ATTACHMENT = 1454
Set s = CreateObject("Notes.NotesS
Set ws = CreateObject("Notes.NotesU
Call ws.opendatabase(Server, Recipient)
Set db = s.GetDatabase(Server, Recipient)
Set doc = db.CreateDocument()
'send_to = Cells(1, 1)
'subject_out = Cells(2, 1)
'body_out = Cells(3, 1)
send_to = "JohnDoe@whatever.com"
subject_out = "New mail Subject"
body_out = "The body of the message goes here!"
doc.sendto = send_to
doc.form = "Memo"
doc.Subject = subject_out
Set rtItem = doc.CREATERICHTEXTITEM("Bo
Call rtItem.APPENDTEXT(body_out
Call rtItem.ADDNEWLINE(1)
'Attach a document!
Call rtItem.EmbedObject(EMBED_A
Call doc.send (False)
doc = Null
ws = Null
db = Null
s = Null
This is only WindowsScriptingHost/Visua
Regards,
stamp
Dim session As New NotesSession
Dim db As NotesDatabase
Dim doc As NotesDocument
Dim rtitem As NotesRichTextItem
Dim object As NotesEmbeddedObject
Set db = session.CurrentDatabase
Set doc = New NotesDocument( db )
Set rtitem = New NotesRichTextItem( doc, "Body" )
Set object = rtitem.EmbedObject _
( EMBED_ATTACHMENT, "", "c:\jim.sam", "James" )
doc.Form = "Main Topic"
doc.Subject = "Here's Jim's document, as an attachment"
Call doc.Save( True, True )