sending workbooks with Lotus Notes

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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.
 
Upvote 0
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
 
Upvote 0
Yeah I know there is something there under the references but otherwise not a big deal thanks anyway!
 
Upvote 0
I'm in a hurry and gotta run, so I can't go through it but the following works for me:

Code:
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
 
Upvote 0
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
 
Upvote 0
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), 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
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top