gghaleb
asked on
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
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
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,
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,
try this to select and copy cells from an excel worksheet.
xlSheet.Range("A1:F6").Sel ect
Excel.Selection.Copy
xlSheet = excel worksheet object , Excel = excel application object as used in madheeswar's sample above.
xlSheet.Range("A1:F6").Sel
Excel.Selection.Copy
xlSheet = excel worksheet object , Excel = excel application object as used in madheeswar's sample above.
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?
Now, automatically copy means you need a bit more logic, doesn't it?
ASKER
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
I will be using that, thank you for your help
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.
Yup, which is what I said would be the problem.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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