Export Excel sheets and send them as emails (Loop)

Dear all,

I'm trying to automate a report production and distribution process. What I need to do is create a macro that goes through the list (1 to 20) and for each row creates a new file (separate Excel file), pastes 2 tabs from the current one (sheet names are in columns J and K) and saves it under the filename in column E. Then the macro should open Outlook and create an email by attaching the file and using the subject, distribution list, email body as shown in the table.

I'd highly appreciate any help as I've been bumping my head with this for a while.
excel111.PNG

Thanks,
Alex

Thank you alansidman for your suggestion. I've attached an Excel file. Below is the code that I have so far (not much...). I haven't reached the loop and send emails part yet.

Sub Create_Files()

Dim MyDateStr As String
Dim wkbCurrent As Workbook
Dim wkbtemp As Workbook
Dim wkbtemp2 As Workbook
Dim MyLocation As String
Dim SheetName As String

Set wkbCurrent = ActiveWorkbook
MyDateStr = wkbCurrent.Sheets("Control").Range("F12")

ChDir "W:\Finance Analysis\Contsys2\DATA\aacorpfinance\_yr 2017\Expense reports\"
      If Len(Dir(MyDateStr, vbDirectory)) = 0 Then
        MkDir (MyDateStr)
    End If
    
    MyLocation = "W:\Finance Analysis\Contsys2\DATA\aacorpfinance\_yr 2017\Expense reports\"
    
SheetName = wkbCurrent.Sheets("Emails").Range("C2")

Set wkbtemp = Workbooks.Add

wkbCurrent.Sheets(SheetName).Cells.Select


With wkbtemp
wkbtemp.Sheets("Sheet1").Range("A1").PasteSpecial



wkbCurrent.Sheets("M & A").Copy
wkbtemp2.Sheets("Sheet2").Range("A1").Select
ActiveSheet.PasteSpecial Paste:=xlValues

Set wkbtemp = Sheets.Add(After:=Sheets(Sheets.Count))
End With
End Sub

Anyone has an idea?