Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 26

Thread: sending workbooks with Lotus Notes

  1. #1
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    HI

    Thats blank also please try again

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

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  6. #6
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  9. #9
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •