How do I Create and modify Notes E-mail using VBA in Excel 1

[HandleUK]

I have a spreadsheet which uses VBA to generate an e-mail within the users Lotus Notes account. This is all done via the back door using the code below:

HOWEVER, what I'd like to do is paste data directly into the body of the newly created document. I suspect this may be achievable if I use the UIDOC route but no matter what I try I can't get this to work. Does anyone out there have a magic wand!!!

Many Thanks...


Set session = CreateObject("Notes.NotesSession&quot
Dim db As Object
Dim o As Object
Dim rtitem As Object
Dim ServerName As String
Dim MailFileName As String
Dim MailFileFound As Boolean

Call GetServerNameAndMailFile(ServerName, MailFileName, MailFileFound)

If Not MailFileFound Then
Exit Sub
End If

' On Error GoTo errormsg:
Set db = session.GetDatabase(ServerName, MailFileName)

'Open your mail file if not already opened
If Not db.IsOpen Then
Call db.openmail
End If


Set doc = db.CreateDocument
doc.Subject = PointsReport.Caption


doc.body = ""
DistList = "&"
doc.SENDTO = ""
doc.COPYTO = ""
doc.sign

Call doc.Save(False, False)

 

[xlbo]

Well, I don't know too much about email automation but the following works fine for me (xl97 SR2, NT)
Watch for word wrap

Sub SendNotesMail()
'Set up the objects required for Automation into lotus notes
Dim subject As String, attachment As String, recipient As String, bodyText As String, Saveit As Boolean
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
myText = "This will go in the body of the email"
subject = mySub
attachment = ""
recipient = myRecip
bodyText = myText
Saveit = True

Set Session = CreateObject("Notes.NotesSession&quot
'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, " &quot)) & ".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 = recipient
MailDoc.subject = subject
MailDoc.Body = bodyText
MailDoc.SAVEMESSAGEONSEND = Saveit
'Set up the embedded object and attachment and attach it
'If attachment <> &quot;&quot; Then
'      Set AttachME = MailDoc.CREATERICHTEXTITEM(&quot;Attachment&quot
'      Set EmbedObj = AttachME.EMBEDOBJECT(1454, &quot;&quot;, attachment, &quot;Attachment&quot
'        MailDoc.CREATERICHTEXTITEM(&quot;Attachment&quot
'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
Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[HandleUK]

Thanks for this Geoff, I'll give it a go. I'm guessing the attachment is added as a path?

PS liked the quote!

 

[xlbo]

I believe so yes - for safety, don't use mappings - make sure that it is the full network path from the root server Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde