Another Lotus Notes Query

  • Gidday -

    I have been working on some code which I got from Ivan Moala written by Dennis XL that allows you to email from within Excel. Now the code works fine but what I need to do is create another object as a workbook copy a sheet from the active workbook then attach this to the e-mail. The code I have been working on simply copys a range from a sheet the pastes this onto the e-mail. Have had a number of attempts and all crash.

    Have read another query in this forum but there is no link???.

    It's always the little details that cause the problems!!!!!!:exclamat::exclamat:

    PS - this formum has helped me heaps - thanks to all for the help I've recieved.


  • Hi Phil,

    Below You find a procedure that copy a formatted named range into the body of an outgoing e-mail:

    Option Explicit
    Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, _
    ByVal lpWindowName As String) As Long

    Sub Lotus_Formatted_Range_Into_Body()
    Dim oWorkSpace As Object, oUIDoc As Object
    Dim stTo As String, stCC As String, stSubject As String, stBody As String
    Dim rnBody As Range
    Dim lnRetVal As Long

    'Lotus Notes must be running in order to get the Paste-function to work properly...
    'Although the body has the focus it will not paste from the clip-board.
    'Even if no formhead are in use it will not work.

    lnRetVal = FindWindow("NOTES", vbNullString)

    If lnRetVal = 0 Then
    MsgBox "Lotus Notes must be open in order to execute this procedure.", vbInformation, "Systemerror - Lotus Notus"
    Exit Sub
    End If

    Application.ScreenUpdating = False

    Set oWorkSpace = CreateObject("Notes.NotesUIWorkspace")

    stTo = "[email protected]"
    stCC = "[email protected]"
    stSubject = "Test"
    'stBody = "As per agreed"

    'In the active sheet a named range is used
    Set rnBody = ActiveSheet.Range("rnbody")

    On Error Resume Next
    'The error-message "Unable to find Window" is a known bug and it generate different
    'error-messages depending on which version (4.x / 5.x) that´s running.
    'Make sure You have open the view "Post" and the use the command
    'File | Database | Properties to find both the server and the maildatabase.
    'Here are my settings without any Domino-server.
    Set oUIDoc = oWorkSpace.ComposeDocument("", "mail\xldennis.nsf", "Memo")
    On Error GoTo 0

    Set oUIDoc = oWorkSpace.CurrentDocument

    Call oUIDoc.FieldSetText("EnterSendTo", stTo)
    Call oUIDoc.FieldSetText("EnterCopyTo", stCC)
    Call oUIDoc.FieldSetText("Subject", stSubject)
    'Call oUIDoc.FieldSetText("Body", stBody)
    Call oUIDoc.GoToField("Body")
    Call oUIDoc.Paste

    Call oUIDoc.Send(False)
    Call oUIDoc.Save(True, False, False)
    Call oUIDoc.Close

    Set oUIDoc = Nothing

    With Application
    .CutCopyMode = False
    .ScreenUpdating = True
    End With

    MsgBox "The e-mail have been created, saved but not sent.", vbInformation

    AppActivate "Notes"
    End Sub

    It still on a working-stage but have been reported to work well when server and maildatabase have been hardcoded.

    Please let me know the outcome of it :)

  • I have been playing with this code:

    Now this will send an e-mail to one recipent, but idealy I want to send the selected range as an attachment .i.e create a new workbook copy the range across and then attach it. This code get's me 60% of the way there. Am now talking to our IT manager who thinks he may have some other code so will send this when I manage to get this thing to fly.................. Thanks for the code, I'm still playing with it but it's a bit over my head!!!!!!!!!!!!!!:tumble:

  • OK, this works within Excel 2000 - attaches the spreadsheet detailed in the pathway - needs work to sort out and fine tune but................

    Still a problem with the error handling on the error routine but.......... the e-mail carried the attachment!!!! :jumpupdo: :viking: :thumbcoo:

  • XL-Dennis,

    I got your paste script to work, but the paste is too big for Lotus Notes standard paste. Is there anything that I can add to it to make it (Paste Special) = Bitmap



  • Hi All,

    Im a new user on the forum and I am also a new user of VBA.

    I am interested in learning more and wanted to see if this lotus notes code could be modified to allow for you to:

    1) Predefine who the email is sent to
    2) Assign the active sheet to be the attachment.

    Basically automating the process more??

    If anyone can help that would be great.

    I can send attachments of what im trying to do if that helps

  • Just updating - improved the attachment system to allow you to browse the hard drive - could be improved for mulitiple attachments by following the way the attachment system works and running a loop

  • Attachment system must still be down!!!

    No major here is the code :)

    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 recipient As String
    Dim ccRecipient As String
    Dim bccRecipient As String
    Dim subject As String
    Dim bodytext As String
    Dim Attachment1 As String
    Option Explicit

    Private Sub CommandButton1_Click()
    ' setting up all sending recipients
    recipient = tobox.value
    ccRecipient = copy.value
    bccRecipient = Bcopy.value
    subject = subject1.value
    bodytext = TextBox1.value

    '// Lets check to see if form is filled in Min req =Recipient, Subject, Body Text
    If recipient = vbNullString Or subject = vbNullString Or bodytext = vbNullString Then
    MsgBox "Recipient, Subject and or Body Text is NOT SET!", vbCritical + vbInformation
    Exit Sub
    End If

    ' creating a notes session
    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
    On Error Resume Next
    End If

    Set MailDoc = Maildb.CreateDocument
    MailDoc.Form = "Memo"

    ' loading the lotus notes e-mail with the inputed data
    With MailDoc
    .sendto = recipient
    .copyto = ccRecipient
    .blindcopyto = bccRecipient
    .subject = subject
    .body = bodytext
    End With

    ' saving message
    MailDoc.SaveMessageOnSend = True

    If Attachment1 <> "" Then
    Set attachME = MailDoc.CREATERICHTEXTITEM("Attachment1")
    Set EmbedObj1 = attachME.EMBEDOBJECT(1454, "", Attachment1, "Attachment")
    MailDoc.CREATERICHTEXTITEM ("Attachment")
    End If

    ' send e-mail !!!!
    MailDoc.PostedDate = Now()

    ' if error in attachment or name of recipients
    On Error GoTo errorhandler1

    MailDoc.send 0, recipient

    Set Maildb = Nothing
    Set MailDoc = Nothing
    Set attachME = Nothing
    Set session = Nothing
    Set EmbedObj1 = Nothing

    MsgBox "Your E-mail has been sent sucessfully to " & tobox.value & (Chr(13)) & "That you for using this wonderful e-mail code"

    Unload Me
    Exit Sub
    ' setting up the error message

    MsgBox "Incorrect name supplied or incorrect names supplied please re-type in the names," & _
    "or your Lotus Notes has not opened correctly. Recommend you open up Lotus Notes" & _
    "to ensure the application runs correctly and that a vaild connection exists"

    End Sub

    Private Function GetAttach() As String
    Dim strFileFullPath As String

    strFileFullPath = Application.GetOpenFilename("Xl Files (*.xls), *.xls")
    If strFileFullPath = "False" Then Exit Function
    GetAttach = strFileFullPath

    End Function

    Private Sub CommandButton2_Click()
    Set Maildb = Nothing
    Set MailDoc = Nothing
    Set attachME = Nothing
    Set session = Nothing
    Set EmbedObj1 = Nothing

    Unload Me

    End Sub

    Private Sub CommandButton3_Click()

    ' setting and attaching the work book
    Attachment1 = GetAttach 'ThisWorkbook.Path + "/" + "Book1.xls"

    attach.Text = Attachment1

    End Sub

    - Attachment is via a control button on the user form - the pathway is shown in a text box called attach - You don't need it but people like to see that something has actually happened so the text box simply displays the pathway - in my userform it's disabled so the pathway can't be played with just looked at.


  • I received an e-mail request for this so thought I'll try and re-attach the spreadsheet.

    Here is the latest version, code as above - Hopefully the attachment system is operational!!!


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!