Duchesssix
New Member
- Joined
- Jan 6, 2011
- Messages
- 42
I am using Lotus Notes to send formatted data from a range or chart as part of an e-mail's body with excellent code from Dennis to email a formatted range from within Excel using Lotus Notes. However, I have two problems I have not been able to bypass and am looking for help on.
1. Notes has spellcheck active and the user is prompted with the spell check box for each email sent. I want the email to autosend and not show the spellcheck popup
2. Notes sends the email but a copy of the email remains open and the user has to manually close the window/email. Since the file will send multiple emails to multiple people I just want the emails to send and save a copy in the sent folder automatically, without having to manually save the email or close the email window.
The code I am using is as follows:
1. Notes has spellcheck active and the user is prompted with the spell check box for each email sent. I want the email to autosend and not show the spellcheck popup
2. Notes sends the email but a copy of the email remains open and the user has to manually close the window/email. Since the file will send multiple emails to multiple people I just want the emails to send and save a copy in the sent folder automatically, without having to manually save the email or close the email window.
The code I am using is as follows:
Code:
Sub Send_Formatted_Range_Data()
Dim oWorkSpace As Object, oUIDoc As Object
Dim rnBody As Range
Dim lnRetVal As Long
Dim stSubject As String
Const stTo As String = "email@company.com"
Const stBody As String = vbCrLf & "Here are your weekly Contest results." & vbCrLf & vbCrLf _
& "Kind regards," & vbCrLf & "Me"
Const stMsg As String = "An e-mail has been succesfully created and saved."
stSubject = "eFenwal Contest as of " & Date
'Check if Lotus Notes is open or not.
lnRetVal = FindWindow("SWT_Window0", vbNullString)
If lnRetVal = 0 Then
MsgBox "Please make sure that Lotus Notes is open!", vbExclamation
Exit Sub
End If
Application.ScreenUpdating = False
'A named range in the activesheet is in use.
Set rnBody = ActiveSheet.Range("Brianna")
rnBody.Copy
'Instantiate the Lotus Notes COM's objects.
Set oWorkSpace = CreateObject("Notes.NotesUIWorkspace")
On Error Resume Next
Set oUIDoc = oWorkSpace.ComposeDocument("", "mail\7\USSC2647.nsf", "Memo")
On Error GoTo 0
'Set oUIDoc = oWorkSpace.CurrentDocument
Call oUIDoc.GotoField("Subject")
'Using LotusScript to create the e-mail.
Call oUIDoc.FieldSetText("EnterSendTo", stTo)
Call oUIDoc.FieldSetText("Subject", stSubject)
'Here the selected range is pasted into the body of the outgoing e-mail.
Call oUIDoc.GotoField("Body")
Call oUIDoc.Paste
'The can be used if You want to add a message into the created document.
Call oUIDoc.FieldAppendText("Body", vbNewLine & stBody)
'Save the created document.
Call oUIDoc.Save(True, False, False)
'If the e-mail also should be sent then add the following line.
Call oUIDoc.Send(True)
'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