sending workbooks with Lotus Notes

[brettvba]

[Jack in the UK]

HI

Thats blank also please try again

Rdgs
==========
Jack

[brettvba]

Ok not sure why my posts are blank must be mental

Q was does anyone have any code for sending
XP Workbooks through Lotus Notes?
Lotus notes is an email programme.

[Jack in the UK]

Hi --

Notes is Lotus (IBM) and very very very very very very ........ good. Better that outlook i add but...


Office suite uses VBA and Lotus IBM Scripts and i never have hurd of VBA talking to Notes as bit like English and French, not ike English and American or Australian basic the same not so here...

Also i do not know anyone that programs in IBM.. al i suggest is check web for notes Scripts and homepage.

Sorry mate

Rdgs
==========
Jack

[brettvba]

Yeah I know there is something there under the references but otherwise not a big deal thanks anyway!

[NateO]

I'm in a hurry and gotta run, so I can't go through it but the following works for me:

Private Sub SendNotesMail()
    Dim Maildb As Object
    Dim UserName As String
    Dim MailDbName As String
    Dim MailDoc As Object
    Dim AttachME As Object
    Dim Session As Object
    Dim EmbedObj1 As Object
    Dim EmbedObj2 As Object
    Dim EmbedObj4 As Object
    Dim EmbedObj5 As Object
    Dim EmbedObj6 As Object
    Dim EmbedObj7 As Object
    Dim EmbedObj8 As Object
    Dim EmbedObj9 As Object
    Dim EmbedObj10 As Object
    Set Session = CreateObject("Notes.NotesSession")
    UserName = Session.UserName
    MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
    Set Maildb = Session.GETDATABASE("", MailDbName)
     If Maildb.IsOpen = True Then
     Else
         Maildb.OPENMAIL
     End If
    Set MailDoc = Maildb.CREATEDOCUMENT
    MailDoc.Form = "Memo"
    Recipient = Range("a15").Value
    MailDoc.sendto = Recipient
    ccRecipient = Range("b15").Value
    MailDoc.CopyTo = ccRecipient
    bccRecipient = Range("c15").Value
    MailDoc.BlindCopyTo = bccRecipient
    Subject = Range("it25")
    MailDoc.Subject = Subject
    BodyText = Range("b8")
    MailDoc.Body = BodyText
    MailDoc.SAVEMESSAGEONSEND = True     Attachment1 = Range("d15")
    If Attachment1 <> "" Then
        On Error Resume Next
        Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
        On Error Resume Next
        Set EmbedObj1 = AttachME.EMBEDOBJECT(1454, "", Attachment1, "Attachment")
        On Error Resume Next
        MailDoc.CREATERICHTEXTITEM ("Attachment")
        On Error Resume Next
    End If
Attachment2 = Range("e15")
    If Attachment2 <> "" Then
        On Error Resume Next
        Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
        On Error Resume Next
        Set EmbedObj2 = AttachME.EMBEDOBJECT(1454, "", Attachment2, "Attachment")
        On Error Resume Next
        MailDoc.CREATERICHTEXTITEM ("Attachment")
        On Error Resume Next
    End If
Attachment3 = Range("f15")
    If Attachment3 <> "" Then
        On Error Resume Next
        Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
        On Error Resume Next
        Set EmbedObj3 = AttachME.EMBEDOBJECT(1454, "", Attachment3, "Attachment")
        On Error Resume Next
        MailDoc.CREATERICHTEXTITEM ("Attachment")
        On Error Resume Next
    End If
Attachment4 = Range("g15")
    If Attachment4 <> "" Then
        On Error Resume Next
        Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
        On Error Resume Next
        Set EmbedObj4 = AttachME.EMBEDOBJECT(1454, "", Attachment4, "Attachment")
        On Error Resume Next
        MailDoc.CREATERICHTEXTITEM ("Attachment")
        On Error Resume Next
    End If
Attachment5 = Range("h15")
    If Attachment5 <> "" Then
        On Error Resume Next
        Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
        On Error Resume Next
        Set EmbedObj5 = AttachME.EMBEDOBJECT(1454, "", Attachment5, "Attachment")
        On Error Resume Next
        MailDoc.CREATERICHTEXTITEM ("Attachment")
        On Error Resume Next
    End If
Attachment6 = Range("i15")
    If Attachment6 <> "" Then
        On Error Resume Next
        Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
        On Error Resume Next
        Set EmbedObj6 = AttachME.EMBEDOBJECT(1454, "", Attachment6, "Attachment")
        On Error Resume Next
        MailDoc.CREATERICHTEXTITEM ("Attachment")
        On Error Resume Next
    End If
Attachment7 = Range("j15")
    If Attachment7 <> "" Then
        On Error Resume Next
        Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
        On Error Resume Next
        Set EmbedObj7 = AttachME.EMBEDOBJECT(1454, "", Attachment7, "Attachment")
        On Error Resume Next
        MailDoc.CREATERICHTEXTITEM ("Attachment")
        On Error Resume Next
    End If
Attachment8 = Range("k15")
    If Attachment8 <> "" Then
        On Error Resume Next
        Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
        On Error Resume Next
        Set EmbedObj8 = AttachME.EMBEDOBJECT(1454, "", Attachment8, "Attachment")
        On Error Resume Next
        MailDoc.CREATERICHTEXTITEM ("Attachment")
        On Error Resume Next
    End If
