email a worksheet, using info from cells

[pbucc]

hi there this is my first post.

what i have is an excel sheet the employees fill out. i would like to save the file as: network number (the number is pulled from a cell like A1) then email that newly saved file to an email that i have like [email protected]. the subject line and body needs to read here is the network number (cell A1). the employee can hit the send button. it has to work in both 2003 and 2007.

i can have it mail using the cell info using the hyperlink, and have it save the file name but i can't get it to do both.

any help would be great.

[gjlindn]

Would you mind posting your current code? Thanks!

[pbucc]

Would you mind posting your current code? Thanks!

here is the Hyperlink:

=HYPERLINK("mailto:[email protected]" &"?subject=Copper Pro Exclusion Sheet for " &A1, "Send to the cdna")

so this will send the mail with the subject pulling from the cell A1

here is what i am using to save the file:

Please Login or Register  to view this content.

i hope this helps.

[gjlindn]

Hi pbucc...sorry for the delayed response. It's been a busy weekend. Remember to add code tags around your code when posting to this forum (moderators will give you infractions for not having them). If you're using Outlook, you could use this

Please Login or Register  to view this content.

Just add a call to it as the last line in your Save routine.

[snb]

or

Please Login or Register  to view this content.

[pbucc]

or

Please Login or Register  to view this content.

this works but there are 2 issues.

1: i need to save the file as the network number and email the saved file with the network number in the subject.

2: when i use the hyperlink it just opens the email and i hit send. the above code opens that window that count's down an you hit yes.

i need this to work in both 2003 and 2007.

[gjlindn]

There's no reason to think snb's code wouldn't work in both 2007 and 2003...but you will get the Outlook prompt notifiying you that an external app is trying to get info in either case. If you add a call to EmailMe in your save routine, when you run that routine it will also email.

Please Login or Register  to view this content.

[gjlindn]

Here's another solution that does not use Outlook and therefore no Outlook warning. Change "smtp.yourserver.com" to your SMTP server (if not sure how to do this, do a search on Google--how do i find my smtp server)

Please Login or Register  to view this content.

[pbucc]

There's no reason to think snb's code wouldn't work in both 2007 and 2003...but you will get the Outlook prompt notifiying you that an external app is trying to get info in either case. If you add a call to EmailMe in your save routine, when you run that routine it will also email.

Please Login or Register  to view this content.

This works great in 2003, but in 2007 we have a macros issue. i work in a large company and i think Macros are turned off by default. i have about 100 employees that need to use this. even if we copy the worksheet and paste in the the email body...

[Leith Ross]

Hello pbucc,

There is no native method in Excel to accomplish both tasks. To do both requires using VBA. If macros can not be enabled then you my friend are SOL.

[pbucc]

ok i have it working. atteched a mass email to the employees to deal with the Macros. the one thing that is the file name is putting % in place of the spaces. again this is just in 2007. 2003 is fine.

[pbucc]

this is working fine. the code works. just had to educate the workers to set the macros security.

[SOLVED]