Using Excel VBA to send formatted range in Lotus Notes bypassing spell check

[Duchesssix]

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:

 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

 

[Adekxl]

Hello Duchesssix,

have you found solution for:
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.

I tried
.AutoSpell="0"

but it does'nt run.

Regards