dwanders
asked on
Lotus Notes Email from VBA Excel Worksheet {Formated Text}
I have been trying to get a MS Excel spread sheet to email specific content of an Excel sheet to an email message. I have found the code that allows for me to generate an email message and that work swimmingly. Especially if I wanted to attach the Spread sheet (works great as well). What I would really like to do (that I have determined cannot be done because the NotesUI does not support COM) is esentially:
VBA for Excel -> Select Cells -> Paste into a Notes Document (maintaining the Cell Formatting).
The code that I have (see end of question) that works will only copy the text to the email document (I have removed all comments for the code, I originally got this code from the internet and modified it slightly to give credit to the owner whoever they may be).
I have given up on the Copy and Paste approach (which would have worked great) and have decided to try and use HTML formatting to get as close to the results that I would like. I am thinking some thing like:
Dim BodyText As String -- > Then --> BodyText = "MY HTML Code" --> then dump that to the Body of the email message. Which does not work, the text is exactly as I send it, but the client does not execute the HTML - it displays it. I have been trying to figure out Lotus Notes PassThru HTML but cannot get it to work either.
Any suggestions or pointers will be greatly appreciated.
##### Begin Excel VBA Code to pass email to Notes #####
Private Sub CommandButton1_Click()
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 ClipBoard As DataObject 'Data object for getting text from clipboard
Subject = "This is a Test Email Messag"
Recipient = "Notes Email User"pying it to Clipboard
Sheets("Sheet1").Select
Range("A5:G19").Select
Selection.Copy
Set ClipBoard = New DataObject
ClipBoard.GetFromClipboard
SaveIt = True
Set Session = CreateObject("Notes.NotesS
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
Set EmbedObj = AttachME.EMBEDOBJECT(1454,
MailDoc.CREATERICHTEXTITEM
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
Thanks again in advance
VBA for Excel -> Select Cells -> Paste into a Notes Document (maintaining the Cell Formatting).
The code that I have (see end of question) that works will only copy the text to the email document (I have removed all comments for the code, I originally got this code from the internet and modified it slightly to give credit to the owner whoever they may be).
I have given up on the Copy and Paste approach (which would have worked great) and have decided to try and use HTML formatting to get as close to the results that I would like. I am thinking some thing like:
Dim BodyText As String -- > Then --> BodyText = "MY HTML Code" --> then dump that to the Body of the email message. Which does not work, the text is exactly as I send it, but the client does not execute the HTML - it displays it. I have been trying to figure out Lotus Notes PassThru HTML but cannot get it to work either.
Any suggestions or pointers will be greatly appreciated.
##### Begin Excel VBA Code to pass email to Notes #####
Private Sub CommandButton1_Click()
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 ClipBoard As DataObject 'Data object for getting text from clipboard
Subject = "This is a Test Email Messag"
Recipient = "Notes Email User"pying it to Clipboard
Sheets("Sheet1").Select
Range("A5:G19").Select
Selection.Copy
Set ClipBoard = New DataObject
ClipBoard.GetFromClipboard
SaveIt = True
Set Session = CreateObject("Notes.NotesS
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
Set EmbedObj = AttachME.EMBEDOBJECT(1454,
MailDoc.CREATERICHTEXTITEM
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
Thanks again in advance
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Set EmbedObj = rt.EMBEDOBJECT(1453, "Microsoft Excel Worksheet", "c:\temp\book2.xls", "xlObject")
Hope now it is more clear
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
Set EmbedObj = rt.EMBEDOBJECT(1453, "Microsoft Excel Worksheet", "c:\temp\book2.xls", "xlObject")
Hope now it is more clear
ASKER
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.
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.NotesU
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
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.NotesU
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
ASKER
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.NotesU
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.
Dim ws As Object [works fine]
Set ws = CreateObject("Notes.NotesU
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.NotesU
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.currentDo
NotesSession.CurrentDataba
alt:
.CurrentDatabase.AllDocume
NotesSession.getDatabase(s
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
CONST server = "someServerName"
CONST dbFilePath = "mail\something.nsf"
Dim ws As Object
Set ws = CreateObject("Notes.NotesU
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.currentDo
NotesSession.CurrentDataba
alt:
.CurrentDatabase.AllDocume
NotesSession.getDatabase(s
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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...
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.
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.
ASKER
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.
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).
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).
ASKER
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.NotesU
'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.NotesS
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("Se
'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(
'Enter a valid user name in the Valid Username spot
Call NotesUIDoc.FIELDSETTEXT("E
'Using a Domino Group above makes more sense for multiple users
Call NotesUIDoc.FIELDSETTEXT("S
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?
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.NotesU
'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.NotesS
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("Se
'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(
'Enter a valid user name in the Valid Username spot
Call NotesUIDoc.FIELDSETTEXT("E
'Using a Domino Group above makes more sense for multiple users
Call NotesUIDoc.FIELDSETTEXT("S
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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 =)
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.NotesS
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("FixingDataRangeStar
RangeEndCol = Range("FixingDataRangeStar
RangeEndRow = Range("FixingDataRangeStar
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.NotesS
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
Set EmbedObj = AttachME.EMBEDOBJECT(1454,
MailDoc.CREATERICHTEXTITEM
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
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.NotesS
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("FixingDataRangeStar
RangeEndCol = Range("FixingDataRangeStar
RangeEndRow = Range("FixingDataRangeStar
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.NotesS
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
Set EmbedObj = AttachME.EMBEDOBJECT(1454,
MailDoc.CREATERICHTEXTITEM
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
Ooops - posted in the wrong forum, pls delete/ignore above - apologies
ASKER
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?