Solved

Add an attachment to Lotus Notes through VBA

Posted on 2000-05-12
6
1,342 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
Sometimes clients can lose connectivity with the Lotus Notes Domino Server, but there's not always an obvious answer as to why it happens.   Read this article to follow one of the first experiences I had with Lotus Notes on a client's machine, my…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

734 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