Command Button

[Guest]

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

 

[steveb]

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

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...

 

[steveb]

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
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''

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?