Lotus Notes Email From VBA Excel Spreadsheet - Paste Special as Rich Text

kj8hr14,

consider a workaround?

1 after copying the cells from excel, create another excel automation object and paste the copied cells
2 save the newly created excel automation object
3 embed the saved file in the body as an embedded object (type = EMBED_OBJECT/1453)

hope it helps.

cezarF Hi

Your suggestion sounds very familiar to one I found on this site. I have nevertheless tried it - but with no luck. I get the following error: "424Object required" on the last line of the code below.

This is the code I inserted to create the new excel workbook and to add the embedded object. Note even though I paste formats I still need to adjust the width of the columns in the new workbook.

Workbooks.Add
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlValues
    Selection.PasteSpecial Paste:=xlPasteFormats
    Columns("A:A").ColumnWidth = 17#
    Columns("B:B").ColumnWidth = 25#
    Columns("C:C").ColumnWidth = 25#
    Columns("D:D").ColumnWidth = 25#
    Columns("G:G").ColumnWidth = 25#
    ActiveWorkbook.SaveAs filename:="C:\EmailBody.xls", FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
       
    Set RichTextView = NotesDoc.CreateRichTextItem("Attachment")
    Set ViewAttach = RichTextAttachment.EmbedObject(1453, "", "C:\EmailBody.xls")

Marilyng thanks for your suggestions. Would you mind going into more detail on the windows send keys, an example perhaps.

kj8hr14,

your "RichTextAttachment" is not instantiated. try this,  Set ViewAttach = RichTextView.EmbedObject(1453, "", "C:\EmailBody.xls")
   
in addition, should the >>Set RichTextView = NotesDoc.CreateRichTextItem("Attachment")<< be
>>Set RichTextView = NotesDoc.CreateRichTextItem("Body")<<?

sorry, got itchy fingers...

as for the format of the new excel file, you can:
1 create the new excel file  using a pre-formated excel template (.xlt).  or
2 you can do the formatting programatically. if you are not sure about the commands, open a blank excel sheet, record a macro (Tools, Macro, Record ne Macro), do the formatting, stop recording. then edit the macro (alt+F8) select the macro and click Edit. you should be able see vba code which can be translated to lotusscript with no/minor change.

Try these:

http://www.winguides.com/scripting/reference.php?id=149
http://www-10.lotus.com/ldd/nd6forum.nsf/55c38d716d632d9b8525689b005ba1c0/d44d074221447880852570ca00500b5b?OpenDocument&Highlight=0,sendkeys

Hi cezarF

Thanks for spotting my error. Have got it to work however it doesn't exactly solve my problem. To reitterate I need to display the contents of the workbook, ie, the range of cells from my original workbook, in the body of the email (but NOT as an image). Your suggestion embeds the file which then has to be opened, something that can't be achieved by those using Blacberry phones.

Hi marilyng

Tried your Sendkeys suggestion. Beause I'm running the code from within the Excel Visual Basic Editor, the menus in the VB Editor are being accessed (as it is the active app) instead of the menus in Lotus Notes. Do you know how to access the Lotus Notes menus using SendKeys from within Excel VB Editor?

I'm using the following code:

Set WshShell = CreateObject("WScript.Shell")
   
    Call uidoc.GOTOFIELD("Body")
   
    WshShell.SendKeys "%E"

   i = 1
   
    For i = 1 To 7
        WshShell.SendKeys "{DOWN}"
        i = i + 1
    Loop
   
    WshShell.SendKeys "{ENTER}"
    WshShell.SendKeys "{DOWN}"
    WshShell.SendKeys "{ENTER}"
   
    Call uidoc.Save

Hmmm... never tried this using windows scripting..

Dim ProcID As Integer
' Start the Calculator application, and store the process id.
ProcID = Shell("CALC.EXE", AppWinStyle.NormalFocus)
' Activate the Calculator application.
AppActivate(ProcID)
SendKeys.SendWait("22")
SendKeys.SendWait("*")
SendKeys.SendWait("44")
SendKeys.SendWait("=")

Alternatively, you can use FindWindow and SetForegroundWindow to force focus.

----
Let's see, wshshell..
you'd have to use  do something like this wih run and appActivate...
 set WshShell = WScript.CreateObject("WScript.Shell")
         WshShell.Run "calc"
         WScript.Sleep 100
         WshShell.AppActivate "Calculator"
         WScript.Sleep 100
         WshShell.SendKeys "1{+}"
         WScript.Sleep 500
         WshShell.SendKeys "2"
         WScript.Sleep 500
         WshShell.SendKeys "~"
         WScript.Sleep 500
         WshShell.SendKeys "*3"
         WScript.Sleep 500
         WshShell.SendKeys "~"
         WScript.Sleep 2500

So,thinking you have to create the memo back end, and then open it front end before you call the appactivate, or force focus.

Have just tried something like this that I found during a search of this site - with no luck. (see http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_21544651.html?qid=21544651)

Well, the link says it all... including the sleep.

The problem you're having is getting a link on the window title, which means in your app you have to get a handle on it so you can pass it to windows.

I have tried the following still with no luck, ie, still get nothing in body of email
 
WshShell.AppActivate ("Wellington Risk Roundup - May 1 2006 - Lotus Notes")
WshShell.SendKeys "%E"

 Sleep 15
   
    ThisWorkbook.Activate
    i = 1
   
    For i = 1 To 7
        objShell.AppActivate ("Wellington Risk Roundup - May 1 2006 - Lotus Notes")
        objShell.SendKeys "{DOWN}"
        DoEvents
        ThisWorkbook.Activate
        i = i + 1
    Next
   
    objShell.AppActivate ("Wellington Risk Roundup - May 1 2006 - Lotus Notes")
    objShell.SendKeys "{ENTER}"
    objShell.SendKeys "{DOWN}"
    objShell.SendKeys "{ENTER}"
   
    ThisWorkbook.Activate
    Call uidoc.Save

How many columns and rows are we talking about here?

6 columns and 5 rows.

Just thinking, and I know you're searching for a quick solution, but the only consistent way to get the table from excel into a Notes Email is maybe to build it.  You can cycle through your excel spreadsheet so you know the number of cells and rows.
With that information you can declare a  richtexttable and build it.

See Examples: AppendTable method  in Designer Help

Here's one example from help:
 2.      This view action creates a basic auto-width table of 4 rows and 3 columns, and populates it.
