Lotus Notes Q: Copy'n paste from Excel AND place Excel attachment

[Guest]

Hi all,

I know the way to place Excel file as attachement in Lotus Notes memo and also know the way to copy a range in Excel and paste it as part of body (although scaling is VERY dodgy!).

I OBVIOUSLY browsed past Newsgroup postings and even IBM Web site! NO info... And, I did so Notes type libraries (domobj.tlb and notes32.tlb) .

What I can NOT do is achieving both! The first code does copy and paste of Excel range as part of body (NOT text, but image). The second does create attachement. I spotted both codes from somewhere on the web (thank to the coders!).

Can anyone shed a light to acheive both please?

Cheers,
=============
Sub CopyAndPaste()
Dim Server As String, Database As String
Dim Notes As Object
Dim db As Object
Dim WorkSpace As Object
Dim doc As Object
Dim UIdoc As Object
Dim AttachME As Object
Dim EmbedObj As Object

Set Notes = CreateObject("Notes.NotesSession")
Set db = Notes.GETDATABASE("", "")
Set WorkSpace = CreateObject("Notes.NotesUIWorkspace")
Set doc = WorkSpace.ComposeDocument(Server, Database, "Memo")

Set UIdoc = WorkSpace.CurrentDocument
Call UIdoc.FieldSetText("EnterSendTo", "tetsuya oguma")
Call UIdoc.FieldSetText("Subject", "Pasting from Excel to Notes")

Range("test").Copy 'the range to copy
Call UIdoc.GotoField("Body")
Call UIdoc.Paste
Call UIdoc.SEND
UIdoc.Close

Set Notes = Nothing
Set db = Nothing
Set WorkSpace = Nothing
Set doc = Nothing
Set UIdoc = Nothing
End Sub
=====================
Public Sub SendAttachment()
Dim Maildb As Object 'The mail database
Dim UserName As String 'The current users notes name
Dim MailDbName As String 'THe current users notes mail database name
Dim MailDoc As Object 'The mail document itself
Dim AttachME As Object 'The attachment richtextfile object
Dim Session As Object 'The notes session
Dim EmbedObj As Object 'The embedded object (Attachment)

Set Session = CreateObject("Notes.NotesSession")

Set Maildb = Session.GETDATABASE("", "")
If Maildb.ISOPEN = True Then
Else
Maildb.OPENMAIL
End If

Set MailDoc = Maildb.CREATEDOCUMENT
MailDoc.Form = "Memo"
MailDoc.sendto = "Tetsuya Oguma"
MailDoc.Subject = "This is subject"
MailDoc.Body = "BodyText"
MailDoc.SAVEMESSAGEONSEND = False

Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", "C:\MyFile.xls", "Attachment")
MailDoc.CREATERICHTEXTITEM ("Attachment")

MailDoc.PostedDate = Now()
MailDoc.SEND 0, Recipient

Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj = Nothing
End Sub

 

[Guest]

Sorry I forgot to tell I am using Excel 97 SR-2 on Windows NT4.

Cheers,