VBA Workbook name and steppinjg throug the code

[ziad alsayed]

dear all

the below code is working but i have 2 issues here

1- in am using WBN.name and it is giving the file name with .xls , i need to remove the .xls ( check in red)
2- if i step through the code after i pass the line " Set WBN = Workbooks.Open(Filename:=thisfile)
" the code will run till the end without allowing me to step line by line, how is that happening?

Sub try()
Dim omail As Object
Dim oapp As Object
Dim Msg As String
Dim WSl As Worksheet
Dim WBN As Workbook
Dim SigString As String
Dim Signature As String
Set WSl = Worksheets.Add(After:=Worksheets("By Sector"))
' list Files from folder, this will get the path of all files from the folder
Call FindFiles2007(Dummy)
' find lastrow
lastrow = WSl.Cells(Rows.Count, 1).End(xlUp).Row
' Loop through all  the files on wsl
For j = 1 To lastrow
    thisfile = WSl.Cells(j, 1)
    Set WBN = Workbooks.Open(Filename:=thisfile)
   
        Set oapp = CreateObject("outlook.application")
        Set omail = oapp.createitem(0)
' add the email Signature
    SigString = "c:\Users\Ziad Alsayed\AppData\Roaming\Microsoft\Signatures\Ziad.htm"
                If Dir(SigString) <> "" Then
                    Signature = GetBoiler(SigString)
                Else
                    Signature = ""
                End If
' start preparing to sned the email
        With omail
            .To = "[EMAIL="imad.elhage@jubailibros.com"]imad.elhage@jubailibros.com[/EMAIL]"
            .cc = "[EMAIL="karim.jubaili@jubailibros.com"]karim.jubaili@jubailibros.com[/EMAIL]"
            .Subject = [COLOR=red]WBN.Name[/COLOR] & " " & "Customer Without Profile" & " " & Format$(Date, "dd ,mmmm,yyyy")
            .attachments.Add WBN.FullName
            Msg = "Dear" & " " & "Imad" & "<br><br>"
            Msg = Msg & "Please find attached file, "
            Msg = Msg & " attached are customer With No Profile, please Contact " & [COLOR=red]WBN.Name[/COLOR] & " to add the profile."
            Msg = Msg & "<br><br>"
            .htmlbody = Msg & "<br><br>" & Signature
            .send
        End With
   ' Close Workbook
   WBN.Close savechanges:=False
 Next j
        
    Set omail = Nothing
    Set oapp = Nothing
    WSl.Delete
End Sub
Function GetBoiler(ByVal sFile As String) As String
'**** Kusleika
    Dim fso As Object
    Dim ts As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.getfile(sFile).OpenAstextstream(1, -2)
    GetBoiler = ts.readall
    ts.Close
End Function

Appreciate any help

 

[VoG]

Try

.Subject = Left(wbn.Name, InStr(wbn.Name, ".") - 1) & " " & "Customer Without Profile" & " " & Format$(Date, "dd ,mmmm,yyyy")

 

[ziad alsayed]

thanks VoG, this is perfectly working. appreciate your Help.