Sub Click(Source As Button)
  Dim session As New NotesSession
  Dim db As NotesDatabase
  Set db = session.CurrentDatabase
  REM Create document with Body rich text item
  Dim doc As New NotesDocument(db)
  Call doc.ReplaceItemValue("Form", "Main topic")
  Call doc.ReplaceItemValue("Subject", "Table 4 x 3")
  Dim body As New NotesRichTextItem(doc, "Body")
  REM Create table in Body item
  rowCount% = 4
  columnCount% = 3
  Call body.AppendTable(rowCount%, columnCount%)
  REM Populate table
  Dim rtnav As NotesRichTextNavigator
  Set rtnav = body.CreateNavigator
  Call rtnav.FindFirstElement(RTELEM_TYPE_TABLECELL)
  For iRow% = 1 To 4 Step 1
    For iColumn% = 1 To 3 Step 1
      Call body.BeginInsert(rtnav)
      Call body.AppendText("Row " & iRow% & ", _
      Column " & iColumn%)
      Call body.EndInsert
      Call rtnav.FindNextElement(RTELEM_TYPE_TABLECELL)
    Next
  Next
  REM Save document and refresh view
  Call doc.Save(True, False)
  Dim ws As New NotesUIWorkspace
  Call ws.ViewRefresh
End Sub

So, and I haven't checked to see if the properties are available in excel, but if not there might be other possibilites.. tell me, is the excel spreadsheet the first point.. that is, the user opens the spreadsheet, modifies it and then sends it from Excel?

Is there a reason why the spreadsheet  is maintained separately?  Can it be part of a database office library? (for instance)  where you have the spreadsheet, can modify it and send it.. (see the Microsoft Office Library template maybe, not only for using it, or exploring the methods used in the template for collecting the excel data, and seeing if that data displays correctly)

To create a Microsoft Office LIbrary, in Notes do: File>>Database>>New, and select the Microsoft Office Library Template.

THe difference here is that the database holds the excel spreadsheet, where it can be modified, and the modification history is maintained so that there's never a doubt about which is the latest.. dunno.. just a thought.

The spreadsheet is part of an end of day reporting process. It has links to market data (Reuters). We have a scheduled task to start excel and open the spreadsheet at 6.30pm every evening. There is an open event upon which a macro is run (automatically). This macro generates an email which is sent to a number of people around the globe.

Thanks for your other suggestions. I will give them a go.

kj8hr14 ,

it will not be an image if you attach the new excel file as an embedded object. you need to close the file first before embedding it in the body of the email.

cezarF, was wondering if you can spot any problems in my code that could explain why this doesn't work for me.
Code as follows:

Function SendEMail(SheetName As String, EmailBody As String, EmailSubject As String, MyAttachment As String)

Application.DisplayAlerts = False

On Error GoTo ErrorMsg
   
    Dim EmailList As Variant
    Dim ws, uidoc, session, db, uidb, NotesAttach, NotesDoc, objShell As Object
    Dim RichTextBody, RichTextAttachment, ViewAttach As Object
    Dim StyleBold, StyleNorm, StyleUnderline, StyleFont10 As Object
    Dim server, mailfile, user, usersig As String
    Dim SubjectTxt, MsgTxt As String
    Dim data
    Dim group1, group2, group3 As String
    Dim i As Integer
    Dim h As Long
       
    'The email groups are contained in a named range in my worksheet. I create an email list
    'from these named ranges
    group1 = Range("EmailList1")
    group2 = Range("EmailList2")
    EmailList = Array(group1, group2)
     
    Set ws = CreateObject("Notes.NotesUIWorkspace")
    Set session = CreateObject("Notes.NotesSession")
   
    user = session.UserName
    usersig = session.CommonUserName
    server = session.GetEnvironmentString("MailServer", True)
    mailfile = session.GetEnvironmentString("MailFile", True)
   
    Set db = session.GetDatabase(server, mailfile)
    Set uidb = ws.CURRENTDATABASE
    Set NotesDoc = db.createdocument
       
    Set RichTextBody = NotesDoc.CreateRichTextItem("Body")
    'Attach spreadsheet
    If MyAttachment <> "" Then
        Set RichTextAttachment = NotesDoc.CreateRichTextItem("Attachment")
        Set NotesAttach = RichTextAttachment.EmbedObject(1454, "", MyAttachment)
    End If
   
    NotesDoc.Subject = EmailSubject 'The subject line in the email
   
    NotesDoc.SendTo = user 'I send the email to myself
       
    NotesDoc.CopyTo = EmailList ' Others that I send the email to
       
    Set uidoc = ws.EDITDOCUMENT(True, NotesDoc)
 
    ThisWorkbook.Activate
    Sheets(SheetName).Select
    Sheets(SheetName).Range(EmailBody).Select
    Selection.Copy
Call uidoc.GOTOFIELD("Body")
   
    Workbooks.Add
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlValues
    Selection.PasteSpecial Paste:=xlPasteFormats
    Columns("A:A").ColumnWidth = 17#
    Columns("B:B").ColumnWidth = 25#
    Columns("C:C").ColumnWidth = 25#
    Columns("D:D").ColumnWidth = 25#
    Columns("G:G").ColumnWidth = 25#
    ActiveWorkbook.SaveAs filename:="\\wlgsrv1\vaxfiles\NZEaros\EmailBody.xls", FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
       
    ActiveWorkbook.Close
   
    Set ViewAttach = RichTextBody.EmbedObject(1453, "", "\\wlgsrv1\vaxfiles\NZEaros\EmailBody.xls")

ThisWorkbook.Activate
    Call uidoc.Save
       
    'I commented this out because I get a msg box asking me if I want to save and send,
    'send only, discard or cancel changes and I don't know how to get rid of the msg box
   
    'Call uidoc.Close(True)
   
    Set NotesDoc = uidoc.DOCUMENT
    Call NotesDoc.Save(True, True)
   
    'This has to be set to False or we get errors
    NotesDoc.SAVEMESSAGEONSEND = False
    'Keep this set to false.
    NotesDoc.SEND False
   
'**************************************************************************************
'This piece has been added to get around the problem of stopping the clear clipboard
'prompt'

    Sheets("Email Message").Select
    Sheets("Email Message").Range("A1").Select
    Selection.Copy
'**************************************************************************************

    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:    If Err.Number = 7225 Then
            MsgBox "The file " & Range("Fname_NZ_VaR") & " cannot be found in the location " & _
            Range("Path_NZ_VaR"), vbOKOnly, "Error"
    ElseIf Err.Number = 1004 Then
            MsgBox "One of the following may be causing an error:" & vbCrLf & _
            "1. The range 'Path_NZ_VaR' and/or 'Fname_NZ_VaR' does not exist in this spreadsheet," & _
            vbCrLf & "2. The range 'Fname_NZ_VaR' does not contain a filename," & vbCrLf _
            & "3. The path " & Range("Path_NZ_VaR") & " does not exist.", vbOKOnly, "Error"
    Else
            MsgBox Err.Number & Err.Description
    End If
   
End Function

kj8hr14,
sorry,  i can't seem to understand your code and unfortunately, i don't have the resources here to test it.

do you mind sending your vba code to me? if it is in excel, please send the file itself. my email address is found in my profile here in EE.

regards

Ok, this code works.  Here's what I did
I created a range on the active worksheet to copy and paste, but skipped saving the file in excel in order to attach it.
The reason that you need to BOTH attach and embed is in case the email program strips the embedded stuff, then the user gets the attached anyway.

I also skipped your sendto, and other ranges, since you seem capable of doing that.  
-------------------------------------------------

Public Function SendEMail(SendTo As String, EmailSubject As String, MyAttachment As String) As Boolean
SendEMail = True
Dim myRange As Range   'I set a range on the spreadsheet
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

Oh, the other thing I did was to have the function return a success or fail value.. :)

Hi marilyng

Good to hear from you. Just checking if I'm missing something but your code looks very similar to what I sent through initially with my question. Hoping I'm wrong about this...

The line:
Call uidoc.Paste
will paste the range as an image.

I need to mimic the manual steps of Selecting Edit, Paste Special, Rich Text, in order for users of cell phones to be able to view the email.

