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.NotesSession")
   
    Set ws = CreateObject("Notes.NotesUIWorkspace")
    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.
da29647Asked:
Who is Participating?
 
stampCommented:
Hi da29647,

you need some constants and calling conventions from VBA/VBS.
Here is a complete example:
http://www.notes.net/46dom.nsf/55c38d716d632d9b8525689b005ba1c0/303d45851d816c21852567fa006b5d57?OpenDocument

Regards,
stamp
0
 
snocrossCommented:
This script creates a new document in the current database, creates a new rich text item called Body on the document, and attaches the file JIM.SAM to the Body. It then sets the value of the Form and Subject items on the document and saves it.
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 )
0
 
da29647Author Commented:
Perfect!
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
stampCommented:
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.NotesSession")

                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("Body")
                Call rtItem.APPENDTEXT("The body of the message goes here!")
                Call rtItem.ADDNEWLINE(1)

                'Attach a document!
                Call rtItem.EmbedObject(EMBED_ATTACHMENT, "",
                "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
0
 
da29647Author Commented:
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
0
 
stampCommented:
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.NotesSession")
Set ws = CreateObject("Notes.NotesUIWorkspace")
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("Body")
Call rtItem.APPENDTEXT(body_out)
Call rtItem.ADDNEWLINE(1)

 'Attach a document!
Call rtItem.EmbedObject(EMBED_ATTACHMENT, "","C:\AUDITOR3.INI")
Call doc.send (False)

doc = Null
ws  = Null
db  = Null
s   = Null


This is only WindowsScriptingHost/VisualBasicScrip, but it worked on my WinNT mashin.

Regards,
stamp
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.