Lotus Notes copying cells from excel and mailing them

Hi,

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
gghalebAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

madheeswarCommented:
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:
\June282001.xls")

 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


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

 Print "Starting import from Excel file..."

 Do While True
Finish:
 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
 Else
 Print written
 Messagebox "Finished"
 Goto Done
 End If
 End With
 Loop
 Return
Done:
End Sub
0
marilyngCommented:
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,  
0
cezarFCommented:
try this to select and copy cells from an excel  worksheet.

    xlSheet.Range("A1:F6").Select
    Excel.Selection.Copy

xlSheet = excel worksheet object , Excel = excel application object  as used in madheeswar's sample above.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

marilyngCommented:
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?
0
gghalebAuthor Commented:
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
0
cezarFCommented:
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.
0
marilyngCommented:
Yup, which is what I said would be the problem.
0
marilyngCommented:
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

Worksheets("Sheet1").Activate
Worksheets("Sheet1").Range("Range1").Select
Worksheets("Sheet1").Range("Range1").Copy


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)
        Else
            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
 
ErrorMsg:
    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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.