STill rough.. the problem is that the mail form is set to present the mail send prompt, so you're always stuck with the email, or worse, with duplicate copies.  Workaround - maybe create a special email form that has the property checked off, or use an email file where the property on that form is disabled.  

This one embeds the worksheet..  and deesn't save the email, but it will leave it up on the screen.  Also, I noticed that if you have Notes Open, it kinda creates a new instance.. hmmm. Should probably add a function to grab the existing window..

Declare Function SetForegroundWindow Lib "user32" (ByVal hwnd As Long) As Long
Declare Function ShowWindow& Lib "user32" (ByVal hwnd As Long, ByVal nCmdShow As Long)
Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long


---------------
Public Function SendEMail(SendTo As String, EmailSubject As String, MyAttachment As String) As Boolean
SendEMail = True

Const EMBED_ATTACHMENT As Integer = 1454
Const EMBED_OBJECT As Integer = 1453
Const EMBED_OBJECTLINK As Integer = 1452

Worksheets("Sheet1").Activate

On Error GoTo ErrorMsg
   
    Dim EmailList As Variant
    Dim ws, uidoc, session, db, NotesDoc As Object
    Dim RichTextBody As Object
    Dim server, mailfile, user, usersig As String
    Dim SubjectTxt, MsgTxt As String
           
    Call CreateNotesSession
    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
        .postedDate = Date
    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, "Microsoft Office Excel Worksheet")
            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
   
       
  '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.InsertText("Click on the Worksheet to Edit it")
           Call uidoc.InsertText(Chr(10) + Chr(13))
           Call uidoc.CreateObject("Excel Worksheet", "", MyAttachment)
           Call uidoc.InsertText(Chr(10) + Chr(13))
           Call uidoc.InsertText(Chr(10) + Chr(13))
         End If
     End If
   End If
   
   
   MsgBox "OK sending your document now", vbOKOnly, "Done"
   NotesDoc.send False
    '.Save True, False, True
    '.saveOptions = "0"
    'Call uidoc.Close
   
     
    Set session = Nothing  'close connection to free memory
    Set db = Nothing
    Set uidoc = Nothing
    Set NotesAttach = Nothing
    Set NotesDoc = Nothing
    Set ws = Nothing
   
 Exit Function
 
ErrorMsg:
   
    SendEMail = False
    Msg = "Error # " & Str(Err.Number) & " was generated by " _
            & Err.Source & Chr(13) & Err.Description
    MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
    Set session = Nothing  'close connection to free memory
    Set db = Nothing
    Set NotesAttach = Nothing
    Set NotesDoc = Nothing
    Set ws = Nothing
   Exit Function
End Function

kj8hr14,

Got what you need but only while debugging. When breaks are removed, embedded OLE is just an icon.

Function SendEMail(SheetName As String, EmailBody As String, EmailSubject As String, MyAttachment As String)

Application.DisplayAlerts = False

