send button

[farhospurchase]

I have created a form in excel. I would like to include a button that will
automatically email the form to a specific email address when pressed. I am
a new user and need step by step instructions.

[Richard Buttrey]

On Wed, 2 Aug 2006 14:47:33 -0700, farhospurchase
<[email protected]> wrote:

>I have created a form in excel. I would like to include a button that will
>automatically email the form to a specific email address when pressed. I am
>a new user and need step by step instructions.

A method I use fairly frequently is outlined below.


If your button is an Activex control 'Command Button' right click on
it whilst in Design mode and add the code below between the

Private Sub CommandButton1_Click()
End Sub

lines of of code.


If your 'button' is say an object like a clip art graphic, or a
drawing object such as a rectangle etc. then you'll need to put the
same code in a VBA macro procedure. Hit ALT and F11 to go into the
Visual Basic environment.


In either case:
In the Project Explorer window, select your current workbook and then
from the Menu "Insert Module"

In the blank code window probably on the right, add a

Sub SendEmail
End Sub

procedure and again copy the code below between the Sub & End Sub
lines.

You'll need to

1.give a range name called "MyForm" to the area covered by your form
2. In the code below change the "stEmailName" text to the email name
of your recipient
3. Change the "stFolder" text to a suitable folder name

Close the Visual Basic environment when you've finished or ALT F11
bacl


Now when you click your button the form will first be copied and saved
as a file called "MyForm.xls". Then Outlook will open (if it's not
already open), and the file will be attached to the email.
Alter the text of the
..Subject and .Body to something meaningful as appropriate

Code to be pasted between a Sub...End Sub
-----------------
Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim stEmailName As String
Dim olMyRecipient As Object
Dim stFolder As String

Set olApp = New Outlook.Application
stEmailName = "[email protected]"
stFolder = "c:\documents and settings\another\my documents"
Application.DisplayAlerts = False

Range("myform").Copy
Workbooks.Add
ActiveSheet.Paste
ActiveWorkbook.SaveAs Filename:="MyForm"
Set olMail = olApp.CreateItem(olMailItem)
Set olMyRecipient = olMail.Recipients.Add(stEmailName)

With olMail
.To = stEmailName
.Subject = "Your Subject"
.Body = "Body Text"
.Attachments.Add stFolder & "\Myform.xls"
End With

olMail.Send
----------

HTH

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________