Email an Excel form input using VBA

J

Jaime

Hi all,

Have a question. Created a form in Excel and want to email
just the input by the user not the excel spreasheet. How
can I perform this. I used activeworkbook.routing but this
emails the spreashhet.

If anyone can help me it would be greatly appreciated.

Thanks in advance!
Jaime
 
D

Dick Kusleika

Jaime

When you say form, do you mean UserForm, or do you mean that you've
replicated a form on an Excel worksheet?

What mail client are you using and do you need to be able to use it on
various mail clients?
 
J

Jaime

Yes, a user form create on the VBA project. I use the
Auto_Open on a macro to initialize the UserForm and
minimize the worksheet. The User then fills out the form
and the output is emailed to several recipients. I used
the collectiv data object (CDO) code. But some users
Outlook's are not current and they received a Run Time
Error (Sendusing eror). Searched the error and according
the the XGEN ariticle it's the outlook 2000 or 2002
without any service pack.

Dim iMsg As Object
Dim iConf As Object
Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
With iMsg
Set .Configuration = iConf
.To = "[email protected]"
.From = "[email protected]"
.Subject = "This is a test"
.Send
End With
Set iMsg = Nothing
Set iConf = Nothing
End Sub

I know that I can use Mapi to do this and it will prompt
users that this program will accesss their mail program.
But It was attaching he Spreadheet and I just want he
Email with the outut of the UserForm.
Here the code I used before...

Application.ScreenUpdating = True
ActiveSheet.Select
'ActiveWorkbook.HasRoutingSlip = True
With ActiveWorkbook.RoutingSlip

.Recipients = "[email protected]"
.Subject = "Test "
.Message = MyMessage
.Delivery = xlAllAtOnce
.ReturnWhenDone = True
.TrackStatus = True
End With
Application.ScreenUpdating = True
ActiveWorkbook.Route
 
D

Dick Kusleika

Jaime

You can automate Outlook which will give you the same result as the
RoutingSlip except that you will have more flexibility (you don't have to
attach the workbook). Look here for info on automating outlook

www.dicks-clicks.com

It would be nice, though, if you could get around that CDO error because you
wouldn't have to deal with the security prompt. Sadly, I don't know enough
about CDO to help you there. Everything I know comes from here

http://www.rondebruin.nl/cdo.htm

so it might be worth browsing around.
 
Top