On Error GoTo ErrorMsg
   
    Dim EmailList As Variant
    Dim ws, uidoc, session, db, uidb, NotesAttach, NotesDoc, objShell As Object
    Dim RichTextBody, RichTextAttachment, ViewAttach As Object
    Dim StyleBold, StyleNorm, StyleUnderline, StyleFont10 As Object
    Dim server, mailfile, user, usersig As String
    Dim SubjectTxt, MsgTxt As String
    Dim data
    Dim group1, group2, group3 As String
           
    'The email groups are contained in a named range in my worksheet. I create an email list
    'from these named ranges
    group1 = Range("EmailList1")
    group2 = Range("EmailList2")
    EmailList = Array(group1, group2)
     
    Set ws = CreateObject("Notes.NotesUIWorkspace")
    Set session = CreateObject("Notes.NotesSession")
   
    user = session.UserName
    usersig = session.CommonUserName
    server = session.GetEnvironmentString("MailServer", True)
    mailfile = session.GetEnvironmentString("MailFile", True)
 
   
    Set db = session.GetDatabase(server, mailfile)
    Set uidb = ws.CURRENTDATABASE
    Set NotesDoc = db.createdocument
    NotesDoc.SaveOptions = 0
    NotesDoc.Encrypt = 0
    NotesDoc.Sign = 0
    NotesDoc.Form = "Memo"
       
    Set RichTextBody = NotesDoc.CreateRichTextItem("Body")
    'Attach spreadsheet
    'If MyAttachment <> "" Then -cezarF
    '    Set RichTextAttachment = NotesDoc.CreateRichTextItem("Attachment") -cezarF
    '    Set NotesAttach = RichTextAttachment.EmbedObject(1454, "", MyAttachment) -cezarF
    'End If -cezarF
   
    NotesDoc.Subject = EmailSubject 'The subject line in the email
   
    NotesDoc.SendTo = user 'I send the email to myself
       
    NotesDoc.CopyTo = EmailList ' Others that I send the email to -cezarF
       
    'Set uidoc = ws.EDITDOCUMENT(True, NotesDoc) -cezarF
 
    ThisWorkbook.Activate
    Sheets(SheetName).Select
    Sheets(SheetName).Range(EmailBody).Select
    Selection.Copy 'Picture Appearance:=xlScreen, Format:=xlPicture
    'Call uidoc.GOTOFIELD("Body") -cezarF
    'Call uidoc.Paste  'CAN I PASTE SPECIAL
   
    Workbooks.Add
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlValues
    Selection.PasteSpecial Paste:=xlPasteFormats
    Columns("A:A").ColumnWidth = 17#
    Columns("B:B").ColumnWidth = 25#
    Columns("C:C").ColumnWidth = 25#
    Columns("D:D").ColumnWidth = 25#
    Columns("G:G").ColumnWidth = 25#
    ActiveWorkbook.SaveAs filename:="\\wlgsrv1\vaxfiles\NZEaros\EmailBody.xls", FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
       
    ActiveWorkbook.Close
   
    Set ViewAttach = RichTextBody.EmbedObject(1453, "", "\\wlgsrv1\vaxfiles\NZEaros\EmailBody.xls")
    'Set ViewAttach = RichTextBody.EmbedObject(1453, "", "c:\EmailBody.xls", "myexcel")
       
    ThisWorkbook.Activate
    'Call uidoc.Save -cezarF
       
    'I commented this out because I get a msg box asking me if I want to save and send,
    'send only, discard or cancel changes and I don't know how to get rid of the msg box
   
    'Call uidoc.Close(True)
       
    'Set NotesDoc = uidoc.DOCUMENT -cezarF
   
    Call NotesDoc.Save(True, True)
   
    Set uidoc = ws.EDITDOCUMENT(True, NotesDoc)
    Dim ViewAttach1 As Object
    Set ViewAttach1 = uidoc.GetObject("myexcel")
    'ViewAttach1.FITTOWINDOW = True
    'ViewAttach1.RUNREADONLY = True
    ViewAttach1.Activate
    'Call uidoc.Save
    Call uidoc.SEND
    Call uidoc.Close
    'This has to be set to False or we get errors
   
    'NotesDoc.SAVEMESSAGEONSEND = False
    'Keep this set to false.
    'NotesDoc.SEND False -cezarF
   
    Set session = Nothing  'close connection to free memory
    Set db = Nothing
    Set NotesAttach = Nothing
    Set NotesDoc = Nothing
    Set uidoc = Nothing
    Set ws = Nothing
   
    MsgBox "Done!"
   
    Exit Function
   
ErrorMsg:   If Err.Number = 7225 Then
            MsgBox "The file " & Range("Fname_NZ_VaR") & " cannot be found in the location " & _
            Range("Path_NZ_VaR"), vbOKOnly, "Error"
    ElseIf Err.Number = 1004 Then
            MsgBox "One of the following may be causing an error:" & vbCrLf & _
            "1. The range 'Path_NZ_VaR' and/or 'Fname_NZ_VaR' does not exist in this spreadsheet," & _
            vbCrLf & "2. The range 'Fname_NZ_VaR' does not contain a filename," & vbCrLf _
            & "3. The path " & Range("Path_NZ_VaR") & " does not exist.", vbOKOnly, "Error"
    Else
            MsgBox Err.Number & Err.Description
    End If
   
End Function

Hi cezarF and marilyng

Really appreciate your efforts with this but still not achieving my objective which was to mimic paste special as RICH TEXT in the body of the email. Your suggestions are still embedding an OBJECT which can't be viewed/opened on mobile phones.

If you copy a range of cells in excel and then do the foll in Notes manually:
Create New Memo
Select Edit
Paste Special
Rich Text
OK

you will find that you get all the formatting from excel but it is still text and can be edited in the new memo.

This is the difference between what you are suggesting and what I get when I do it manually (ie, in Notes select Edit, Paste Special, Rich Text, OK).

I guess this is much harder than I thought. Would appreciate if you can let me know if this is at all achievable.

            

I'm sorry.. if it  COULD be achieved, we would have submitted it.  Maybe you think we're holding out for extra points? :))  We are presenting every workaround that is possible.

>>paste special as RICH TEXT << Solly, this cannot be done in the limitation of this forum, using back end code from Visual Basic.  

You have code that will embed the picture, or the OLE object, and a suggestion  to simply step through the table columns and rows and use Notes Table to build the parallel table in a Notes Rich Text Field.

Here's what I tried:

I tried API
I tried windows scripting
Because you're doing this from excel, you are limited with regard to focus and availability of windows.  In order to manipulate these windows correctly, you would need to construct an outside program in C to grab the right values and push them there.

Unfortunately, cannot be done without a LOT of work, and is why you pay LOTS of money to Midas Rich Text to get a program that does this.  If you must have the "Rich Text" solution, then perhaps you might consider purchasing Midas Rich Text.

