Excel --submit worksheet via E-mail with user defined SUBJECT field

[5656]

I currently have a MS Excel based "form" set-up and have some code that submits the form via e-mai upon clicking a button on the Excel form.

My dillema: I want the user of the Excel "form" to be able to input what the subject of the e-mail will be into a designated cell / text area located on the excel "form".
Is this possible ?

We are using Office 97 and Microsoft Exchange 5.0

Here's my current code:
--------------------------------------
Sub Button5_Click()

ActiveWorkbook.SendMail Recipients:=(&quot;emsg2@.juded.com&quot, ReturnReceipt:=True, Subject:=&quot;Request for Support&quot; <<< WOULD LIKE SUBJECT TO BE VARIABLE / BASED ON USER INPUT

Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = &quot;Request for Support has been submitted.&quot; ' Define message.
Style = msoButtonSetNone ' Define buttons.
Title = &quot;Send Confirmation&quot; ' Define title.
Help = &quot;DEMO.HLP&quot; ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.

MyString = &quot;Yes&quot; ' Perform some action.
Else ' User chose No.
MyString = &quot;No&quot; ' Perform some action.
End If

End Sub
-------------------------
Would greatly appreciate any assistance...or guidance.

Thank you.

 

[ColinC]

Have you tried using an input box?

Dim SomeString

SomeString = InputBox(&quot;Enter Subject:&quot

Subject:=SomeString
Colin Chevrier
colin.chevrier@ca.jdsuniphase.com

 

[5656]

ColinC ~
Thank you for the response. As you can probably tell I'm not fluent at all with VB, I've tried what you recommended as it makes very much sense to me...however I can't get the input box to take the value I give it !! When you said &quot;InputBox&quot; is that the same thing as a Text Box ?
Should I be putting the script you gave me in the same &quot;Module&quot; , &quot;Sub&quot; and &quot;Dim&quot; as my submit button ?
Everytime I try to create a macro for the Text Box it starts a &quot;Private Sub&quot; is that what I need ?
Sorry about my lack of knowledge...I'm trying to get this to work for my group at work as it will be a great timesaver tool.
Thanks again !!

 

[ColinC]

InputBox is a function supplied by VBA. The function returns a string value (the VB help files in Excel explain the function better than I can).

Sub Button5_Click()
Dim Msg As String, SomeString As String, Title As String, Help As String, MyString As String
Dim Style As Integer, Ctxt As Integer, Response As Integer

SomeString = InputBox(&quot;Enter Subject:&quot
ActiveWorkbook.SendMail Recipients:=(&quot;emsg2@.juded.com&quot, ReturnReceipt:=True, Subject:=SomeString

Msg = &quot;Request for Support has been submitted.&quot; ' Define message.
Style = msoButtonSetNone ' Define buttons.
Title = &quot;Send Confirmation&quot; ' Define title.
Help = &quot;DEMO.HLP&quot; ' Define Help file.
Ctxt = 1000 ' Define topic context. Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)

If Response = vbYes Then ' User chose Yes.
MyString = &quot;Yes&quot; ' Perform some action.
Else ' User chose No.
MyString = &quot;No&quot; ' Perform some action.
End If

End Sub

Another thing you should consider is declaring your variables as specific data types. When you don't specify a type it is assumed to be of type Variant. I've included part of the help files which summarize the data type sizes.

Variant
(with numbers) 16 bytes

Variant
(with characters) 22 bytes + string length

Integer 2 bytes

String
(variable-length) 10 bytes + string length

String
(fixed-length) Length of string
Colin Chevrier
colin.chevrier@ca.jdsuniphase.com