Solved

Add an attachment to Lotus Notes through VBA

Posted on 2000-05-12
6
1,327 Views
Last Modified: 2013-12-18
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.
0
Comment
Question by:da29647
  • 3
  • 2
6 Comments
 
LVL 5

Expert Comment

by:snocross
ID: 2804934
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
 
LVL 4

Accepted Solution

by:
stamp earned 50 total points
ID: 2805079
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
 

Author Comment

by:da29647
ID: 2807602
Perfect!
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 4

Expert Comment

by:stamp
ID: 2809912
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
 

Author Comment

by:da29647
ID: 2811805
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
 
LVL 4

Expert Comment

by:stamp
ID: 2812249
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

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Notes > Exchange calendar issues 5 64
Add code to Lotus Notes view column 2 123
Entry not found in index or view's index not built 4 89
LotusScript: Merge Data to Word File 22 89
You’ve got a lotus Domino web server, and you have been told that “leverage browser caching” is a must do. This means that we have to tell the browser everywhere in the web to use cache. In other words, we set (and send) an expiration date in the HT…
For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now