I do have a question, because I never had this problem with cell phones since we used a Notes Everyplace server and cell phone forms.. so the server did all the work of presentation to the cell phone.   Even when we had Sprint users, we simply let sprint massage the emails.  How are your cell phone users connecting to your Notes domain?


Hey, leave this open for awhile and see if maybe someone else has another suggestion.  Partha and Heman have been responding to stuff.

kj8hr14,

i'm about to solve it. just one minor prob with calling uidoc.close. it prompts me with a save dialog box which is supposed to be supressed by the SaveOptions field. marilyng is right. Paste Special can't be done programatically but there's always a backdoor.

Good to hear cezarF.

Marilyng I've made some enquiries about how phone users connect to the Notes domain. Will get back to you.

And again, I do appreciate both your efforts to help.

kj8hr14,
btw, what im trying to do is Paste Special, Microsoft Excel Worksheet (using embedded object) as an alternative to the Paste Special Rich Text.

Cezar,  this is a form property on the mail form (as I said in my post)  you can't get rid of it without changing the mail template.  So, as I suggested kj8hr14 has a couple of three choices.

1. Create a second mail memo to use with the property turned off,
2. Don't save the document in the code, but use the API calls to switch the focus to the form so that you know it needs closing, and have the user close it.
--
3. Use a vanilla mail template associated with a "generic" user account so that you can turn off the form property.
--

If it MUST be rich text, your choices are:
Buy Midas Rich Text
Stop fussing with the Edit Paste idea, and knuckle down and script it long way.   I am constantly amazed how much time we spend trying to find a quick workaround because we didn't want to spend the time coding something, and that time spent (let's see, I did about four hours yesterday, Cezar, you must be at a few hours at least, and gosh know how long kj8hr14 spent... combine it all and if it's close to 40 hours, well, you would have had your longer, script solution running.  :))

Send keys might work (albeit inconsistently) but you have to use the API calls to switch focus, so that the right application has the foreground window, else send keys doesn't work.

Marilyn I have given SendKeys a shot as you suggested. One version using functions to grab the window (shown below).

The other version I actually posted as a question to VB experts as I couldn't get it working. See http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/VB_Controls/Q_21834185.html

Got a response from the Notes administrators as follows:

"The Blackberry devices don't actually connect to Notes as such.
When new emails arrive in the clients mail file on the notes server they are picked up by the polling agent on the blackberry server. ( This is wireless connection from the Server to the Blackberry)
If Users make changes to calendar entries or email entries on their Blackberry they are sent to the RIM server in Canada which passes the changes to the domino server via vodafone.
The blackberry server pushes the email to the handheld device via Vodafone and the RIM server based in Canada.
Any attachments in the email are converted to text and only the first part of the document is sent to the device. users can request for the next chunk of data to be sent.as they scroll down through the email.
If Users wish to connect to the domino mail server then they need to use their notes client on their PC not a Blackberry device.

summary.
The devices do not connect to the domino mail servers
email is pushed to the hand held from the blackberry server.
attachments are converted to text and broken down to manageable chunks which are then sent to the device."

Even though he mentions that attachments are converted to text, it doesn't appear to be from what I can see on my manager's phone.

First Version of Sendkeys using functions to grab window:

Option Explicit
Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Public Declare Function SetForegroundWindow Lib "user32" (ByVal hwnd As Long) As Long
Public Declare Function ShowWindow Lib "user32" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Function SendEMail(SheetName As String, EmailBody As String, EmailSubject As String, MyAttachment As String)

Application.DisplayAlerts = False

