Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
million dollar question!!!!!!!!!!
I have searched for weeks, and have come to the conclusion that it
cannot be done. Emailing a cell range (named range) from excel and pasting it into Lotus Notes body seems to be impossible. I have tried every code in Google groups and spent countless hours looking for a code that works. I have searched the internet, and still nothing. In a desperate cry, does anyone have a proven code that will send a named range / cell range in the body of Lotus Notes? Anyone? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
million dollar question!!!!!!!!!!
Hello
Try this code (found the http://frederic.sigonneau.free.fr/co...otusNotes3.txt) Amend to your needs, (esp. MailDoc.Body and remove the Attachment bit) HTH Cordially Pascal Sub SendNotesMail() ' code mpep, testé par Armel François, mpfe 'Set up the objects required for Automation into lotus notes 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) 'Start a session to notes Set Session = CreateObject("Notes.NotesSession") 'Get the sessions username and then calculate the mail file name 'You may or may not need this as for MailDBname with some systems you 'can pass an empty string UserName = Session.UserName MailDbName = Left$(UserName, 1) & _ Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf" 'Open the mail database in notes Set Maildb = Session.GETDATABASE("", MailDbName) If Maildb.IsOpen = True Then 'Already open for mail Else Maildb.OPENMAIL End If 'Set up the new mail document Set MailDoc = Maildb.CREATEDOCUMENT MailDoc.Form = "Memo" MailDoc.sendto = "moncorrespondant@...." ' à adapter MailDoc.Subject = "Objet ..." ' idem MailDoc.Body = "bla... " & Chr(10) & "bla" & "..." 'idem MailDoc.SAVEMESSAGEONSEND = SaveIt 'Set up the embedded object and attachment and attach it Attachment = "c:\test.jpg" ' à adapter If Attachment < "" Then Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment") Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Attachment, "Attachment") MailDoc.CREATERICHTEXTITEM ("Attachment") End If 'Send the document MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder MailDoc.SEND 0, Recipient 'Clean Up Set Maildb = Nothing Set MailDoc = Nothing Set AttachME = Nothing Set Session = Nothing Set EmbedObj = Nothing End Sub "J.W. Aldridge" a écrit dans le message de news: ... I have searched for weeks, and have come to the conclusion that it cannot be done. Emailing a cell range (named range) from excel and pasting it into Lotus Notes body seems to be impossible. I have tried every code in Google groups and spent countless hours looking for a code that works. I have searched the internet, and still nothing. In a desperate cry, does anyone have a proven code that will send a named range / cell range in the body of Lotus Notes? Anyone? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
million dollar question!!!!!!!!!!
Didnt quite work....
Adjusted it to refer to my cell range, but the following line came up with an error. Set EmbedObj = AttachME.EmbedObject(1454, "", attachment, "Attachment") Sub SendNotesMail() ' code mpep, testé par Armel François, mpfe 'Set up the objects required for Automation into lotus notes 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) 'Start a session to notes Set Session = CreateObject("Notes.NotesSession") 'Get the sessions username and then calculate the mail file name 'You may or may not need this as for MailDBname with some systems you 'can pass an empty string UserName = Session.UserName MailDbName = Left$(UserName, 1) & _ Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf" 'Open the mail database in notes Set Maildb = Session.GETDATABASE("", MailDbName) If Maildb.IsOpen = True Then 'Already open for mail Else Maildb.OPENMAIL End If 'Set up the new mail document Set MailDoc = Maildb.CREATEDOCUMENT MailDoc.Form = "Memo" MailDoc.sendto = "jeremy aldridge" ' à adapter MailDoc.subject = "No subject needed... i know what this is for..." ' idem MailDoc.Body = "bla... " & Chr(10) & "bla" & "..." 'idem MailDoc.SAVEMESSAGEONSEND = SaveIt 'Set up the embedded object and attachment and attach it attachment = "C:\Documents and Settings\460844\Desktop\[auto email.xls]Sheet1!$b$2:$L$22" ' à adapter If attachment < "" Then Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment") Set EmbedObj = AttachME.EmbedObject(1454, "", attachment, "Attachment") MailDoc.CREATERICHTEXTITEM ("Attachment") End If 'Send the document MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder MailDoc.SEND 0, recipient 'Clean Up Set Maildb = Nothing Set MailDoc = Nothing Set AttachME = Nothing Set Session = Nothing Set EmbedObj = Nothing End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
million dollar question!!!!!!!!!!
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
million dollar question!!!!!!!!!!
tried that too....
didnt work. Error message moved to next line, then next line, etc... need the cell range to be copied and pasted into the email. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rounding dollar amounts in half dollar increments | Excel Worksheet Functions | |||
Comparing Row by Row - A-D dollar amounts to E dollar amount | Excel Worksheet Functions | |||
the 100 dollar question | Charts and Charting in Excel | |||
Question For Importing Data Into Excel/Converting General Number to Dollar Amount | Excel Discussion (Misc queries) | |||
Thanks a million | Excel Programming |