Mail merge with Lotus Notes and Excel using VBA

[ibizaer]

Hello Everyone -

The following macro was working fine with Excel 2016. I now have Excel 365 but this macro doesn't work. Request your help please.

Option Explicit

Sub Send_Formatted_Range_Data()
   Dim oWorkSpace As Object, oUIDoc As Object
   Dim rnBody As Range
   Dim lnRetVal As Long
   Dim Recepient As String
   Dim subjectName As String
   Dim LotusDB As String
   Dim totalSender, currentSender, i As Integer


    totalSender = Sheets("Launcher").Range("B5").Value
    currentSender = Sheets("Launcher").Range("A5").Value
       
For i = currentSender To totalSender

   Recepient = Sheets("Launcher").Range("A7").Value
   LotusDB = Sheets("Launcher").Range("D7").Value
   subjectName = Sheets("Launcher").Range("C7").Value
  
      
   '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 = False

   'A named range in the activesheet is in use.
  
  
   Sheets("Data").Select
   ActiveSheet.Range("$A$9:$D$17").AutoFilter Field:=1, Criteria1:=Recepient 'Remember to change this
   Set rnBody = ActiveSheet.Range("MailRange1")
   rnBody.Copy

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

   On Error Resume Next
   'Set oUIDoc = oWorkSpace.ComposeDocument("", "C:\Users\********\Mail Merge\mail_merge.nsf", "Memo") 'Update this
  Set oUIDoc = oWorkSpace.ComposeDocument("", "C:\Users\********\Mail Merge\mail_merge.nsf", "Memo") 'Update this
  
Application.Wait (Now() + TimeValue("00:00:01"))
  
   On Error GoTo 0

   Set oUIDoc = oWorkSpace.CurrentDocument

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

 
   'Call oUIDoc.FieldAppendText("EnterCopyTo", stCC)
   Call oUIDoc.FieldAppendText("Subject", subjectName)
   Call oUIDoc.FieldAppendText("EnterSendTo", Recepient)

   '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")
  
Application.SendKeys "{Esc}"

Application.Wait (Now() + TimeValue("00:00:01"))

Application.SendKeys "Y"

Application.Wait (Now() + TimeValue("00:00:01"))

  Application.SendKeys "{ENTER}"

  Application.Wait (Now() + TimeValue("00:00:01"))

Sheets("Launcher").Range("A5").Value = i + 1
  
   Next i

End Sub

thanks,
Rohit