Lotus Notes Email from VBA Excel Worksheet {Formated Text}

I am not really following the suggestion.

After I copy the selected ceels into the clipboard:

Sheets("Sheet1").Select
Range("A5:G19").Select
Selection.Copy

You think I should past it into ta new workspace (not sure what you mean)

Then embed that file as an OLE Object, why not just embed the original sheet (not sure how to emnbed it) or the clipboard contents? Basically, I am not following the suggestion. Could you clairy more with some code replacements maybe?

OK I will give you a line by line instructions

1. After Selection.Copy inlcude this following lines in your script

Workbooks.Add
ActiveSheet.Paste
ActiveWorkbook.SaveAs Filename:= _
        "C:\temp\Book2.xls", FileFormat:=xlNormal, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False

2. the above code will save your copied selection as Book2.xls, not inlcude that in your embedding mechanism

Instead of this line MailDoc.body = ClipBoard.GetText(1) use this
        Dim rt as Object
        Set rt = MailDoc.CREATERICHTEXTITEM("Attachment")
        Set EmbedObj = rt.EMBEDOBJECT(1453, "Microsoft Excel Worksheet", "c:\temp\book2.xls", "xlObject")

Hope now it is more clear

Humm, well it did not work... When I tried to use the code sinps above, I got an error stating that you canno state the class and the type in together or something similar to that. So we remove the "Microsoft Excel Worksheet" and sent an empty string for that placeholder "". Then it seemed to work fine (from Excel) When I went to view the Lotus Notes Email, a file had been sent and there was an attachment (as indicated by the paper clip) but nothing was visible.

I believe that we have decided that this way is far too much hassle to have "Pretty text" in an email message. I believe that we will be going with the plain old text out put that the

 MailDoc.body = Clipboard .GetText

(In all honesty I prefer the plain text. It is the end users that would like Bold and large RED text formating) While it would be nice, the means to the end are just too twisted.

I will leave this post up, just in case some-one has a simple answer to the problem that we can try in the future. If no one can come up with a better, simpler solution I may split the points down and give you a few for a very valiant effort.

Nah, this is pretty easy.  What you need to do is use the Notes UI through COM, which you thought was impossible.  It is possible, though Notes will prompt for password if the password is not currnetly input by user (e.g., Notes had to start for this to work, or user had cleared password or timeout had cleared password).  But, since you were using Notes.NotesSession, that would be true as well for your existing code.

We are going to use the UI classes to make a memo doc appear in the current screen, move te cursor to the body field, and either:
1) execute a simple paste, which will take the cells in the clipboard, and paste them as a table
2) import an html FILE that we have created (not isnert the text of teh file -- you need to create a file containing the text, and import it)

Basic steps:

Dim ws as Object 'NotesUiWOrkspace
Set ws = CreateObject("Notes.NotesUiWorkspace")
Dim form as Object NotesUiDocument, which will be a form displaying a new memo
Set form = ws.Compose servr, dbFilePath, "Memo" 'opens thememo form, in whatever database is on servr in file dbFilePath
form.gotoField "Body"


form.Paste 'we are in body pasting clipboard contents, which you sould have put in earlier code to copy cells to clipboard
form.Import "html", "my_temp_file.html"


'you can set the other fields using teh code you have, you just need a handle to teh notesDocument that the form uses
Dim MailDoc as Object
Set MailDoc = form.document
MailDoc.SendTo = Recipient
'etc.
'etc.
'etc.
'when done filling out fields, use the form's meothod for saving and sending
form.Save
form.Send

That is exactly what I would like to do, however, when I try the code above, just entering it into the VBA editor, I get the following

Dim ws As Object   [works fine]
Set ws = CreateObject("Notes.NotesUIWorkspace")  [works fine]
Dim form As Object  NotesUiDocument   [error VBA expects end of statement - I comment out the NotesUiDocument which lets the editor continue so we have ]
Dim form As Object
set form = ws.compose server,  [again VBA expects end of statement]

Should I have some add in installed that I am missing? I have checked Tools -> References and Lotus Domino Objects are checked as well as Lotus Notes Automation Classes.

