Hi All,

I have written a excel macro to send email through lotus notes with an excel sheet range copied in the body of the email.

The mail is sent successfully after composed, but the problem am facing is when i tried to close the composed email, a popup window is displayed asking

"Do you want to save the message" with YES, NO and CANCEL buttons.

Below is the code which i used to sent email through lotus notes

Sub Send_Formatted_Range_Data()
Dim sPassword As String

Dim oWorkSpace As Object, oUIDoc As Object
Dim rnBody As Range
Dim lnRetVal As Long

Const stTo As String = "Nagamaheswar"
'Const stTo1 As String = ""
'Const stTo As String = "sumitmpatel@trer.co.in"
' Const stCC As String = "ramulugantela@hre.com"
Const stBody As String = vbCrLf & "As per Report the status report is created ."
& vbCrLf _
& "Kind regards" & vbCrLf & "Mahesh"
Const stSubject As String = "Status:SIT ENV check for 23rd Nov :XFOS::GREEN
(Automation Test EMAIL for Status Report)"
Const stMsg As String = "An e-mail has been succesfully created and saved."

'Check if Lotus Notes is open or not.
'lnRetVal = FindWindow("NOTES", vbNullString)

'If lnRetVal = 0 Then
' MsgBox "Please make sure that Lotus Notes is open!", vbExclamation
' Exit Sub
'End If

Application.ScreenUpdating = True

'A named range in the activesheet is in use.
Set rnBody = ActiveSheet.Range("B165")

'sPassword = GetLNPassword
Set Session = CreateObject("Lotus.NotesSession")

Session.Initialize ("@lt12345")
UserName = Session.UserName
MailDbName = _
Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " ")))
& ".nsf"
Set Maildb = Session.GETDATABASE("", MailDbName)

'Instantiate the Lotus Notes COM's objects.
Set oWorkSpace = CreateObject("Notes.NotesUIWorkspace")

On Error Resume Next
Set oUIDoc = oWorkSpace.ComposeDocument("", "archive\a_ap3518.nsf", "Memo")
On Error GoTo 0

Set oUIDoc = oWorkSpace.CurrentDocument

'Using LotusScript to create the e-mail.
Call oUIDoc.FieldSetText("EnterSendTo", stTo)
'Call oUIDoc.FieldSetText("EnterCopyTo", stCC)
Call oUIDoc.FieldSetText("Subject", stSubject)

'The can be used if You want to add a message into the created document.
'Call oUIDoc.FieldAppendText("Body", vbNewLine & stBody)

'Here the selected range is pasted into the body of the outgoing e-mail.
Call oUIDoc.GoToField("Body")
Call oUIDoc.Paste

'Save the created document.
Call oUIDoc.Save
'If the e-mail also should be sent then add the following line.
Call oUIDoc.Send(False)

Call oUIDoc.Close(False)
'Code to quit Excel
'Application.SendKeys ("%fx")

'Release objects from memory.
Set oWorkSpace = Nothing
Set oUIDoc = Nothing

With Application

.CutCopyMode = False
.ScreenUpdating = True
End With

' MsgBox stMsg, vbInformation

'Activate Lotus Notes.
AppActivate ("Notes")

End Sub