Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Lotus Notes copying cells from excel and mailing them

Posted on 2006-04-17
Medium Priority
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

ID: 16468702
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

ID: 16470336
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

ID: 16476788
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.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 18

Expert Comment

ID: 16482892
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

ID: 16485594
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

ID: 16486144
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

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

Accepted Solution

marilyng earned 2000 total points
ID: 16621685
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You’ve got a lotus Domino web server, and you have been told that “leverage browser caching” is a must do. This means that we have to tell the browser everywhere in the web to use cache. In other words, we set (and send) an expiration date in the HT…
Lack of Storage capacity is a common problem that exists in every field of life. Here we are taking the case of Lotus Notes Emails, as we all know that we are totally depend on e-communication i.e. Emails. This article is fully dedicated to resolvin…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Screencast - Getting to Know the Pipeline
Suggested Courses
Course of the Month13 days, 12 hours left to enroll

581 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