I have continued to think that this should not be that hard [as you say]. Especially when I use the VBA code to do everything but past the data into the body of the text. If you can think of some thing that I am missing I will appreciate the information (or if you can point me to the location that you are getting your info Books, web sites etc... I would really appreciate it.

Yo seem to have corrupted teh text of the code somewhat


CONST server = "someServerName"
CONST dbFilePath = "mail\something.nsf"

Dim ws As Object
Set ws = CreateObject("Notes.NotesUIWorkspace")
Dim form as Object
Set form = ws.Compose server, dbFilePath, "Memo"

etc. (see previous comment's code)

Te info comes mostly from my head (experience).  But you can find most of it in Domino Designer help.  CHeck out the index entry for COM to see how to instantiate the base class for NotesUiWOrkspace and NotesSession. Then, browse the entries for NotesSession and NotesUiWOrkspace, to see their methods and properties.


The usual paths are:
NotesUiWorkspace.currentDocument.document (NotesUiWorkspace.NotesUiDocument.NotesDocument)
NotesSession.CurrentDatabase.getView("viewName").getFirstDocument/.getNextDocument(previousDocument) (NotesSession.NotesDatabase.NotesView.NotesDocument)

alt:
.CurrentDatabase.AllDocuments.get... (NotesDocumentCOllection instead of NotesView)
NotesSession.getDatabase(server,path)

From NotesDocument, you can use .getItemValue and .getFirstItem and .replaceItemValue.  The first returns an array of scalars, the other two return NotesItem objects.

From NotesUiDocument, you do not have to jump into the back-end .document -- you can use teh UI form's properties and mthods, such as .Paste and .Import

Goofy,

Interesting idea, but theer would be a few problems:

1) Does the user have acess to an SMTP server?

2) Does the user wish to create/install additional software to do the send?

3) Is there something else normally expected in the outgoing e-Mail -- a personal message for example?  If this questionis about getting a general-purpsoe solution, then the users will expect to have their cels appear in a new memo that they can edit and address manually

4) Getting the HTML file to appear as the body of themessage is more complicated than just dumping the HTML s the data portion.  You need to, at the very least, indicate content type as HTML

Qwaletee

About your puntualizations (is this said ok?):

1) If you do not have access to an SMTP server, how can you receive a mail? dwanders said this is going to "travel" by mail, so we assume that the recipient has a mail server. You may connect to the recipient server, or the sender server this does not affect the solution

2) The user does not need any "extra" software... telnet comes with the OS... And the VBA Script should be on the Excel spreadsheet.

3) Do not know the answer, I'm interested too in that.

4) I was trying to give the idea, not the whole solution. Of course, you may also add a "Return-receipt" confirmation if you want...

Goofytouy,

To your points:

1) A common setup is Internet network Domino servers -> DOmino DMZ -> Sendmail DMZ -> Internet.  Users would  nothave access to the SMTP server, and would probably be rejected by spam relay rules even if they did have access.

2) What software are you talking about, coming with the OS?  You need a scriptable telnet.

I think Goofytouy had an interesting approach, but I do believe it is getting away from what I originally wanted to do (which is send email in Native Notes transport) - and to go even further I wanted to be able to cut and paste the data - specifically so that it could NOT be edited by the end users.

From what I have seen, any SMTP server that is avaialble on your network (or that you can physically get too (i.e. ping)) will receive properly formated email. (I have even been amazed that you can manually telnet to the port of a SMTP server (even Domino), create a message and mail it by faking the sender and recip (as long as they are vaild users)). This (I believe) is one of the main staples of the Spamming folks out there. It is also the main reason we jocky our ports around with Filtering software so that our Domino server is not receiving generic SMTP email sent by Outlook user on the network.

At the beginning of this My though was to generate HTML, but what I have ended up with (I think) is much better. It is pretty much done, I am just finishing it up and then I will decide how the points are going to get disbursed and past the final code here for anyone to use that might find it useful.  

qwaletee,

About your 1 and 2, let me say that if you have internet access, and spam and relay policies, you may telnet to YOUR domino or exchange server, and providing you know a valid address for THAT server, it will accept your properly formatted mails. if user is unrecognized however, it would kick you back for instance, with a "Relaying mail is denied in server configuration". (Domino 5.01) Note dwanders, that in this case, ONLY ONE of the email addresses must be valid (recipient or sender).

