calling lotus notes from excel 4

[hopelessliar]

I have been trying to develop a way of including a button in a spreadsheet that sends a notification mail when clicked. My company uses lotus notes which precludes the possiblity of using outlook via COM. I know there is a component for notes (domobj.tlb) but I have had no success in using it. Has anyone managed this?

I tried using OLE automation instead, but notes rejects the call as it is not signed.

Can anyone help me with this?

 

[xlbo]

Hi - Try this:
Sub SendNotesMail()
'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)
Dim myTime As String

recipient = "Hardcode or pick up cell text here"
Subject = "Hardcode or pick up cell text here"

Attachment = "Full Path and name of attachment goes here"
'Start a session to notes
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
msgbox &quot;Mail sent to &quot; & recipient & &quot; re: &quot; & subject
end sub

works for me - can't get it to attach multiple files at once tho - best you can do is set up a loop for the attachments and send multiple emails with 1 file per email
HTH
Geoff

 

[hopelessliar]

Thanks Geoff,
unfortunately, this also is rejected by Notes as it isn't signed. It can be forced thru by clicking 'trust signer' or 'execute once'. How can I sign this, or otherwise get notes to accept this is a valid routine and not a malicious script? I'm sure sys admin would grant the necessary priveleges, if I knew how to go about it.

Sys admin is, of course, clueless.

Thanks again.

Luc

 

[xlbo]

Honestly - I havn't a clue - We run LN at work and this runs fine - no messages, nothing. Just sends the mail to the recipient.
Geoff

 

[kylua]

If notes has been set up so that you can't use it from other programs, an anti virus procedure, then it will ALWAYS ask you to verify an email before it is sent.
This way, any virus is stopped in its tracks. The only way round it is to get the Notes admin to remove this block.
Or, if you have access to an intranet or the web and your emails are external, you can call an asp page from the spreadsheet to send the mail for you.

 

[kylua]

In case it's any help, i have an extensive help file on running notes from VBA or VB

If you mail colin@kylua.com then I'll mail it to you. It may help, altho as I said previous, probably not.

 

[TheEntertainer]

Xlbo,

Excellant post. One problem we don't seem to get a copy of the e-mail in the sent folder

Are we missing something?

Thanks

 

[kylua]

It never does as far as I can see.
I always send the mail to myself as well.
I think it's maildoc.bcc = myemailaddress

 

[acron]

To get a copy in the Sent folder use

MailDoc.SAVEMESSAGEONSEND = True

(I think xlbo may have forgotten to assign a boolean value to SaveIt, which would cause it to default to 0 i.e. False)

At least it works for me.

A.C.

 

[xlbo]

heh heh - I didn't forget anything - I just robbed the code from a google search and adapted a coupla other bits of it to do what I wanted. Never particularly wanted a copy in the sent items so never checked that bit
You get a star for that tho - just in case I need it in the future ;-) Rgds
~Geoff~