why is the file name duplicating in subject field?

[ray707]

Hi there,

I need some help with a VBA code and was wondering if anyone could help.

I needed a VBA code to email a single excel sheet from a workbook and found the code below. The problem is that when the file gets emailed to the recipient, the file name says 'TestFileScottNew.xlsm.xlsm'. In other words, the 'xlsm' part gets duplicated in the attachment and I want to prevent this from happening. I was wondering if anyone could help and let me know how to remove one of the xlsm's in the file name? (if you have a better code to help me achieve my aim then feel free to suggest it!)

Here is the code:

Please Login or Register  to view this content.

Any help appreciated!

[dangelor]

Try...

Please Login or Register  to view this content.

... and add code tags to your post.

[AC PORTA VIA]

change this line

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

[ray707]

Try...

Please Login or Register  to view this content.

... and add code tags to your post.

change this line

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

That worked! Thank you!

The only thing is the file name now has 'temp' at the beginning, so it says 'TempTestFileScottNew.xlsm'- this is less of a problem but any ideas why and how to fix it?!

also how can I put this into a command button so that the user can press a button and it pings out the email? I tried putting the above into the VBA for a command button but it didn't work:

Please Login or Register  to view this content.

The error says:

Compile error: expected end sub

[AC PORTA VIA]

remove below line

Please Login or Register  to view this content.

[dangelor]

The only thing is the file name now has 'temp' at the beginning, so it says 'TempTestFileScottNew.xlsm'- this is less of a problem but any ideas why and how to fix it?!

Try this...

Please Login or Register  to view this content.

[ray707]

Try this...

Please Login or Register  to view this content.

This didn't work for removing 'temp'; it would send an email without an attachment and before that it would ask me to save the file which I want to avoid...

[ray707]

remove below line

Please Login or Register  to view this content.

This worked for the command button, thank you. It does generate a pop up notification upon activation though which says 'A file named C:\Users\xxxx\AppData\Local\TempBook1.xlsm already exists in this location. Do you want to replace it?' but I think that is okay, right? My understanding is that the way these codes work is that they create a temp file in a location and then deletes it, so this is simply doing that..? Therefore I don't think we can avoid this can we..?

[dangelor]

Try this version...

Please Login or Register  to view this content.

[ray707]

try this version...

Please Login or Register  to view this content.

thank you!

[ray707]

Hello, I have a quick question about the code posted by dangelor 2 posts above. I want to amend this code to send out the email to an address in a specific cell in that worksheet; so for example, if I write an email address in cell D2 of the worksheet, I want the code to send the sheet to that email address. I thought I could do this by simple amending the .To part of the code:

Please Login or Register  to view this content.

However this did not work and no email was sent out. Please can someone advise?

[romperstomper]

Are you sure the codename of your sheet is Sheet1?

[ray707]

Are you sure the codename of your sheet is Sheet1?

In the actual excel file the sheet is called 'Craft summary' and in the visual basic under Microsoft Excel Objects it says 'Sheet1 (Craft summary)'. Am I inputting the name incorrectly for the sheet?

[romperstomper]

And that is the workbook with the code in it?

[ray707]

I got it working! I transferred the code from module to worksheet and that worked. Perhaps I need to improve my understanding of module vs worksheet as I thought it was always best to put the code in the module

Thanks!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[ray707]

Hi guys, quick question.

If I want the text in the body of the email to reference a cell in the excel sheet, how would I do that?

So for example, if the job number is in cell A9, and I want the text in the email body to say 'please find spreadsheet attached for A9', how would I amend the code?

Please Login or Register  to view this content.

Any help would be appreciated

[ray707]

hmmm the code to send out an email is not working now, I am not sure why. Does anyone have any idea why? When I click on the command button to send the email it creates a new file with that worksheet in it and nothing gets emailed. This didn't happen usually, it would just send that worksheet out automatically (no new file was created or anything, it would get emailed straight out to the recipient)

Please Login or Register  to view this content.

EDIT: The code works now so this post is redundant. If anyone can help with the post above that would be great