Link to home
Start Free TrialLog in
Avatar of WAT1948
WAT1948

asked on

Excel 2007

Hi Experts... I am creating a spreadsheet that will act as a form to be filled out. It has several calculations; otherwise I would convert to a PDF form. I am looking to add an email hyperlink that will send the spread sheet to a recipient. I see where I can add the hyperlink and it creates an email to the recipient but I want it to also attach the spreadsheet to the email. I can do this with PDF forms but have been stumped so far in Excel.

I would appreciate any help. Thanks...
Avatar of ComputerAidNZ
ComputerAidNZ
Flag of United Kingdom of Great Britain and Northern Ireland image

From Excel
Click Globe
Select Send To
Select Email
You will then be presentded with your email client to send the attachment as normal
If you want a button on the excel SS, just bcreate a macro and save it as a button and voila!
Avatar of McOz
McOz

For this, you need to use a macro and assign it to a button. A hyperlink alone won't do the job.
Check out this link:

http://www.rondebruin.nl/mail/folder2/mail2.htm

It gives example of code to send the sheet as an attachment.

Good luck!
Avatar of WAT1948

ASKER

Thanks for the input... I have looked at all three and had the most success with Lordy123's link. I have the macro (which will bring up an email ready to be sent with the spreadsheet attached) and I placed the command button on the SS. I am having difficulty associating the macro with the command button though. Excel did not prompt for the macro and I have not found where to create the association yet.

I tried McOz's macro as well... it worked but I would prefer that the macro brings up the outlook client and allow the user to choose the receipeint before sending. Also I want the user to be able to save the SS without the macro immediatley deleting it (although I can see where this would be desirable in some applications).

Thanks for all of your input. If I just find out how to associate the macro with the command button the form will be all set.
If you have the VB code already inserted, then:
Open Macros from the Developer tab (I aseume you have this already, but if not, let me know), Click Options and assign a Shortcut Key so that the uses only has to press the shortcut key ant the code is then automatically run and opens mail client.
If you want the user to type i their own email address, just leave the "" from around the email address, but remove the email address and then the address line is blank
Macros is also avaialble from the VIew Tab
Or if you want the macro to run from the Quick Access Toolbar:
RIght-Click Menu Bar
Selectr  Custromise Quick Access Toolbar
Select Customise from Left Menu
Select Macros from Choose COmmands From
Double-Click the vb Code - it'll put it into the righthad side
Click OK
The you will bee a new button on the tiny menu up top!
Avatar of WAT1948

ASKER

Thanks... that works but I would rather have the command button execute the macro. Is there a way to associate the macro with the command button?
What command button do you have?
Maybe its a stage I missed
The only thing I can think of is you have a button already on your form, in which case firstly copy the code you wish to run on the button (excluding the Sub...End Sub bit) to the clipboard, then create a NEW button then follow steps below
After giving the Macro a Name
Click NEW
It'll open the VB code area.
Past in between the SUB... and End Sub from clipboard
It should be
Sub ButtonName
    Code Pasted from Clipboard
End Sub
Exit from the VB Code screen and test button, replace your original button

I don't know of a way of associating existing code with an existing button, YET
Avatar of WAT1948

ASKER

Thanks... I am running into trouble at this point. I created a new button and pasted the code from the email macro to command button as follows:

Private Sub SEND_Click()

End Sub

Private Sub CommandButton1_Click()
Application.Dialogs(xlDialogSendMail).Show arg1:="WAYNET@INTERCONBUILDINGCORP.com", _
                      arg2:="FIELD WORK AUTHORIZATION / CHANGE ORDER"
End Sub

When I click on it brings up the VB workspace. ???? Little bit lost at this point.
Avatar of WAT1948

ASKER

I think I am making to much of this. My intention was to create a form for my end users to fill out and email to their managers. I had in mind to create a form in acrobat but I needed the functionality of Excel built into the form. If I am distributing the spreadsheet as a form they can simply choose to send the spreadsheet via email. Having a send button would just make it dummy proof if they did not realize they could do this. Anyway I believe we are good with what we have thanks for all your help.
Shold be

Private Sub CommandButton1_Click()
Application.Dialogs(xlDialogSendMail).Show arg1:="WAYNET@INTERCONBUILDINGCORP.com", _
                      arg2:="FIELD WORK AUTHORIZATION / CHANGE ORDER"

End Sub

ensure you remove the sub... end sub from the copied text, so you have a new (from the creation of the new button)
Sub

End Sub
ASKER CERTIFIED SOLUTION
Avatar of ComputerAidNZ
ComputerAidNZ
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of WAT1948

ASKER

ComputerAidNZ... thanks for your help! I will keep trying tomorrow inspite of my last comment. I have to leave now for the evening. Will let you know.