MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How do I send email from Excel Macro


Posted by Keith Hoar on October 25, 1999 10:48 AM

I need to send an email from a macro that dynamically compares current product hierarchy to MVS DB/2 tables when a mismatch is encountered.

The email must be sent to a Lotus Notes messaging system.

Thanks for any suggestions.


Posted by Chris on October 25, 1999 11:03 AM

I don't use Lotus Notes, but here's some code I found:

Private Sub CommandButton1_Click()
Dim Subject As String
Dim Recipient As String
Dim Msg As String

Subject = "Test message from VBA"
Recipient = ""
Msg = "This is a message sent to myself from VBA." + _
vbNewLine + vbNewLine + _
"Talk to you later..."

'************************************************************
'here is the notes part
'************************************************************

Dim Session As NOTESSESSION
Dim Database As NOTESDATABASE
Dim Doc As NOTESDOCUMENT

'session and database declared in general as object
Set Session = CreateObject("Notes.NotesSession") 'create notes session
Set Database = Session.GETDATABASE("", "") 'set db to database not yet named
Call Database.OPENMAIL 'Open the users mail database

Set Doc = Database.CREATEDOCUMENT 'create a new document in mail database
Call Doc.replaceitemvalue("SendTo", Recipient) 'create sendto field
Call Doc.replaceitemvalue("Subject", Subject) 'create subject field
Call Doc.replaceitemvalue("Body", Msg) 'create body field
Call Doc.SEND(False) 'send the message - if unsucessful, an error

'this error can be trapped.
'Note: The Session object contains Database and Document. Destroying parent gets rid of others.
Set Session = Nothing ' close connection to free memory

End Sub

Good Luck,
Chris