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

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
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:
Code:
'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:
Code:
.Subject = "MOHLTC Revenue Listing Attached"
and I will need to alter this line for the body:
Code:
.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 :biggrin:
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

QuietRiot

Well-known Member
Joined
May 18, 2007
Messages
1,079
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. MacOS
add this line:

Code:
tempfilename2 = Mid(tempfilename, 31, 300)

Code:
.Subject = "MOHLTC Revenue Listing Attached" + tempfilename2

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

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
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 :biggrin: :confused: :p
 

Forum statistics

Threads
1,181,098
Messages
5,928,055
Members
436,586
Latest member
latintxn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top