And about the telnet software coming with OS... well I'm sorry...i should have said that only with linux you have an scriptable telnet... but exists a lot of telnets' out there that accept scripting (Rumba for instance).

Ok fellas, here is the code that I am going to use (and what I would consider an accceptable answer the original question I posted):

Steps -->
Create a new Excel Document
Open the Visual Basic for Applications tool bar
 --> View --> Tool bars -->  Check Control and Visual Basic
Place a VB Command Button on Worksheet1 along with the cells you want to Cut and Paste from excel to lotus notes

Paste the following code in the sub for CommandButton1_Click()

Private Sub CommandButton1_Click()
   
    Dim CN, UserName, MailFile As String
   
    Dim workspace As Object
    Set workspace = CreateObject("Notes.NotesUIWorkspace")
   
    'Getting/creating the name of the current users mail DB file
    'Note this will only work on a Single user install of Lotus Notes that has a
    'common name convention e.g. FirstInitalfirst seven of last name

    Dim session As Object
    Set session = CreateObject("Notes.NotesSession")
    CN = session.COMMONUSERNAME
    UserName = LCase(Left(Left$(CN, 1) & Right$(CN, (Len(CN) - InStr(1, CN, " "))), 8)) & ".nsf"
    Set session = Nothing
    MailFile = "mail\" & UserName

    'Enter your Domino Server name in the following ServerName spot

    Call workspace.OPENDATABASE("ServerName", MailFile)
   
    'Set your selection area on the worksheet (this is what will get pasted in your Notes Emial
   
    Sheets("Sheet1").Select
    Range("A5:G18").Select
    Selection.Copy
       
    Dim NotesUIDoc As Object
    Set NotesUIDoc = workspace.COMPOSEDOCUMENT("", "", "Memo")

    'Enter a valid user name in the Valid Username spot
    Call NotesUIDoc.FIELDSETTEXT("EnterSendTo", "Valid Username")
    'Using a Domino Group above makes more sense for multiple users
    Call NotesUIDoc.FIELDSETTEXT("Subject", ">>>>>> This is a TEST Email Message <<<<<")
    NotesUIDoc.GOTOFIELD "Body"
    NotesUIDoc.Paste
    NotesUIDoc.SEND
    NotesUIDoc.Close

    'Clean it up
    Set NoteUIDoc = Nothing
    Set workspace = Nothing
 
    Range("A1").Select
    Application.CutCopyMode = False
           
End Sub

So who among us submitted anything similar to the above?

I would agree that your (qwaletee) code was the closest, with the HemanthaKumar missing the mark (never did get that approach to work, and it caused Notes crash consistantly - even after I was given "Step by Step" which did not work either). Goofytouy had an interesting approach that I did not even try as I was pretty far sold on the Cut & Paste approach.

Even though qwaletee was the closest, the code that was given was incomplete and caused numerous crashes with Notes and Excel. I believe the crashes were caused by the code not following the Domino model for OLE objects. Reguardless, I would really like for this board to me more Flexible with its point giving approach. I would like to award partial points qwaletee for giving partial code and also pointing me to the correct documentation so I could trudge through the code myself (which is what truely lead to the final code - your pointer/reminder to the Documentation that I could not find before - I actually forgot that I had designer installed on my PC, I also found the Domino OLE objects model which was the biggest help of all). I would also give partial credit to Heman... as I did try his code and it sorta worked but did cause many a reboot.  I would give no points to Goofytouy, simply because I did not try that approach and dont know if it worked or not, and finally I would give myself some points as I did most of the codeing after getting the doc's and maps needed to get it worked out.

So if this board was more flexible, I would award the following points:

250 qwaletee & I would keep the other 200 for the ammount of time I had to grut through it myself & 50 to Heman.... Since the system wont let me do that, (I must spend the entire 500 and it seems I cannot award myself points, I will dispurse the points between all three of you who posted to this original message with most to qwaletee, next to Heman... and the lowest to Goofytouy [again - only because I did not try his approach at all])

Before I do that though, I am going to read up on this board and see if I have alturnatives to doing that.

Thanks to all for your suggestions and help with this matter.

