macro to send different sheets to different recipients as per list

Hi Experts

in sheet 1 there is a list of recipients and the name of the sheets. Other sheet contain data to be send to recipients.

My requirement is a macro that will send each sheets to the recipients as per the list in Sheet 1
Attached a workbook for ref.

Replace all you code with the following...tested and works. I think the problem was that you were not indicating a path for the file you were saving therefore the code was trying to attach a non-existent file. I have also changed your access to Outlook from early binding, which requires the MS Outlook Library to be installed to late binding which does not and is more inter operable across different versions.

Option Explicit

Sub Send_XLSheets_Word_Outlook()
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim rnRecipients As Range, rnWorkSheets As Range, rnCell As Range
Dim stName As String, fPath_Name As String
Dim i As Long

Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets("Sheet1")

With wsSheet
'Here we have created a list of recipients.
Set rnRecipients = .Range("rnRecipients")
'Here we have created a list of singel worksheets in the active workbook.
Set rnWorkSheets = .Range("rnWorksheets")
End With

Application.ScreenUpdating = False

For i = 1 To rnRecipients.Count
    'Here we copy, create a new workbook and
    stName = rnWorkSheets(i, 1).Value
    fPath_Name = ThisWorkbook.Path & "\" & stName & ".xls"
    wbBook.Worksheets(stName).Copy
    With ActiveWorkbook
        'in your original script you were missing "ThisWorkbook.Path & "\" & ", which is probably why the function was not working
        .SaveAs Filename:=fPath_Name, FileFormat:=51
        .Close
    End With
    Email_Sheet rnRecipients(i, 1).Value, "Subject: Reports", "As per agreed", ThisWorkbook.Path & "\" & "Report.doc", ThisWorkbook.Path & "\" & stName & ".xls", True
    
    'Delete newly created workbook
    Kill fPath_Name
Next i

Application.ScreenUpdating = True

End Sub

Function Email_Sheet(StrTo As String, _
                              StrSubject As String, StrBody As String, Attachment1 As String, Attachment2 As String, Send As Boolean)
    'Uses late binding, which does not require the library to be installed.  your previous method is early binding which requires the Ms Outlook x.x library
    'I've found late binding to be more usefull in mixed version environments
    Dim OutApp As Object
    Dim OutMail As Object
    Dim signature As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    Application.ScreenUpdating = False
    With OutMail
        .Display
    End With
        signature = OutMail.HTMLBody
    Application.ScreenUpdating = True
    
    On Error GoTo ExitFunc
    With OutMail
        .To = StrTo
        .CC = ""
        .BCC = ""
        .Subject = StrSubject
        .HTMLBody = StrBody & "<br>" & signature
        With .Attachments
            'Here we add the word-memo.
            .Add Attachment1
            .Item(1).DisplayName = "Summery - Report"
            'Here we add a worksheets.
            .Add Attachment2
            .Item(2).DisplayName = "Details - Report"
        End With
        If Send = True Then
            .Send
        Else
            .Display
        End If
    End With

ExitFunc:
    Set OutMail = Nothing
    Set OutApp = Nothing
End Function

The above code is not attaching the file, not even separating sheets and creating new file, it only creates a new mail with the recipient name

The following code is working fine for me. The only problem with the code is that, I have to place recipient in each sheet.
Also with this code i donot have to install any outlook libery and easy to use
It picks up the recipient name from A1 of each sheet and send the sheet to the recipient, but because there are many sheets and different recipients each time, it is also time consuming.
I need the code to pick the recipient name from the list given in the first sheet, remane the file as sheet name and then send to the recipient.

Also if possible it marks as send in the list in the first sheet

HTML Code: 

I did test my code it it worked as expected (create copy of sheet, create email, attach doc file attach spreadsheet and send) delete file...perhaps the difference is in version I am on 2013.

Yes, might be b'coz I might using 2007. Any way thank you, any other solutions that can be run in any version.

Any help please