On Error GoTo ErrorMsg
   
    Dim EmailList As Variant
    Dim ws, uidoc, session, db, uidb, NotesAttach, NotesDoc, objShell As Object
    Dim RichTextBody, RichTextAttachment, ViewAttach As Object
    Dim StyleBold, StyleNorm, StyleUnderline, StyleFont10 As Object
    Dim server, mailfile, user, usersig As String
    Dim SubjectTxt, MsgTxt As String
    Dim data
    Dim group1, group2, group3 As String
    Dim i As Integer
    Dim h As Long
       
    'The email groups are contained in a named range in my worksheet. I create an email list
    'from these named ranges
    group1 = Range("EmailList1")
    group2 = Range("EmailList2")
    EmailList = Array(group1, group2)
     
    Set ws = CreateObject("Notes.NotesUIWorkspace")
    Set session = CreateObject("Notes.NotesSession")
   
    user = session.UserName
    usersig = session.CommonUserName
    server = session.GetEnvironmentString("MailServer", True)
    mailfile = session.GetEnvironmentString("MailFile", True)
   
    Set db = session.GetDatabase(server, mailfile)
    Set uidb = ws.CURRENTDATABASE
    Set NotesDoc = db.createdocument
       
    Set RichTextBody = NotesDoc.CreateRichTextItem("Body")
    'Attach spreadsheet
    If MyAttachment <> "" Then
        Set RichTextAttachment = NotesDoc.CreateRichTextItem("Attachment")
        Set NotesAttach = RichTextAttachment.EmbedObject(1454, "", MyAttachment)
    End If
   
    NotesDoc.Subject = EmailSubject 'The subject line in the email
   
    NotesDoc.SendTo = user 'I send the email to myself
       
    NotesDoc.CopyTo = EmailList ' Others that I send the email to
       
    Set uidoc = ws.EDITDOCUMENT(True, NotesDoc)
 
    ThisWorkbook.Activate
    Sheets(SheetName).Select
    Sheets(SheetName).Range(EmailBody).Select
    Selection.Copy 'Picture Appearance:=xlScreen, Format:=xlPicture
    Call uidoc.GOTOFIELD("Body")
    'Call uidoc.Paste  'CAN I PASTE SPECIAL
   
    Set objShell = CreateObject("WScript.Shell")
       
    h = FindWindow(vbNullString, "Wellington Risk Roundup - May 1 2006 - Lotus Notes")
    If h = 0 Then MsgBox "Cant find window exiting sendkeys":
    ShowWindow h, 1
    h = SetForegroundWindow(h)
    Sleep 100
    SendKeys "%E"
    Sleep 15
    SendKeys "S"
    DoEvents

    For i = 1 To 7
        objShell.SendKeys "{DOWN}"
        DoEvents
    Next
   
    objShell.SendKeys "{ENTER}"
    Sleep 15
    objShell.SendKeys "{DOWN}"
    Sleep 15
    objShell.SendKeys "{ENTER}"
    DoEvents
   
    ThisWorkbook.Activate
    Call uidoc.Save
       
    'I commented this out because I get a msg box asking me if I want to save and send,
    'send only, discard or cancel changes and I don't know how to get rid of the msg box
   
    'Call uidoc.Close(True)
       
    Set NotesDoc = uidoc.DOCUMENT
    Call NotesDoc.Save(True, True)
   
    'This has to be set to False or we get errors
    NotesDoc.SAVEMESSAGEONSEND = False
    'Keep this set to false.
    NotesDoc.SEND False
   
'**************************************************************************************
'This piece has been added to get around the problem of stopping the clear clipboard
'prompt'

    Sheets("Email Message").Select
    Sheets("Email Message").Range("A1").Select
    Selection.Copy
'**************************************************************************************

    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:    If Err.Number = 7225 Then
            MsgBox "The file " & Range("Fname_NZ_VaR") & " cannot be found in the location " & _
            Range("Path_NZ_VaR"), vbOKOnly, "Error"
    ElseIf Err.Number = 1004 Then
            MsgBox "One of the following may be causing an error:" & vbCrLf & _
            "1. The range 'Path_NZ_VaR' and/or 'Fname_NZ_VaR' does not exist in this spreadsheet," & _
            vbCrLf & "2. The range 'Fname_NZ_VaR' does not contain a filename," & vbCrLf _
            & "3. The path " & Range("Path_NZ_VaR") & " does not exist.", vbOKOnly, "Error"
    Else
            MsgBox Err.Number & Err.Description
    End If
   
End Function

cezarF I have tried MANUALLY pasting special as excel worksheet and still can't be viewed on Blackberry. Not sure if it is worth pursuing. Wouldn't want to waste any more of your time.

I wouldn't mind finding out how to suppress the save prompt though. I have no idea how to change that property as marilyng suggested.

SysExpert thanks - will look into it.

To change the save prompt property, open the mail template in designer, go to forms, open the memo, open the memo form property box, click on the HAT and uncheck the On Close: Present Mail Send Dialog.

Now, I don't recommend doing this.. because then you'd have to change everyone's template and it will affect how Notes operates.  I would suggest throwing a separate template and applying it to those mail files that need it.

Hmmm.. if you've manually tried pasting and it can't be viewed on a blackberry, then the blackberry is expecting PLAIN text with no tables,  so before you go further, see what the blackberry can see.. can it see any image, embedded... what does it do with attached files?  Also, wondering if there isn't an excel file viewer for the blackberry, or if the blackberry server serves up the file viewers.  What happens if you create a rich text table in Notes and send it?

>>>Any attachments in the email are converted to text and only the first part of the document is sent to the device. users can request for the next chunk of data to be sent.as they scroll down through the email.<<< well there you are.   Anything that is embedded is turned into an attachment, and they scroll through it.  Now ask how it handles excel attachments.

Send keys is useless, then.   The only thing that would work if you want it to flash on the blackberry is  what I suggested the first time, cycle through and turn the spreadsheet into  some tabbed text report.  Or use the attachment and make sure they have the excel viewer installed.

Ah, yes, I was right, there is an eOffice set of viewers/programs for the blackberry. eOffice Professional Edition, like anything, you have to purchase it.  

Search on Blackberry accessories, or blackberry excel.   So, you're manager is making you jump through hoops to do something that can be achieved if he or she spent a few bucks?

