|
Email an Excel form input using VBA
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 |
Email an Excel form input using VBA
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? -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Jaime" wrote in message ... 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 |
Email an Excel form input using VBA
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 = " .From = " .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 = " .Subject = "Test " .Message = MyMessage .Delivery = xlAllAtOnce .ReturnWhenDone = True .TrackStatus = True End With Application.ScreenUpdating = True ActiveWorkbook.Route -----Original Message----- 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? -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Jaime" wrote in message ... Hi all, Have a question. Created a form in Excel and want to 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 . |
Email an Excel form input using VBA
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. -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Jaime" wrote in message ... 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 = " .From = " .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 = " .Subject = "Test " .Message = MyMessage .Delivery = xlAllAtOnce .ReturnWhenDone = True .TrackStatus = True End With Application.ScreenUpdating = True ActiveWorkbook.Route -----Original Message----- 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? -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Jaime" wrote in message ... Hi all, Have a question. Created a form in Excel and want to 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 . |
Email an Excel form input using VBA
Hi Dick and Jaime
Look in the problem section You must fill in the SMTP server in the code http://www.rondebruin.nl/cdo.htm#Problems -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Dick Kusleika" wrote in message ... 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. -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Jaime" wrote in message ... 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 = " .From = " .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 = " .Subject = "Test " .Message = MyMessage .Delivery = xlAllAtOnce .ReturnWhenDone = True .TrackStatus = True End With Application.ScreenUpdating = True ActiveWorkbook.Route -----Original Message----- 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? -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Jaime" wrote in message ... Hi all, Have a question. Created a form in Excel and want to 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 . |
Email an Excel form input using VBA
You are welcome
-- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl wrote in message ... That worked! :) Thanks so much! -----Original Message----- Hi Dick and Jaime Look in the problem section You must fill in the SMTP server in the code http://www.rondebruin.nl/cdo.htm#Problems -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Dick Kusleika" wrote in message news:% ... 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. -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Jaime" wrote in message ... 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 = " .From = " .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 = " .Subject = "Test " .Message = MyMessage .Delivery = xlAllAtOnce .ReturnWhenDone = True .TrackStatus = True End With Application.ScreenUpdating = True ActiveWorkbook.Route -----Original Message----- 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? -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Jaime" wrote in message ... Hi all, Have a question. Created a form in Excel and want to 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 . . |
| All times are GMT +1. The time now is 09:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com