Excel Submit Button

[Kenny100]

Hi Folks

Is it possible using VB to create a 'Submit' button in Excel that will send the current Excel worksheet to a specified email address?

If so, how the heck do I do it?! I'm using WinNT and Excel 97.

Cheers,
Kenny.

 

[xlbo]

What emaail app are you using ??

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[djeeten]

Hi,

as you might have seen I had a similar problem.

I solved it by using DoCmd.SendObject in the code behind the 'submit' button:

DoCmd.SendObject acSendForm, "frmNewOrder", acFormatRTF, "emailadres", , , "Definition", Message, False

You will have to use something different than acSendForm, but I'm not sure what


dj

 

[xlbo]

dj - excel NOT access......

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Nikita6003]

Hiya,

maybe these links'll help:

If you're using Outlook try: Thread707-460408
If it's GroupWise try: Thread707-475539
And if it's Lotus Notes try: Thread705-465811 or Thread68-451283

and there's loads more useful threads out there - I'm just too lazy to look 'em all up for you

HTH

Cheers
Nikki
"Look mommy - I'm flying!"

 

[acron]

Attach the following code to a button on the sheet you want to email.

The code creates a workbook containg a copy of the sheet you wish to send and uses the Workbook SendMail method email.

Sub eMailSheet()
ActiveSheet.Copy
ActiveSheet.DrawingObjects.Delete
With ActiveWorkbook
.SendMail Recipients:="emailAddress"
.Saved = True
.Close
End With
Ens Sub

The second line of code deletes any objects on the sheet so that the button to carry out the email is removed. If you have objects that you uwish to reatin on the sheet you will need to change that line to delete just the button that sends the form.

The workbook is closed without saving once the email is sent.

A.C.

 

[speedyfish]

Hi,

This has been a fantastic help to me, but I just have one question - acron, you mention that all that is needed to delete the button rather than all DrawingObjects is a simple change to the second line. I feel like a dunce, but I can't work out what change is needed to be made to that line. Could you please let me know what it is?

Thanks a bunch,

wil.

 

[xlbo]

ActiveSheet.DrawingObjects("DrawingObjectName&quot.delete
will delete a specified button - be careful tho - won't be the same syntax for "controls toolbox" buttons

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[speedyfish]

Thanks for that, but I'm still receiving the following error message when I actually name the button:

Unable to get the DrawingObjects property of the Worksheet class

I guess I just need to learn VBA properly...


Thanks,

wil.