Need help adding the file name as the subject in an email sent from Excel

[rkjudy]

I have a workbook that has a macro that creates a PDF of a certain sheet and attaches it to an email and opens Outlook ready for sending. Then, all i have to do is add the email address of the recipient and send it. As it is coded now, the Subject of the email is simply "New Equipment Quote". I would like for the Subject to be "New Equipment Order for" + Filename. For example, if the filename were ABC Company, the Subject would be "New Equipment Order for ABC Company". Here is my current code.

Please Login or Register  to view this content.

Can someone show me how to add the filename to the end of the Subject?

[Tinbendr]

Please Login or Register  to view this content.

[LJMetzger]

Hi rk,

Try the following (your other routines were untouched - you still need them):

Please Login or Register  to view this content.

Lewis

[playaller]

--- Solved ---

[rkjudy]

The first solution provided above did cure the problem, but I guess I left out another key piece of information. I don't want to use a static FileName. I want the FileName to the be the name of the workbook at that time. So I guess I will need to adjust the statement that defines the FileName as static "New Equipment Quote" to where it will make the FileName the current name of the workbook. For example, if the name of the workbook at the time the email is created is "ABC Company", I want the FileName to be "ABC Company", so that the Subject will be "New Equipment Order for ABC Company". Any help?

[Tinbendr]

How about...

Please Login or Register  to view this content.

[rkjudy]

The FileName is changed (through another routine) every time the workbook is used. So, I don't want a fixed FileName. I want the FileName to be the CURRENT name of the workbook. Therefore the Subject will also also be correct. What do you think?

[rkjudy]

When using the code above:

Please Login or Register  to view this content.

I get a Run-time error '438':
Object doesn't support this property or method.

Can someone help me with this issue?

[LJMetzger]

Try replacing 'ActiveSheet.Path' with 'ActiveWorkbook.Path'.

Lewis