Solved

Excel 2007

Posted on 2011-03-03
15
227 Views
Last Modified: 2012-05-11
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...
0
Comment
Question by:WAT1948
15 Comments
 
LVL 2

Expert Comment

by:ComputerAidNZ
Comment Utility
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!
0
 
LVL 6

Expert Comment

by:Lordy123
Comment Utility
This is exactly what you're looking for:

http://www.mrexcel.com/articles/email-an-excel-workbook.php
0
 
LVL 9

Expert Comment

by:McOz
Comment Utility
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!
0
 

Author Comment

by:WAT1948
Comment Utility
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.
0
 
LVL 2

Expert Comment

by:ComputerAidNZ
Comment Utility
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
0
 
LVL 2

Expert Comment

by:ComputerAidNZ
Comment Utility
Macros is also avaialble from the VIew Tab
0
 
LVL 2

Expert Comment

by:ComputerAidNZ
Comment Utility
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!
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:WAT1948
Comment Utility
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?
0
 
LVL 2

Expert Comment

by:ComputerAidNZ
Comment Utility
What command button do you have?
Maybe its a stage I missed
0
 
LVL 2

Expert Comment

by:ComputerAidNZ
Comment Utility
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
0
 

Author Comment

by:WAT1948
Comment Utility
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.
0
 

Author Comment

by:WAT1948
Comment Utility
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.
0
 
LVL 2

Expert Comment

by:ComputerAidNZ
Comment Utility
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
0
 
LVL 2

Accepted Solution

by:
ComputerAidNZ earned 500 total points
Comment Utility
Or:

Private Sub SEND_Click()

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

I think this was the new button name
0
 

Author Comment

by:WAT1948
Comment Utility
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.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now