The same is true when you get an excel file in Notes, if you don't have excel installed, then Notes uses an excel viewer to display it.   There is no magic here, just word, excel, powerpoint viewers.

Hi Marilyng

Will find out how excel attachments are handled on the Blackberry. Also just letting you know I can't do anything in Notes Designer (company policy) - I'm just a poor old excel support person. Blackberry software is also a company policy thing.

So you're manager really is making you do cartwheels.. sheesh.  

Being a cynic, I used to work at a place where this group of managers would start at one end of IT and ask for something like this, each one of us would say, "nah, can't be done, or sure, buy this for $$"  Well, they figured if they just went down the list until they found someone who would give them the answer they wanted to hear that's who they would stick with.  They even got straight no's from the IT management, but the IT management didn't figure the managers would just keep asking down the line, so never said anything to the staff.  

My guess would be that he/she asked Notes about this, and was told that only rich text is delivered, and to purchase the vb reader, probably got the same answer from blackberry, so now he/she is down to you.  So, why not check with the Notes Admin first to see what the manager was told to do?  (Just for the heck of it)

Oh, the times when someone did jump up and decide they wanted to impress the "boss" they provided a really non-robust solution that none of us who knew better would even think about doing because it would end up being more support trouble than it was worth.   Usually, I would end up supporting the really crappy solution....:)

SysExpert Hi

Very close to a solution. Have recorded an AutoIt3 script to paste special (rich text) the clipboard contents into the body of the lotus notes memo, save and send the memo.

However, when I run the script from my excel macro, it runs too fast and the pasting doesn't actually occur. I've tried sleep etc but no luck.

Do you have any ideas as to how I can slow the processing so as to enable the clipboard contents to be pasted before the email is sent?

AutoIt3 script below. I run the script from my excel macro as follows:

Shell ("C:\AutoIt3\AutoIt3.exe C:\showWindow3.au3")

AutoIt3 script as follows:

Opt("WinWaitDelay",100)
Opt("WinTitleMatchMode",4)
Opt("WinDetectHiddenText",1)
Opt("MouseCoordMode",0)
WinWait("Wellington Risk Roundup - May 8 2006 - Lotus Notes","")
If Not WinActive("Wellington Risk Roundup - May 8 2006 - Lotus Notes","") Then WinActivate("Wellington Risk Roundup - May 8 2006 - Lotus Notes","")
WinWaitActive("Wellington Risk Roundup - May 8 2006 - Lotus Notes","")
Send("{ALTDOWN}e{ALTUP}")
MouseMove(104,174)
MouseDown("left")
MouseUp("left")
Sleep(1000)
WinWait("Paste Special","")
If Not WinActive("Paste Special","") Then WinActivate("Paste Special","")
WinWaitActive("Paste Special","")
Send("{DOWN}{ENTER}")
Sleep(1000)
WinWait("Wellington Risk Roundup - May 8 2006 - Lotus Notes","")
If Not WinActive("Wellington Risk Roundup - May 8 2006 - Lotus Notes","") Then WinActivate("Wellington Risk Roundup - May 8 2006 - Lotus Notes","")
WinWaitActive("Wellington Risk Roundup - May 8 2006 - Lotus Notes","")
MouseMove(161,135)
MouseDown("left")
MouseUp("left")
Sleep(1000)
WinWait("Lotus Notes","")
If Not WinActive("Lotus Notes","") Then WinActivate("Lotus Notes","")
WinWaitActive("Lotus Notes","")
MouseMove(59,82)
MouseDown("left")
MouseUp("left")
Sleep(1000)
WinWait("Spell Check","")
If Not WinActive("Spell Check","") Then WinActivate("Spell Check","")
WinWaitActive("Spell Check","")
MouseMove(238,248)
MouseDown("left")
MouseUp("left")
Sleep(1000)

Try the system sleep:

Declare Sub Sleepx Lib "kernel32.dll" Alias "Sleep" (Byval milliseconds As Long)

Call sleepx(5000)

Hmmm.. thinking about this, the problem is  if you  sleep system, then you don't pass control, which is really what you have to do.  call up one application, get some stuff, open a second one, and pass control to the second one, then go back to the first one.  Offhand, you can do this in vb.net, but I don't think inside an excel container.

So, did you paste special inside an email and send it off to your boss's phone?  To see if the paste special works?  I would be amazed if it does since most send plain text.

Also, I'm not sysExpert :)  but you're welcome to ask Mike to look this over - offhand, I would ask CRAK or Maddy, more versed in what you are doing.

Sorry marilyng - I'm new to Experts-Exchange. I just assumed that my comments would go to all who responded to my question. How do I get hold of CRAK or Maddy?

Hi

I've got the AutoIt3 script working in excel VBA. So full marks to SysExpert. It was a tough one. My sincere thanks to marilyng and cezarF for all their attempts to assist. Better luck next time.