dwanders:

      Although points are always welcome, I feel I do not deserve any points because you did not use my solution.
      I appreciate your kindness anyway, but trust me, it is not necessary.
      I'm not here for the points eventhough I could need them, but I'm here to help & learn in the meantime.
      For instance, qwaletee remembered me, that not all OS come with scriptable telnet's =)

Ok please forget all of the above; I managed to fix abov bugs, but now there is a new one which appears much tricker, & for which I would tremendously any feedback that helps solve the problem:

I have the code below, which seems to work fine if Lotus is open, or runs on the same machine that Lotus is installed.

Now, the problem now is that I need to run this code from a remote machine; and when I tried this it didnt work. To be more clear: the Excel Sheet that contains the relevant data that is to be sent out vial the email (The "FixingDataRange" in the code below) can only be accessed through this remote session

 In particular, the code got stuck at the following line:

Set Session = CreateObject("Notes.NotesSession")

Now, the error message I receive is the following:
Run-time error '429'
ActiveX Component can't create object

I figure that there may be two basic ways to approach this:
1) Find a way how the data content that is to be copied to the email (The "FixingDataRange" in the code below) is transferred first from the remote Excel session to an Excel session from my own computer, and then send the data range from there
2) Find a way of how to access Lotus notes through the remote session, so that the code can be run directly from the actual, remotely accessed Excel workbook

Frankly, these are just speculative solutions as I am an entire novice to these kind of issues; however, if anyone has an idea of how to solve this issue & implement the solution in VBA, this will be truly tremendously appreciated

Thanks a lot in advance!
Burki
 

Sub SendFixingMail()

    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)
    Dim Subject As String       'The subject string
    Dim Attachment As String    'The path to the attachemnt string
    Dim Recipient As String     'The Recipient string (or you could use the list)
    Dim Recip(10) As Variant    'The Recipient list
    Dim BodyText As String      'The body text
    Dim SaveIt As Boolean       'Save to sent mail
    Dim WasOpen As Integer      'Checking to see if the Mail DB was already
                                'open to determine if session should be
                                'closed (0) or left alone (1)
    Dim MyData As DataObject    'Data object for getting text from clipboard
   
    Subject = "This is a Test Email Messag"
    Recipient = "burk78@gm.com"
   
   
   
    FixingRangeStart = Range("FixingDataRangeStart").Address
    RangeEndCol = Range("FixingDataRangeStart").End(xlToRight).Column
    RangeEndRow = Range("FixingDataRangeStart").End(xlDown).Row
    FixingRangeEnd = Cells(RangeEndRow, RangeEndCol).Address
    Set FixingDataRange = Range(FixingRangeStart, FixingRangeEnd)
    FixingDataRange.Select
    Selection.Copy
    Set ClipBoard = New DataObject
    ClipBoard.GetFromClipboard
    SaveIt = True
    Set Session = CreateObject("Notes.NotesSession")
    UserName = Session.UserName
    MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
    Set Maildb = Session.GETDATABASE("", MailDbName)
    If Maildb.IsOpen = True Then
         WasOpen = 1      'Already open for mail
     Else
         WasOpen = 0
         Maildb.OPENMAIL    'This will prompt you for password
     End If
    Set MailDoc = Maildb.CREATEDOCUMENT
    MailDoc.Form = "Memo"
    MailDoc.sendto = Recipient      'Or use Racip(10) for multiple
    MailDoc.Subject = Subject
    MailDoc.body = ClipBoard.GetText(1)
    MailDoc.SAVEMESSAGEONSEND = SaveIt
    If Attachment <> "" Then
        Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
        Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Attachment, "Attachment")
        MailDoc.CREATERICHTEXTITEM ("Attachment")
    End If
    MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
    MailDoc.Send 0, Recipient
    'Clean Up'
    Range("A1").Select
    Application.CutCopyMode = False
    Set Maildb = Nothing
    Set MailDoc = Nothing
    Set AttachME = Nothing
    Set EmbedObj = Nothing
    If WasOpen = 1 Then
        Set Session = Nothing
    ElseIf WasOpen = 0 Then
        Session.Close
        Set Session = Nothing
    End If
   
    MsgBox "The Line Down Email was sent", vbOKOnly
       
End Sub