Chang CODE TO ADD File Name to Title and Body of E-Mail???

[Mister H]

Hi All:

Edit: SORRY I just noticed I had the naming incorrect which may very well explain why nobody had an answer? THANKS again ALL.


ONCE AGAIN I am seeking advice from the Excel Experts around the World...

The portion of code below has been worked on and revised by some Board Members (THANKS to you ALL). I am now hoping to go one step further with this. I would like the name of the attached file to appear in BOTH the E-Mail Message Title as well as the Body of the e-mail. The code I am using is this:

'Save the new workbook AND Mail it

    FilePath = "E:\FSDB\RevMgmt\Documentation\"
    
    TempFileName = FilePath & Format(ActiveSheet.Range("C1"), "yyyy mm dd") & " " & "MOHLTCK cheque log REVENUE"
    
    NewName = Application.GetSaveAsFilename(Initialfilename:=TempFileName, _
        fileFilter:="Excel Files (*.xls), *.xls")
        
        
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
  
    With Destwb
        .SaveAs FileName:=NewName
        On Error Resume Next

        With OutMail
            .To = ""
            .CC = ""
            .BCC = ""
            .Subject = "MOHLTC Revenue Listing Attached:  "
            .Body = "Hi:  Please see attached the following MOHLTCK file for Uploading to your System:"
            .Attachments.Add Destwb.FullName

I have limited VBA knowledge but my thinking is I need to alter this line for the E-Mail Title:

.Subject = "MOHLTC Revenue Listing Attached"

and I will need to alter this line for the body:

.Body = "Hi:  Please see attached the following MOHLTCK file for Uploading to your System:"

I have tried adding & Filename to these lines but I am getting the WHOLE PATH in there as well. For example if the file created is:

2007 07 24 MOHLTCK cheque log REVENUE

then I would like the e-mail title to be:
MOHLTC Revenue Listing Attached: 2007 07 24 MOHLTCK cheque log REVENUE

I would then like the body of the e-mail to be: Hi: Please see attached the following MOHLTCK file for Uploading to your System: 2007 07 24 MOHLTCK cheque log REVENUE

Currently I can ONLY get the title to read:

MOHLTC Revenue Listing Attached: E:\FSDB\RevMgmt\Documentation\2007 07 24 MOHLTCK cheque log REVENUE

Does ANYONE have any thoughts on how I can accomplish the above?

As ALWAYS THANKS for your assistance.
Mark

 

[QuietRiot]

add this line:

tempfilename2 = Mid(tempfilename, 31, 300)

.Subject = "MOHLTC Revenue Listing Attached" + tempfilename2

.Body = "Hi:  Please see attached the following MOHLTCK file for Uploading to your System:" + tempfilename2

 

[Mister H]

THANKS...

THANKS for your help QuietRiot. I will try out your fix in a few minutes. i just wanted to say THANKS.

Bye 4 Now,
Mark