Hi There,

I could not get a commandbutton (that I titled submit survey) to send the survey to me, so what I did was record a macro and tried to execute the macro from the button, it mostly works (see code below) but I keep getting object required, but do not understand what it is looking for ... Is anybody out.... Can Anybody help me :{ ... Confused and frustrated...

Private Sub CommandButton1_Click(
On Error GoTo Err_Command1_Clic

Dim stDocName As Strin

stDocName = "SubmitSurvey
DoCmd.RunMacro stDocNam

Exit_Command1_Click
Exit Su

Err_Command1_Click
MsgBox Err.Descriptio
Resume Exit_Command1_Clic

End Sub

Michelle,

Tried this in a dummy workbook and changed the line:
DoCmd.RunMacro stDocName
to
Run stDocName

and it worked like a champ.
(am using Excel 2002)

hth
steveb

"Michelle" wrote in message
...
Hi There,

I could not get a commandbutton (that I titled submit survey) to send the
survey to me, so what I did was record a macro and tried to execute the
macro from the button, it mostly works (see code below) but I keep getting
object required, but do not understand what it is looking for ... Is anybody
out.... Can Anybody help me :{ ... Confused and frustrated...

Private Sub CommandButton1_Click()
On Error GoTo Err_Command1_Click

Dim stDocName As String

stDocName = "SubmitSurvey"
DoCmd.RunMacro stDocName

Exit_Command1_Click:
Exit Sub

Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click

End Sub

Michelle,

Glad my little input helped! Now try this...

Here's some code (originally gotton from this group) that sends the active
file as email
Change the portions in quotes to suit your needs.

steveb
(Remove 'NOSPAM' from my email address if replying direct)

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''
Sub SendMyMail()
Dim subj As String
Application.DisplayAlerts = False

If MsgBox("Ready to send?", vbYesNo + vbQuestion, " email") = vbYes
Then
Sheets(2).Select
ActiveSheet.Copy
ActiveSheet.Protect
ActiveWorkbook.SaveAs "c:\Temp\filename.xls"

subj = Cells(3, 2) & " WhatYouWant "
subj = subj & InputBox("Add to your Subject Line", "email Subject")
subj = WorksheetFunction.Proper(subj)

'Emails the activeworkbook'
ActiveWorkbook.SendMail Recipients:="Your-email- address", _
Subject:=subj, ReturnReceipt:=True

ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False

End If
Application.DisplayAlerts = True
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''
''''''''''''''''''''''

"Michelle" wrote in message
...
Thanks Steve B this worked great, however, I still have a problem... if
you can help ... I then executed the macro but it did not pass the e-mail
address so the command just hangs, here is the is the code for the macro,
how do I pass the e-mail address and and send command to outlook via the
excel macro?

Sub SubmitSurvey()
'
' SubmitSurvey Macro
' Macro recorded 5/10/2004
'

'
Application.Dialogs(xlDialogSendMail).Show
End Sub




----- steveb wrote: -----

Michelle,

Tried this in a dummy workbook and changed the line:
DoCmd.RunMacro stDocName
to
Run stDocName

and it worked like a champ.
(am using Excel 2002)

hth
steveb

"Michelle" wrote in message
...
Hi There,
I could not get a commandbutton (that I titled submit survey) to
send the
survey to me, so what I did was record a macro and tried to execute
the
macro from the button, it mostly works (see code below) but I keep
getting
object required, but do not understand what it is looking for ... Is
anybody
out.... Can Anybody help me :{ ... Confused and frustrated...
Private Sub CommandButton1_Click()
On Error GoTo Err_Command1_Click
Dim stDocName As String
stDocName = "SubmitSurvey"
DoCmd.RunMacro stDocName
Exit_Command1_Click:
Exit Sub
Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click
End Sub