Attachment9 = Range("l15")
    If Attachment9 <> "" Then
        On Error Resume Next
        Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
        On Error Resume Next
        Set EmbedObj9 = AttachME.EMBEDOBJECT(1454, "", Attachment9, "Attachment")
        On Error Resume Next
        MailDoc.CREATERICHTEXTITEM ("Attachment")
        On Error Resume Next
    End If
Attachment10 = Range("m15")
    If Attachment10 <> "" Then
        On Error Resume Next
        Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
        On Error Resume Next
        Set EmbedObj10 = AttachME.EMBEDOBJECT(1454, "", Attachment10, "Attachment")
        On Error Resume Next
        MailDoc.CREATERICHTEXTITEM ("Attachment")
        On Error Resume Next
    End If
    MailDoc.PostedDate = Now()
    On Error GoTo errorhandler1
    MailDoc.SEND 0, Recipient
    Set Maildb = Nothing
    Set MailDoc = Nothing
    Set AttachME = Nothing
    Set Session = Nothing
    Set EmbedObj1 = Nothing
    Set EmbedObj2 = Nothing
    Set EmbedObj3 = Nothing
    Set EmbedObj4 = Nothing
    Set EmbedObj5 = Nothing
    Set EmbedObj6 = Nothing
    Set EmbedObj7 = Nothing
    Set EmbedObj8 = Nothing
    Set EmbedObj9 = Nothing
    Set EmbedObj10 = Nothing
    Exit Sub
errorhandler1:
    Application.Goto Reference:="IAddress"
    CellLocation99 = ActiveSheet.Name & "!" & ActiveCell.Address
    Worksheets("Address Complications").Select
    Range("a1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    ActiveCell = CellLocation99
    Set Maildb = Nothing
    Set MailDoc = Nothing
    Set AttachME = Nothing
    Set Session = Nothing
    Set EmbedObj1 = Nothing
    Set EmbedObj2 = Nothing
    Set EmbedObj3 = Nothing
    Set EmbedObj4 = Nothing
    Set EmbedObj5 = Nothing
    Set EmbedObj6 = Nothing
    Set EmbedObj7 = Nothing
    Set EmbedObj8 = Nothing
    Set EmbedObj9 = Nothing
    Set EmbedObj10 = Nothing
End Sub

The attachments are dos filepathsnames. I have a pretty good Excel workbook set up with some monster functionality. I could send the entire kit & kaboodle via e-mail if you shoot me an e-mail

Cheers,

Nate

[Ivan F Moala]

Here is another way;

Option Explicit

Sub LotusNotes_Session()
Dim Subject As String
Dim Recipient As String
Dim Msg As String
Dim nSession As notesSession
Dim nDatabase As NotesDatabase
Dim nDoc As NOTESDOCUMENT
'Need to reference the Lotusnotes object library

Subject = "Test message from VBA"
Recipient = "me@here.com.nz"
Msg = "This is a message sent to myself from VBA." + _
vbNewLine + "blah"

'session and database declared in general as object
Set nSession = CreateObject("Notes.NotesSession") 'create notes session
Set nDatabase = nSession.GetDatabase("", "") 'set db to database not yet named
Call nDatabase.OPENMAIL 'Open the users mail database

Set nDoc = nDatabase.CREATEDOCUMENT 'create a new document in mail database
Call nDoc.replaceitemvalue("SendTo", Recipient) 'create sendto field
Call nDoc.replaceitemvalue("Subject", Subject) 'create subject field
Call nDoc.replaceitemvalue("Body", Msg) 'create body field
Call nDoc.SEND(False) 'send message

Set nSession = Nothing ' close connection to free memory

End Sub


'Notes from help file;
'Set nDatabase = nSession.GetDatabase( server$, dbfile$ [, createonfail ] )

'Parameters
'server$

'String. The name of the server on which the database resides.
'Use an empty string ("") to indicate a database on the current computer:
'if the script runs on the workstation, the empty string indicates a local database;
'if the script runs on a server, it indicates a database on that server.


'dbfile$

'String. The file name and location of the database within the Notes data directory.
'Use empty strings for both dbfile$ and server$ if you want to open the database later.
'Use a full path name if the database is not within the Notes data directory.


'createonfail

'Note This parameter is new with Release 5.

'Boolean. Optional. Specify True (default) to create the database if it does not exist.


'Return value
'NotesDatabase

'A NotesDatabase object that can be used to access the database you've specified.


'If a database exists at the server$ and dbfile$ specified,
'the NotesDatabase object is open, and the script can access all its properties and methods.

'If a database does not exist at the server$ and dbfile$ specified,
'the NotesDatabase object is closed. To create a new database at the
'specified location if one does not exist, specify True for createonfail.


'Ivan

[brettvba]

Cheers fellas its hard to find many knowledgable fellas in little old NZ
much apprecited

[NateO]

Brett,

Looks like Ivan's your neighbor. And judging by the 'unlock the vba' code demonstrated a few days ago, the man has skills.

Back to the matter at hand. I personally prefer to type the message in a worksheet cell than in the VBE. This is because it's easier to enter hard returns and line-spaces in your message body (alt-enter and space-bar on blank lines) than in the VBE (using chr(13)[s], etc....). Just throw that out there in terms of user-ability.

Nice work Ivan, good hunting Brett.

Cheers,

Nate

[ This Message was edited by: NateO on 2002-02-20 18:19 ]

[brettvba]

With your particular one Nate how to you send to say 10 or 15 ppl in the "To" Field
??