The following tutorial and code(vba macro) can be used to send a lotus notes mail/memo in just a click of a button in excel sheet or by calling the macro directly(by pressing ALT+F8).
Creating the mail form in Excel Sheet:1. Create a new workbook in excel
2. Goto Sheet1 and rename the sheet as Main
3. Goto Cell C5 and Type "To"
4. Goto Cell C6 and Type "Subject"
5. Goto Cell C7 and Type "Message"
6. From the Autoshapes toolbar(above the excel workbook status bar), select rectangle
7. Draw a small rectangle in the excel sheet just below the message text(this is the send button)
8. Double-Click the rectangle and type the text "Send"
Now that the basic mail send form is complete, we now proceed to insert the send mail code
The Lotus Notes Send mail VBA Code/Macro:1. Press ALT+F11 to open the Visual Basic Editor
2. In the Project explorer Window(on the left hand side of the editor). If you are not able to see the window Press CTRL+R to open the window
3. In the Project explorer Window Right Click the excel sheet name and select Insert>Module
4. Double click the new module created("Module 1" - if this is a new excel sheet)
5. Add the below code in the code window displayed in the right side.
6. Goto the "Main" excel sheet and right click the "Send" button that we created, and select "Assign Macro", and in the dialog box displayed, select the
SendLotusNotesMail function and press OK.
7. Save the excel sheet and enter the "To" address in Cell D5, the subject in D6 and the message in D7 and press "Send".
8. The mail will be sent to the address you provided.
Sub SendLotusNotesMail()
Dim UserName As String
Dim MailDbName As String
Dim Maildb As Object
Dim MailDoc As Object
Dim AttachME As Object
Dim Session1 As Object
Dim EmbedObj1 As Object
Dim recep As Variant
Dim ccRecipient As Variant
Redim recep(15)
Redim ccRecipient(10)
'Open and locate current LOTUS NOTES User
'On Error GoTo testing
Set Session1 = CreateObject("Notes.NotesSession")
UserName = Session1.UserName
MailDbName = "Mail\yourmaildatabase.nsf"
Set Maildb = Session1.GETDATABASE("yourmailserver", MailDbName)
'Example: Set Maildb = Session1.GETDATABASE("maildb/server/company", MailDbName)
If Maildb.IsOpen = True Then
Else
Maildb.OPENMAIL
End If
' Create New Mail and Address Title Handlers
attachment1 = Application.GetOpenFilename(, , "Please select file to send")
Set MailDoc = Maildb.CREATEDOCUMENT
MailDoc.form = "Memo"
recep(0) = ThisWorkbook.Worksheets("Main").Range("D5")
ccRecipient(0)=ThisWorkbook.Worksheets("Main").Range("D5")
subj = ThisWorkbook.Worksheets("Main").Range("D6")
mailbody = ThisWorkbook.Worksheets("Main").Range("D7")
MailDoc.sendto = recep
MailDoc.CopyTo = ccRecipient
MailDoc.Subject = subj
MailDoc.Body = mailbody
' Select Workbook to Attach to E-Mail
MailDoc.SaveMessageOnSend = True
If attachment1 <> "" Then
Set AttachME = MailDoc.CREATERICHTEXTITEM("attachment1")
Set EmbedObj1 = AttachME.embedobject(1454, "attachment1", attachment1, "")
End If
Call MailDoc.Send(False)
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session1 = Nothing
Set EmbedObj1 = Nothing
End Sub
Send Message
Advertise