Lotus Notes copying cells from excel and mailing them

Posted on 2006-04-17
Last Modified: 2013-12-18

I am wordering if there is a way in Lotus Notes (or any other means) to be able to automatically program it to highlight certain cells in MS Excel, copy them to clipboard, create a new email, paste them to this email, read another cell to get the email address, and send the email off.

is this possible?

I do not want to send a file as an attachment, I want the cells to be pasted directly into the body of the email.

This is important, I need your help.

Many Thanks
Question by:gghaleb
    LVL 19

    Expert Comment

    I got it from Web somewhere...

     This imports data from Excel into Lotus Notes, skipping blank lines and column headers. This will only import 100 records at a time and can be changed by changing the written value.

    Sub Initialize
     Dim FileNum As Integer
     Dim xlFilename As String

     Filenum% = Freefile()
     xlFileName$ = Inputbox("What file name and path? example:H:

     Dim session As New NotesSession
     Dim db As NotesDatabase
     Dim view As NotesView
     Dim doc As NotesDocument
     Set db = session.CurrentDatabase
     Set doc = New NotesDocument(db)
     Dim One As String

     Dim row As Integer
     Dim written As Integer

     '// Next we connect to Excel and
    open the file. Then start pulling over the records.
     Dim Excel As Variant
     Dim xlWorkbook As Variant
     Dim xlSheet As Variant
     Print "Connecting to Excel..."
     Set Excel = CreateObject( "Excel.Application.8" )
     Excel.Visible = False '// Don't display the Excel window
     Print "Opening " & xlFilename & "..."
     Excel.Workbooks.Open xlFilename '// Open the Excel file
     Set xlWorkbook = Excel.ActiveWorkbook
     Set xlSheet = xlWorkbook.ActiveSheet

     '// Cycle through the rows of the
    Excel file, pulling the data over to Notes
     Goto Records
     Print "Disconnecting from Excel..."
     xlWorkbook.Close False '// Close the Excel file without saving (we
    made no changes)
     Excel.Quit '// Close Excel
     Set Excel = Nothing '// Free the memory that we'd used
     Print " " '// Clear the status line

     row = 0 '// These integers intialize to zero anyway
     written = 0

     Print "Starting import from Excel file..."

     Do While True
     With xlSheet
     row = row + 1
     Set view = db.GetView("Main View")
     Set doc = db.CreateDocument '// Create a new doc
     doc.Form = "ImportForm1"

     If .Cells (row, 1).Value = "" And .Cells(row,2).Value = ""
    And .Cells (row, 3).Value = "" And .Cells(row,4).Value = "" And .Cells
    (row, 5).Value = "" And .Cells(row,6).Value = "" And .Cells (row, 7).Value
    = "" And .Cells(row,8).Value = "" And .Cells (row, 9).Value = "" And
    .Cells(row,10).Value = ""Then
     Goto Finish
     End If

     If .Cells (row, 1).Value = "PO #" And .Cells(row,2).Value
    = "Order #" And .Cells (row, 3).Value = "Order da" And .Cells(row,4).Value
    = "Part #" And .Cells (row, 5).Value = "Or" And .Cells(row,6).Value
    = "Line " And .Cells (row, 7).Value = "Qty" And .Cells(row,8).Value
    = "Unit pri" And .Cells (row, 9).Value = "Ship to Company" And
    .Cells(row,10).Value = "Ship method"Then
     Goto Finish
     End If

     doc.SWEPO = .Cells( row, 1 ).Value
     doc.SWEORDER = .Cells(row, 2 ).Value
     doc.SWEORDERDATE = .Cells(row, 3).Value
     doc.ITEMNUMBER = .Cells( row, 4 ).Value
     doc.ORDERSTATUS = .Cells(row, 5).Value
     doc.QUANTITYORDERED = .Cells( row, 6).Value
     doc.AMOUNTBILLED = .Cells(row, 7).Value
     doc.SHIPMETHOD = .Cells( row, 8).Value
     doc.SHIPDATE = .Cells(row, 9).Value
     doc.TRACKINGNUMBER = .Cells(row, 10).Value

     Call doc.Save( True, True ) '// Save the new doc

     written = written + 1
     Print written
     If written = 5 Then
     Print written
     Goto Finish
     Print written
     Messagebox "Finished"
     Goto Done
     End If
     End With
    End Sub
    LVL 18

    Expert Comment

    Unfortunately, that will help to read the file, but not to paste it into new email memo.  

    What you ask is somewhat possible, but a lot of coding either from excel or from notes.

    The way to open a file is above, but after you read "cells", you also need to compose a mail memo on the back end, and fill the values you want pasted into the rich text field, and then open the email for editing.

    The problem you're always going to have is the control of the Excel worksheet.  The "copied" cells have to consistently have the same value, in the same location, else every time you run your agent, your copied or mail to values just won't work.

    So, you either have to construct the process in excel visual basic or move the entire process into a Notes database just for consistency.

    Either way, you'll have to start with your code and then ask us to help,  
    LVL 14

    Expert Comment

    try this to select and copy cells from an excel  worksheet.


    xlSheet = excel worksheet object , Excel = excel application object  as used in madheeswar's sample above.
    LVL 18

    Expert Comment

    Now all you need to do is open the new mail memo and use  a windows API call to paste the clipboard into the  body section of the memo.  
    Now, automatically copy means you need a bit more logic, doesn't it?

    Author Comment

    Ok I will be doing this from excel, There is a lotus notes API that can be used from excel.
    I will be using that, thank you for your help
    LVL 14

    Expert Comment

    much easier then. in excel, record a macro to select and copy cells then use the generated code to automate the process. the problem i see though is that when it comes to pasting the cells in the body of the email that your program will be sending.
    LVL 18

    Expert Comment

    Yup, which is what I said would be the problem.
    LVL 18

    Accepted Solution

    Try this - it will paste the excel image into a NotesDocument and email it.   There is both an attach and embed, to allow for mail programs that strip embedded.  PRoblem is that you end up still in excel, and there is a save on send? messagebox in Notes... :)  So you have to switch to the active window.

    Public Function SendEMail(SendTo As String, EmailSubject As String, MyAttachment As String) As Boolean
    SendEMail = True
    Dim myRange As Range
    Const EMBED_ATTACHMENT As Integer = 1454
    Const EMBED_OBJECT As Integer = 1453
    Const EMBED_OBJECTLINK As Integer = 1452


    On Error GoTo ErrorMsg
        Dim EmailList As Variant
        Dim ws, uidoc, session, db, uidb, NotesAttach, NotesDoc, objShell As Object
        Dim RichTextBody, RichTextAttachment As Object
        Dim server, mailfile, user, usersig As String
        Dim SubjectTxt, MsgTxt As String
        Set session = CreateObject("Notes.NotesSession")
        If session Is Nothing Then
            MsgBox "Sorry, unable to instantiate the Notes Session", vbOKOnly, "Unable to Continue"
            SendEMail = False
            Exit Function
        End If
        user = session.UserName
        usersig = session.CommonUserName
        server = ""
        'server = session.GetEnvironmentString("MailServer", True)
        mailfile = session.GetEnvironmentString("MailFile", True)
        Set db = session.GetDatabase(server, mailfile)
        If Not db.IsOpen Then
            Call db.Open("", "")
        End If
        If Not db.IsOpen Then
            MsgBox "Sorry, unable to open: " & mailfile, vbOK, "Unable to Continue"
            SendEMail = False
            Exit Function
        End If
        Set NotesDoc = db.createdocument
        With NotesDoc
            .form = "Memo"
            .Principal = user
            .Subject = EmailSubject 'The subject line in the email
            .SendTo = SendTo  'temp variant for now
        End With
        Set RichTextBody = NotesDoc.CreateRichTextItem("Body")
        If Not RichTextBody Is Nothing Then
            If MyAttachment <> "" Then
                Call RichTextBody.addnewline(2)
                Call RichTextBody.appendText("Please find the following file attached: " & MyAttachment)
                Call RichTextBody.addnewline(1)
                Call RichTextBody.embedObject(EMBED_ATTACHMENT, "", MyAttachment)
                Call RichTextBody.addnewline(1)
                Call RichTextBody.addnewline(2)
                Call RichTextBody.appendText("There were no excel files to attach to this notice")
                Call RichTextBody.addnewline(1)
            End If
        End If
        With NotesDoc
            .computewithform False, False
            .savemessageonsend = True
            .Save True, False, True
        End With
        'Now set the front end stuff
       Set ws = CreateObject("Notes.NotesUIWorkspace")
       If Not ws Is Nothing Then
       Set uidoc = ws.editdocument(True, NotesDoc)
        If Not uidoc Is Nothing Then
             If uidoc.editmode Then
               Call uidoc.gotoField("Body")
               Call uidoc.Paste
             End If
         End If
       End If
       With NotesDoc
            .postedDate = Date
            .Save True, False, True
            .SaveOptions = "0"
            .send False
       End With
        Set session = Nothing  'close connection to free memory
        Set db = Nothing
        Set NotesAttach = Nothing
        Set NotesDoc = Nothing
        Set uidoc = Nothing
        Set ws = Nothing
     Exit Function
        On Error GoTo 0
        SendEMail = False
        MsgBox "Sorry there was an error processing the request: " + Error$ + "-" + Str(Err), vbOKOnly, "Error"
        Set session = Nothing  'close connection to free memory
        Set db = Nothing
        Set NotesAttach = Nothing
        Set NotesDoc = Nothing
        Set ws = Nothing
       Exit Function
    End Function

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    For users on the Lotus Notes 8 Standard client, this article provides information on checking the Java Heap size and adjusting it to half of your system RAM in attempt to get the Lotus Notes 8.x Standard client to run faster.  I've had to exercise t…
    Article by: Rob
    Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now