Macro to extract data from sheet and email this

[howard]

I have tried to write code to extract data from sheet "Man accounts bank" to a blank workbook and then to save this worksheet containing this sheet only to C:/my Documents and then to attach the file to be emailed

 Sub SendFiles()
    Dim lCount As Long
    Dim vFilenames As Variant
    Dim sPath As String
    Dim lFilecount As Long
    Dim sFullName As String
    sPath = "C:\My Documents\"
    ChDrive sPath
    ChDir sPath
   
        Application.DisplayAlerts = False
        Application.CutCopyMode = False
       Sheets(Array("Man Accounts Bank")).Copy
        ActiveWorkbook.SaveAs Replace(vFilenames(lCount), ".xls", "") & ".Man Accounts bank.xls", FileFormat:=xlNormal
        vFilenames(lCount) = ActiveWorkbook.FullName
        Application.ScreenUpdating = False
        For Each sht In Sheets(Array("Man Accounts bank"))
        Sheets(sht.Name).UsedRange.Copy
        Sheets(sht.Name).Range("a1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Next
        Application.ScreenUpdating = True
        ActiveWorkbook.Close True
        Application.DisplayAlerts = True
        Application.CutCopyMode = True
   
      'Mailfiles "", vFilenames
    For lCount = LBound(vFilenames) To UBound(vFilenames)
Kill vFilenames(lCount)
Next
   ActiveWorkbook.Close False
End Sub

When running the macro, type mismatch 13 appears and the following code is highlighted

 ActiveWorkbook.SaveAs Replace(vFilenames(lCount), ".xls", "") & ".Man Accounts bank.xls", FileFormat:=xlNormal

Your assistance in resolving this is most appreciated

 

[howard]

I have also posted this on

Macro to extract sheet and email | Windows Secrets Lounge

See additional code for emailing which I omitted from my earlier post

 Sub Mailfiles(sMailAddress As String, vFiles As Variant)
    Dim oMailItem As Object
    Dim oOLapp As Object
    Dim lCt As Long

    Set oOLapp = CreateObject("Outlook.application")
    Set oMailItem = oOLapp.CreateItem(0)
    With oMailItem
        .To = sMailAddress
        .Subject = "Group Accounts"
       .body = "Attached please find Group accounts as at " & Format(Month(Date) - 1 & " " & Year(Date), "mmm yyyy") & " & vbNewLine & vbNewLine"
       
                  
              .body = .body & "Regards" & vbNewLine & vbNewLine
.body = .body & "Howard"

 For lCt = LBound(vFiles) To UBound(vFiles)
            .attachments.Add CStr(vFiles(lCt))
        Next
        .Display
        Set oOLapp = Nothing
        Set oMailItem = Nothing
    End With
   
      
End Sub