Userform help needed

[Tim]

First I would like to qualify; I’m not a programmer, far from it. I’m a
police officer that is trying to take my agency from the pen and paper era to
this ‘new’ era called the ‘computer age’. I have been working on this project
(on my own time) for about two years. Everything that I learned so far has
been from this discussion group or a site that I was directed to. I would
like to say thanks to all the people that helped/directed me, written code,
etc., without your help I would have given up a long time ago.

OK enough sucking up. Here is my next project. I would like to try and add
some Userforms to the program. I’ve done a little homework and so far I’ve
been able to build the form, now I need it to do what I want it to.

I plan to have a button on ‘Sheet 1’ that calls the form up. The form has
six text boxes on it (txtDate, txtName, txtPerson, txtTime, txtRelease,
TxtNextdate). I would like the data from those text fields to go to specific
cells on a sheet named ‘Employee List’. i.e. txtDate to G45. I would then
like it to run the following email code (that I found on Ron Debruin’s site,
Thanks Ron). I would like person to be able to fill in the boxes and then
click on one button on the form (cmdEmailtodata) and have it do both
processes.

Sorry for the long post.
Thanks

Private Declare Function ShellExecute Lib "Shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As
String, _
ByVal nShowCmd As Long) As Long
________________________________________________________________________
Sub Mail_Text_in_Body_3()
'Creates statement for person and emails it to data entry
Dim msg As String, URL As String
Dim Recipient As String, Subj As String
Dim cell As Range
Recipient = "[email protected]"

Subj = "Statement for " & " for Incident " & Sheets("Employee
List").Range("N7").Value

msg = "Statement of " & vbNewLine & vbNewLine
For Each cell In Sheets("Employee List").Range("N3")
msg = msg & vbNewLine & cell
Next cell
msg = WorksheetFunction.Substitute(msg, vbNewLine, "%0D%0A")

msg = WorksheetFunction.Substitute(msg, vbLf, "%0D%0A")
URL = "mailto:" & Recipient & "&subject=" & Subj & "&body=" & msg
ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString,
vbNormalFocus
Application.Wait (Now + TimeValue("0:00:03"))
Application.SendKeys "%s"
End Sub

[moi]

Wait a sec.

I have a UserForm now (sample is here:
http://www.geocities.com/smplprgrsrc/XLSMailForm.zip), with 6 EditBoxes:
txtDate, txtName, txtPerson, txtTime, txtRelease and txtNextDate.
There's a button on Sheet1 which launches the form. After a click on button
cmdEmailtodata, the form values are being sent to sheet "Employee List".
So I send
txtDate to G45
txtName to H45
txtPerson to I45
txtTime to J45
txtRelease to K45
and txtNextDate to L45

Tada. So far so good, althoug I think I'm already going in the wrong
direction here.

However, in Ron's api-call, I just don't get the Subject line;
Subj = "Statement for " & " for Incident " & Sheets("Employee
List").Range("N7").Value


Statement for & WHO/WHAT & for incident...

And then: What's in cell N7? Also: What's in cell N3?

Or should I walk through G45-L45, construct the mail message from all cells
and then send it?



"Tim" <[email protected]> schreef in bericht
news:[email protected]...
> First I would like to qualify; I'm not a programmer, far from it. I'm a
> police officer that is trying to take my agency from the pen and paper era
> to
> this 'new' era called the 'computer age'. I have been working on this
> project
> (on my own time) for about two years. Everything that I learned so far has
> been from this discussion group or a site that I was directed to. I would
> like to say thanks to all the people that helped/directed me, written
> code,
> etc., without your help I would have given up a long time ago.
>
> OK enough sucking up. Here is my next project. I would like to try and add
> some Userforms to the program. I've done a little homework and so far I've
> been able to build the form, now I need it to do what I want it to.
>
> I plan to have a button on 'Sheet 1' that calls the form up. The form has
> six text boxes on it (txtDate, txtName, txtPerson, txtTime, txtRelease,
> TxtNextdate). I would like the data from those text fields to go to
> specific
> cells on a sheet named 'Employee List'. i.e. txtDate to G45. I would then
> like it to run the following email code (that I found on Ron Debruin's
> site,
> Thanks Ron). I would like person to be able to fill in the boxes and then
> click on one button on the form (cmdEmailtodata) and have it do both
> processes.
>
> Sorry for the long post.
> Thanks
>
> Private Declare Function ShellExecute Lib "Shell32.dll" _
> Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
> ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As
> String, _
> ByVal nShowCmd As Long) As Long
> ________________________________________________________________________
> Sub Mail_Text_in_Body_3()
> 'Creates statement for person and emails it to data entry
> Dim msg As String, URL As String
> Dim Recipient As String, Subj As String
> Dim cell As Range
> Recipient = "[email protected]"
>
> Subj = "Statement for " & " for Incident " & Sheets("Employee
> List").Range("N7").Value
>
> msg = "Statement of " & vbNewLine & vbNewLine
> For Each cell In Sheets("Employee List").Range("N3")
> msg = msg & vbNewLine & cell
> Next cell
> msg = WorksheetFunction.Substitute(msg, vbNewLine, "%0D%0A")
>
> msg = WorksheetFunction.Substitute(msg, vbLf, "%0D%0A")
> URL = "mailto:" & Recipient & "&subject=" & Subj & "&body=" & msg
> ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString,
> vbNormalFocus
> Application.Wait (Now + TimeValue("0:00:03"))
> Application.SendKeys "%s"
> End Sub
>

[Tim]

N3 is what the completed statement is which goes into the body of the email.
N7 gets incident number from a cell on another sheet and that data is used to
build the re: line in the email. I will be using the data from(txtPerson) to
fill in "Statement for" (&).

Thanks "moi"

"moi" wrote:

> Wait a sec.
>
> I have a UserForm now (sample is here:
> http://www.geocities.com/smplprgrsrc/XLSMailForm.zip), with 6 EditBoxes:
> txtDate, txtName, txtPerson, txtTime, txtRelease and txtNextDate.
> There's a button on Sheet1 which launches the form. After a click on button
> cmdEmailtodata, the form values are being sent to sheet "Employee List".
> So I send
> txtDate to G45
> txtName to H45
> txtPerson to I45
> txtTime to J45
> txtRelease to K45
> and txtNextDate to L45
>
> Tada. So far so good, althoug I think I'm already going in the wrong
> direction here.
>
> However, in Ron's api-call, I just don't get the Subject line;
> Subj = "Statement for " & " for Incident " & Sheets("Employee
> List").Range("N7").Value
>
>
> Statement for & WHO/WHAT & for incident...
>
> And then: What's in cell N7? Also: What's in cell N3?
>
> Or should I walk through G45-L45, construct the mail message from all cells
> and then send it?
>
>
>
> "Tim" <[email protected]> schreef in bericht
> news:[email protected]...
> > First I would like to qualify; I'm not a programmer, far from it. I'm a
> > police officer that is trying to take my agency from the pen and paper era
> > to
> > this 'new' era called the 'computer age'. I have been working on this
> > project
> > (on my own time) for about two years. Everything that I learned so far has
> > been from this discussion group or a site that I was directed to. I would
> > like to say thanks to all the people that helped/directed me, written
> > code,
> > etc., without your help I would have given up a long time ago.
> >
> > OK enough sucking up. Here is my next project. I would like to try and add
> > some Userforms to the program. I've done a little homework and so far I've
> > been able to build the form, now I need it to do what I want it to.
> >
> > I plan to have a button on 'Sheet 1' that calls the form up. The form has
> > six text boxes on it (txtDate, txtName, txtPerson, txtTime, txtRelease,
> > TxtNextdate). I would like the data from those text fields to go to
> > specific
> > cells on a sheet named 'Employee List'. i.e. txtDate to G45. I would then
> > like it to run the following email code (that I found on Ron Debruin's
> > site,
> > Thanks Ron). I would like person to be able to fill in the boxes and then
> > click on one button on the form (cmdEmailtodata) and have it do both
> > processes.
> >
> > Sorry for the long post.
> > Thanks
> >
> > Private Declare Function ShellExecute Lib "Shell32.dll" _
> > Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
> > ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As
> > String, _
> > ByVal nShowCmd As Long) As Long
> > ________________________________________________________________________
> > Sub Mail_Text_in_Body_3()
> > 'Creates statement for person and emails it to data entry
> > Dim msg As String, URL As String
> > Dim Recipient As String, Subj As String
> > Dim cell As Range
> > Recipient = "[email protected]"
> >
> > Subj = "Statement for " & " for Incident " & Sheets("Employee
> > List").Range("N7").Value
> >
> > msg = "Statement of " & vbNewLine & vbNewLine
> > For Each cell In Sheets("Employee List").Range("N3")
> > msg = msg & vbNewLine & cell
> > Next cell
> > msg = WorksheetFunction.Substitute(msg, vbNewLine, "%0D%0A")
> >
> > msg = WorksheetFunction.Substitute(msg, vbLf, "%0D%0A")
> > URL = "mailto:" & Recipient & "&subject=" & Subj & "&body=" & msg
> > ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString,
> > vbNormalFocus
> > Application.Wait (Now + TimeValue("0:00:03"))
> > Application.SendKeys "%s"
> > End Sub
> >
>
>
>

[moi]

hang on...


"Tim" <[email protected]> schreef in bericht
news:[email protected]...
> N3 is what the completed statement is which goes into the body of the
> email.
> N7 gets incident number from a cell on another sheet and that data is used
> to
> build the re: line in the email. I will be using the data from(txtPerson)
> to
> fill in "Statement for" (&).
>
> Thanks "moi"
>
> "moi" wrote:
>
>> Wait a sec.
>>
>> I have a UserForm now (sample is here:
>> http://www.geocities.com/smplprgrsrc/XLSMailForm.zip), with 6 EditBoxes:
>> txtDate, txtName, txtPerson, txtTime, txtRelease and txtNextDate.
>> There's a button on Sheet1 which launches the form. After a click on
>> button
>> cmdEmailtodata, the form values are being sent to sheet "Employee List".
>> So I send
>> txtDate to G45
>> txtName to H45
>> txtPerson to I45
>> txtTime to J45
>> txtRelease to K45
>> and txtNextDate to L45
>>
>> Tada. So far so good, althoug I think I'm already going in the wrong
>> direction here.
>>
>> However, in Ron's api-call, I just don't get the Subject line;
>> Subj = "Statement for " & " for Incident " & Sheets("Employee
>> List").Range("N7").Value
>>
>>
>> Statement for & WHO/WHAT & for incident...
>>
>> And then: What's in cell N7? Also: What's in cell N3?
>>
>> Or should I walk through G45-L45, construct the mail message from all
>> cells
>> and then send it?
>>
>>
>>
>> "Tim" <[email protected]> schreef in bericht
>> news:[email protected]...
>> > First I would like to qualify; I'm not a programmer, far from it. I'm a
>> > police officer that is trying to take my agency from the pen and paper
>> > era
>> > to
>> > this 'new' era called the 'computer age'. I have been working on this
>> > project
>> > (on my own time) for about two years. Everything that I learned so far
>> > has
>> > been from this discussion group or a site that I was directed to. I
>> > would
>> > like to say thanks to all the people that helped/directed me, written
>> > code,
>> > etc., without your help I would have given up a long time ago.
>> >
>> > OK enough sucking up. Here is my next project. I would like to try and
>> > add
>> > some Userforms to the program. I've done a little homework and so far
>> > I've
>> > been able to build the form, now I need it to do what I want it to.
>> >
>> > I plan to have a button on 'Sheet 1' that calls the form up. The form
>> > has
>> > six text boxes on it (txtDate, txtName, txtPerson, txtTime, txtRelease,
>> > TxtNextdate). I would like the data from those text fields to go to
>> > specific
>> > cells on a sheet named 'Employee List'. i.e. txtDate to G45. I would
>> > then
>> > like it to run the following email code (that I found on Ron Debruin's
>> > site,
>> > Thanks Ron). I would like person to be able to fill in the boxes and
>> > then
>> > click on one button on the form (cmdEmailtodata) and have it do both
>> > processes.
>> >
>> > Sorry for the long post.
>> > Thanks
>> >
>> > Private Declare Function ShellExecute Lib "Shell32.dll" _
>> > Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String,
>> > _
>> > ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory
>> > As
>> > String, _
>> > ByVal nShowCmd As Long) As Long
>> > ________________________________________________________________________
>> > Sub Mail_Text_in_Body_3()
>> > 'Creates statement for person and emails it to data entry
>> > Dim msg As String, URL As String
>> > Dim Recipient As String, Subj As String
>> > Dim cell As Range
>> > Recipient = "[email protected]"
>> >
>> > Subj = "Statement for " & " for Incident " & Sheets("Employee
>> > List").Range("N7").Value
>> >
>> > msg = "Statement of " & vbNewLine & vbNewLine
>> > For Each cell In Sheets("Employee List").Range("N3")
>> > msg = msg & vbNewLine & cell
>> > Next cell
>> > msg = WorksheetFunction.Substitute(msg, vbNewLine, "%0D%0A")
>> >
>> > msg = WorksheetFunction.Substitute(msg, vbLf, "%0D%0A")
>> > URL = "mailto:" & Recipient & "&subject=" & Subj & "&body=" & msg
>> > ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString,
>> > vbNormalFocus
>> > Application.Wait (Now + TimeValue("0:00:03"))
>> > Application.SendKeys "%s"
>> > End Sub
>> >
>>
>>
>>

[Bob Phillips]

Tim,

You could simply add a button to the form, load the worksheet from there,
and then run Ron's macro.

I just knocked up a simple example that worked fine.

Is that what you are asking or am I missing something?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Tim" <[email protected]> wrote in message
news:[email protected]...
> N3 is what the completed statement is which goes into the body of the
email.
> N7 gets incident number from a cell on another sheet and that data is used
to
> build the re: line in the email. I will be using the data from(txtPerson)
to
> fill in "Statement for" (&).
>
> Thanks "moi"
>
> "moi" wrote:
>
> > Wait a sec.
> >
> > I have a UserForm now (sample is here:
> > http://www.geocities.com/smplprgrsrc/XLSMailForm.zip), with 6 EditBoxes:
> > txtDate, txtName, txtPerson, txtTime, txtRelease and txtNextDate.
> > There's a button on Sheet1 which launches the form. After a click on
button
> > cmdEmailtodata, the form values are being sent to sheet "Employee List".
> > So I send
> > txtDate to G45
> > txtName to H45
> > txtPerson to I45
> > txtTime to J45
> > txtRelease to K45
> > and txtNextDate to L45
> >
> > Tada. So far so good, althoug I think I'm already going in the wrong
> > direction here.
> >
> > However, in Ron's api-call, I just don't get the Subject line;
> > Subj = "Statement for " & " for Incident " & Sheets("Employee
> > List").Range("N7").Value
> >
> >
> > Statement for & WHO/WHAT & for incident...
> >
> > And then: What's in cell N7? Also: What's in cell N3?
> >
> > Or should I walk through G45-L45, construct the mail message from all
cells
> > and then send it?
> >
> >
> >
> > "Tim" <[email protected]> schreef in bericht
> > news:[email protected]...
> > > First I would like to qualify; I'm not a programmer, far from it. I'm
a
> > > police officer that is trying to take my agency from the pen and paper
era
> > > to
> > > this 'new' era called the 'computer age'. I have been working on this
> > > project
> > > (on my own time) for about two years. Everything that I learned so far
has
> > > been from this discussion group or a site that I was directed to. I
would
> > > like to say thanks to all the people that helped/directed me, written
> > > code,
> > > etc., without your help I would have given up a long time ago.
> > >
> > > OK enough sucking up. Here is my next project. I would like to try and
add
> > > some Userforms to the program. I've done a little homework and so far
I've
> > > been able to build the form, now I need it to do what I want it to.
> > >
> > > I plan to have a button on 'Sheet 1' that calls the form up. The form
has
> > > six text boxes on it (txtDate, txtName, txtPerson, txtTime,
txtRelease,
> > > TxtNextdate). I would like the data from those text fields to go to
> > > specific
> > > cells on a sheet named 'Employee List'. i.e. txtDate to G45. I would
then
> > > like it to run the following email code (that I found on Ron Debruin's
> > > site,
> > > Thanks Ron). I would like person to be able to fill in the boxes and
then
> > > click on one button on the form (cmdEmailtodata) and have it do both
> > > processes.
> > >
> > > Sorry for the long post.
> > > Thanks
> > >
> > > Private Declare Function ShellExecute Lib "Shell32.dll" _
> > > Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As
String, _
> > > ByVal lpFile As String, ByVal lpParameters As String, ByVal
lpDirectory As
> > > String, _
> > > ByVal nShowCmd As Long) As Long
> > >
________________________________________________________________________
> > > Sub Mail_Text_in_Body_3()
> > > 'Creates statement for person and emails it to data entry
> > > Dim msg As String, URL As String
> > > Dim Recipient As String, Subj As String
> > > Dim cell As Range
> > > Recipient = "[email protected]"
> > >
> > > Subj = "Statement for " & " for Incident " & Sheets("Employee
> > > List").Range("N7").Value
> > >
> > > msg = "Statement of " & vbNewLine & vbNewLine
> > > For Each cell In Sheets("Employee List").Range("N3")
> > > msg = msg & vbNewLine & cell
> > > Next cell
> > > msg = WorksheetFunction.Substitute(msg, vbNewLine, "%0D%0A")
> > >
> > > msg = WorksheetFunction.Substitute(msg, vbLf, "%0D%0A")
> > > URL = "mailto:" & Recipient & "&subject=" & Subj & "&body=" & msg
> > > ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString,
> > > vbNormalFocus
> > > Application.Wait (Now + TimeValue("0:00:03"))
> > > Application.SendKeys "%s"
> > > End Sub
> > >
> >
> >
> >

[Tim]

The sheet "Employee List" is a hidden sheet in the workbook. I was hoping to
have a button on my main worksheet (Sheet1) that would call the form up, have
the user fillin the text fields and then have one button on the form that
would transfer the data to the hidden sheet (Employee List), then run the
email code.
I could add a second button for the email but I was hoping to do it all in
one.



"Bob Phillips" wrote:

> Tim,
>
> You could simply add a button to the form, load the worksheet from there,
> and then run Ron's macro.
>
> I just knocked up a simple example that worked fine.
>
> Is that what you are asking or am I missing something?
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Tim" <[email protected]> wrote in message
> news:[email protected]...
> > N3 is what the completed statement is which goes into the body of the
> email.
> > N7 gets incident number from a cell on another sheet and that data is used
> to
> > build the re: line in the email. I will be using the data from(txtPerson)
> to
> > fill in "Statement for" (&).
> >
> > Thanks "moi"
> >
> > "moi" wrote:
> >
> > > Wait a sec.
> > >
> > > I have a UserForm now (sample is here:
> > > http://www.geocities.com/smplprgrsrc/XLSMailForm.zip), with 6 EditBoxes:
> > > txtDate, txtName, txtPerson, txtTime, txtRelease and txtNextDate.
> > > There's a button on Sheet1 which launches the form. After a click on
> button
> > > cmdEmailtodata, the form values are being sent to sheet "Employee List".
> > > So I send
> > > txtDate to G45
> > > txtName to H45
> > > txtPerson to I45
> > > txtTime to J45
> > > txtRelease to K45
> > > and txtNextDate to L45
> > >
> > > Tada. So far so good, althoug I think I'm already going in the wrong
> > > direction here.
> > >
> > > However, in Ron's api-call, I just don't get the Subject line;
> > > Subj = "Statement for " & " for Incident " & Sheets("Employee
> > > List").Range("N7").Value
> > >
> > >
> > > Statement for & WHO/WHAT & for incident...
> > >
> > > And then: What's in cell N7? Also: What's in cell N3?
> > >
> > > Or should I walk through G45-L45, construct the mail message from all
> cells
> > > and then send it?
> > >
> > >
> > >
> > > "Tim" <[email protected]> schreef in bericht
> > > news:[email protected]...
> > > > First I would like to qualify; I'm not a programmer, far from it. I'm
> a
> > > > police officer that is trying to take my agency from the pen and paper
> era
> > > > to
> > > > this 'new' era called the 'computer age'. I have been working on this
> > > > project
> > > > (on my own time) for about two years. Everything that I learned so far
> has
> > > > been from this discussion group or a site that I was directed to. I
> would
> > > > like to say thanks to all the people that helped/directed me, written
> > > > code,
> > > > etc., without your help I would have given up a long time ago.
> > > >
> > > > OK enough sucking up. Here is my next project. I would like to try and
> add
> > > > some Userforms to the program. I've done a little homework and so far
> I've
> > > > been able to build the form, now I need it to do what I want it to.
> > > >
> > > > I plan to have a button on 'Sheet 1' that calls the form up. The form
> has
> > > > six text boxes on it (txtDate, txtName, txtPerson, txtTime,
> txtRelease,
> > > > TxtNextdate). I would like the data from those text fields to go to
> > > > specific
> > > > cells on a sheet named 'Employee List'. i.e. txtDate to G45. I would
> then
> > > > like it to run the following email code (that I found on Ron Debruin's
> > > > site,
> > > > Thanks Ron). I would like person to be able to fill in the boxes and
> then
> > > > click on one button on the form (cmdEmailtodata) and have it do both
> > > > processes.
> > > >
> > > > Sorry for the long post.
> > > > Thanks
> > > >
> > > > Private Declare Function ShellExecute Lib "Shell32.dll" _
> > > > Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As
> String, _
> > > > ByVal lpFile As String, ByVal lpParameters As String, ByVal
> lpDirectory As
> > > > String, _
> > > > ByVal nShowCmd As Long) As Long
> > > >
> ________________________________________________________________________
> > > > Sub Mail_Text_in_Body_3()
> > > > 'Creates statement for person and emails it to data entry
> > > > Dim msg As String, URL As String
> > > > Dim Recipient As String, Subj As String
> > > > Dim cell As Range
> > > > Recipient = "[email protected]"
> > > >
> > > > Subj = "Statement for " & " for Incident " & Sheets("Employee
> > > > List").Range("N7").Value
> > > >
> > > > msg = "Statement of " & vbNewLine & vbNewLine
> > > > For Each cell In Sheets("Employee List").Range("N3")
> > > > msg = msg & vbNewLine & cell
> > > > Next cell
> > > > msg = WorksheetFunction.Substitute(msg, vbNewLine, "%0D%0A")
> > > >
> > > > msg = WorksheetFunction.Substitute(msg, vbLf, "%0D%0A")
> > > > URL = "mailto:" & Recipient & "&subject=" & Subj & "&body=" & msg
> > > > ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString,
> > > > vbNormalFocus
> > > > Application.Wait (Now + TimeValue("0:00:03"))
> > > > Application.SendKeys "%s"
> > > > End Sub
> > > >
> > >
> > >
> > >
>
>
>

[moi]

Ron's macro is working, but only when I replace the ampersand with a
question mark in the subject. Else I get the full msg-line in the address
bar.

Same link as above Tim, it's updated.


"Bob Phillips" <[email protected]> schreef in bericht
news:[email protected]...
> Tim,
>
> You could simply add a button to the form, load the worksheet from there,
> and then run Ron's macro.
>
> I just knocked up a simple example that worked fine.
>
> Is that what you are asking or am I missing something?
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Tim" <[email protected]> wrote in message
> news:[email protected]...
>> N3 is what the completed statement is which goes into the body of the
> email.
>> N7 gets incident number from a cell on another sheet and that data is
>> used
> to
>> build the re: line in the email. I will be using the data from(txtPerson)
> to
>> fill in "Statement for" (&).
>>
>> Thanks "moi"
>>
>> "moi" wrote:
>>
>> > Wait a sec.
>> >
>> > I have a UserForm now (sample is here:
>> > http://www.geocities.com/smplprgrsrc/XLSMailForm.zip), with 6
>> > EditBoxes:
>> > txtDate, txtName, txtPerson, txtTime, txtRelease and txtNextDate.
>> > There's a button on Sheet1 which launches the form. After a click on
> button
>> > cmdEmailtodata, the form values are being sent to sheet "Employee
>> > List".
>> > So I send
>> > txtDate to G45
>> > txtName to H45
>> > txtPerson to I45
>> > txtTime to J45
>> > txtRelease to K45
>> > and txtNextDate to L45
>> >
>> > Tada. So far so good, althoug I think I'm already going in the wrong
>> > direction here.
>> >
>> > However, in Ron's api-call, I just don't get the Subject line;
>> > Subj = "Statement for " & " for Incident " & Sheets("Employee
>> > List").Range("N7").Value
>> >
>> >
>> > Statement for & WHO/WHAT & for incident...
>> >
>> > And then: What's in cell N7? Also: What's in cell N3?
>> >
>> > Or should I walk through G45-L45, construct the mail message from all
> cells
>> > and then send it?
>> >
>> >
>> >
>> > "Tim" <[email protected]> schreef in bericht
>> > news:[email protected]...
>> > > First I would like to qualify; I'm not a programmer, far from it. I'm
> a
>> > > police officer that is trying to take my agency from the pen and
>> > > paper
> era
>> > > to
>> > > this 'new' era called the 'computer age'. I have been working on this
>> > > project
>> > > (on my own time) for about two years. Everything that I learned so
>> > > far
> has
>> > > been from this discussion group or a site that I was directed to. I
> would
>> > > like to say thanks to all the people that helped/directed me, written
>> > > code,
>> > > etc., without your help I would have given up a long time ago.
>> > >
>> > > OK enough sucking up. Here is my next project. I would like to try
>> > > and
> add
>> > > some Userforms to the program. I've done a little homework and so far
> I've
>> > > been able to build the form, now I need it to do what I want it to.
>> > >
>> > > I plan to have a button on 'Sheet 1' that calls the form up. The form
> has
>> > > six text boxes on it (txtDate, txtName, txtPerson, txtTime,
> txtRelease,
>> > > TxtNextdate). I would like the data from those text fields to go to
>> > > specific
>> > > cells on a sheet named 'Employee List'. i.e. txtDate to G45. I would
> then
>> > > like it to run the following email code (that I found on Ron
>> > > Debruin's
>> > > site,
>> > > Thanks Ron). I would like person to be able to fill in the boxes and
> then
>> > > click on one button on the form (cmdEmailtodata) and have it do both
>> > > processes.
>> > >
>> > > Sorry for the long post.
>> > > Thanks
>> > >
>> > > Private Declare Function ShellExecute Lib "Shell32.dll" _
>> > > Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As
> String, _
>> > > ByVal lpFile As String, ByVal lpParameters As String, ByVal
> lpDirectory As
>> > > String, _
>> > > ByVal nShowCmd As Long) As Long
>> > >
> ________________________________________________________________________
>> > > Sub Mail_Text_in_Body_3()
>> > > 'Creates statement for person and emails it to data entry
>> > > Dim msg As String, URL As String
>> > > Dim Recipient As String, Subj As String
>> > > Dim cell As Range
>> > > Recipient = "[email protected]"
>> > >
>> > > Subj = "Statement for " & " for Incident " & Sheets("Employee
>> > > List").Range("N7").Value
>> > >
>> > > msg = "Statement of " & vbNewLine & vbNewLine
>> > > For Each cell In Sheets("Employee List").Range("N3")
>> > > msg = msg & vbNewLine & cell
>> > > Next cell
>> > > msg = WorksheetFunction.Substitute(msg, vbNewLine, "%0D%0A")
>> > >
>> > > msg = WorksheetFunction.Substitute(msg, vbLf, "%0D%0A")
>> > > URL = "mailto:" & Recipient & "&subject=" & Subj & "&body=" & msg
>> > > ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString,
>> > > vbNormalFocus
>> > > Application.Wait (Now + TimeValue("0:00:03"))
>> > > Application.SendKeys "%s"
>> > > End Sub
>> > >
>> >
>> >
>> >
>
>

[Bob Phillips]

Add a button from the forms toolbar and assign the following macro

Sub myStuff()
Userform1.Show
Mail_Text_in_Body_3
End Sub

The userform code would dump to the spreadsheet. Do you need that as well?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Tim" <[email protected]> wrote in message
news:[email protected]...
> The sheet "Employee List" is a hidden sheet in the workbook. I was hoping
to
> have a button on my main worksheet (Sheet1) that would call the form up,
have
> the user fillin the text fields and then have one button on the form that
> would transfer the data to the hidden sheet (Employee List), then run the
> email code.
> I could add a second button for the email but I was hoping to do it all in
> one.
>
>
>
> "Bob Phillips" wrote:
>
> > Tim,
> >
> > You could simply add a button to the form, load the worksheet from
there,
> > and then run Ron's macro.
> >
> > I just knocked up a simple example that worked fine.
> >
> > Is that what you are asking or am I missing something?
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Tim" <[email protected]> wrote in message
> > news:[email protected]...
> > > N3 is what the completed statement is which goes into the body of the
> > email.
> > > N7 gets incident number from a cell on another sheet and that data is
used
> > to
> > > build the re: line in the email. I will be using the data
from(txtPerson)
> > to
> > > fill in "Statement for" (&).
> > >
> > > Thanks "moi"
> > >
> > > "moi" wrote:
> > >
> > > > Wait a sec.
> > > >
> > > > I have a UserForm now (sample is here:
> > > > http://www.geocities.com/smplprgrsrc/XLSMailForm.zip), with 6
EditBoxes:
> > > > txtDate, txtName, txtPerson, txtTime, txtRelease and txtNextDate.
> > > > There's a button on Sheet1 which launches the form. After a click on
> > button
> > > > cmdEmailtodata, the form values are being sent to sheet "Employee
List".
> > > > So I send
> > > > txtDate to G45
> > > > txtName to H45
> > > > txtPerson to I45
> > > > txtTime to J45
> > > > txtRelease to K45
> > > > and txtNextDate to L45
> > > >
> > > > Tada. So far so good, althoug I think I'm already going in the wrong
> > > > direction here.
> > > >
> > > > However, in Ron's api-call, I just don't get the Subject line;
> > > > Subj = "Statement for " & " for Incident " & Sheets("Employee
> > > > List").Range("N7").Value
> > > >
> > > >
> > > > Statement for & WHO/WHAT & for incident...
> > > >
> > > > And then: What's in cell N7? Also: What's in cell N3?
> > > >
> > > > Or should I walk through G45-L45, construct the mail message from
all
> > cells
> > > > and then send it?
> > > >
> > > >
> > > >
> > > > "Tim" <[email protected]> schreef in bericht
> > > > news:[email protected]...
> > > > > First I would like to qualify; I'm not a programmer, far from it.
I'm
> > a
> > > > > police officer that is trying to take my agency from the pen and
paper
> > era
> > > > > to
> > > > > this 'new' era called the 'computer age'. I have been working on
this
> > > > > project
> > > > > (on my own time) for about two years. Everything that I learned so
far
> > has
> > > > > been from this discussion group or a site that I was directed to.
I
> > would
> > > > > like to say thanks to all the people that helped/directed me,
written
> > > > > code,
> > > > > etc., without your help I would have given up a long time ago.
> > > > >
> > > > > OK enough sucking up. Here is my next project. I would like to try
and
> > add
> > > > > some Userforms to the program. I've done a little homework and so
far
> > I've
> > > > > been able to build the form, now I need it to do what I want it
to.
> > > > >
> > > > > I plan to have a button on 'Sheet 1' that calls the form up. The
form
> > has
> > > > > six text boxes on it (txtDate, txtName, txtPerson, txtTime,
> > txtRelease,
> > > > > TxtNextdate). I would like the data from those text fields to go
to
> > > > > specific
> > > > > cells on a sheet named 'Employee List'. i.e. txtDate to G45. I
would
> > then
> > > > > like it to run the following email code (that I found on Ron
Debruin's
> > > > > site,
> > > > > Thanks Ron). I would like person to be able to fill in the boxes
and
> > then
> > > > > click on one button on the form (cmdEmailtodata) and have it do
both
> > > > > processes.
> > > > >
> > > > > Sorry for the long post.
> > > > > Thanks
> > > > >
> > > > > Private Declare Function ShellExecute Lib "Shell32.dll" _
> > > > > Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As
> > String, _
> > > > > ByVal lpFile As String, ByVal lpParameters As String, ByVal
> > lpDirectory As
> > > > > String, _
> > > > > ByVal nShowCmd As Long) As Long
> > > > >
> > ________________________________________________________________________
> > > > > Sub Mail_Text_in_Body_3()
> > > > > 'Creates statement for person and emails it to data entry
> > > > > Dim msg As String, URL As String
> > > > > Dim Recipient As String, Subj As String
> > > > > Dim cell As Range
> > > > > Recipient = "[email protected]"
> > > > >
> > > > > Subj = "Statement for " & " for Incident " & Sheets("Employee
> > > > > List").Range("N7").Value
> > > > >
> > > > > msg = "Statement of " & vbNewLine & vbNewLine
> > > > > For Each cell In Sheets("Employee List").Range("N3")
> > > > > msg = msg & vbNewLine & cell
> > > > > Next cell
> > > > > msg = WorksheetFunction.Substitute(msg, vbNewLine, "%0D%0A")
> > > > >
> > > > > msg = WorksheetFunction.Substitute(msg, vbLf, "%0D%0A")
> > > > > URL = "mailto:" & Recipient & "&subject=" & Subj & "&body=" &
msg
> > > > > ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString,
> > > > > vbNormalFocus
> > > > > Application.Wait (Now + TimeValue("0:00:03"))
> > > > > Application.SendKeys "%s"
> > > > > End Sub
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

[Tim]

Thanks for the help. This has me heading in the right direction, just need
some time to work it into the program. But I better get back to the "HoneyDo"
list, She thinks I'm nuts doing this on my own time... probably right.

I will let you know how I make out

Thanks again to both of you.

"moi" wrote:

> Ron's macro is working, but only when I replace the ampersand with a
> question mark in the subject. Else I get the full msg-line in the address
> bar.
>
> Same link as above Tim, it's updated.
>
>
> "Bob Phillips" <[email protected]> schreef in bericht
> news:[email protected]...
> > Tim,
> >
> > You could simply add a button to the form, load the worksheet from there,
> > and then run Ron's macro.
> >
> > I just knocked up a simple example that worked fine.
> >
> > Is that what you are asking or am I missing something?
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Tim" <[email protected]> wrote in message
> > news:[email protected]...
> >> N3 is what the completed statement is which goes into the body of the
> > email.
> >> N7 gets incident number from a cell on another sheet and that data is
> >> used
> > to
> >> build the re: line in the email. I will be using the data from(txtPerson)
> > to
> >> fill in "Statement for" (&).
> >>
> >> Thanks "moi"
> >>
> >> "moi" wrote:
> >>
> >> > Wait a sec.
> >> >
> >> > I have a UserForm now (sample is here:
> >> > http://www.geocities.com/smplprgrsrc/XLSMailForm.zip), with 6
> >> > EditBoxes:
> >> > txtDate, txtName, txtPerson, txtTime, txtRelease and txtNextDate.
> >> > There's a button on Sheet1 which launches the form. After a click on
> > button
> >> > cmdEmailtodata, the form values are being sent to sheet "Employee
> >> > List".
> >> > So I send
> >> > txtDate to G45
> >> > txtName to H45
> >> > txtPerson to I45
> >> > txtTime to J45
> >> > txtRelease to K45
> >> > and txtNextDate to L45
> >> >
> >> > Tada. So far so good, althoug I think I'm already going in the wrong
> >> > direction here.
> >> >
> >> > However, in Ron's api-call, I just don't get the Subject line;
> >> > Subj = "Statement for " & " for Incident " & Sheets("Employee
> >> > List").Range("N7").Value
> >> >
> >> >
> >> > Statement for & WHO/WHAT & for incident...
> >> >
> >> > And then: What's in cell N7? Also: What's in cell N3?
> >> >
> >> > Or should I walk through G45-L45, construct the mail message from all
> > cells
> >> > and then send it?
> >> >
> >> >
> >> >
> >> > "Tim" <[email protected]> schreef in bericht
> >> > news:[email protected]...
> >> > > First I would like to qualify; I'm not a programmer, far from it. I'm
> > a
> >> > > police officer that is trying to take my agency from the pen and
> >> > > paper
> > era
> >> > > to
> >> > > this 'new' era called the 'computer age'. I have been working on this
> >> > > project
> >> > > (on my own time) for about two years. Everything that I learned so
> >> > > far
> > has
> >> > > been from this discussion group or a site that I was directed to. I
> > would
> >> > > like to say thanks to all the people that helped/directed me, written
> >> > > code,
> >> > > etc., without your help I would have given up a long time ago.
> >> > >
> >> > > OK enough sucking up. Here is my next project. I would like to try
> >> > > and
> > add
> >> > > some Userforms to the program. I've done a little homework and so far
> > I've
> >> > > been able to build the form, now I need it to do what I want it to.
> >> > >
> >> > > I plan to have a button on 'Sheet 1' that calls the form up. The form
> > has
> >> > > six text boxes on it (txtDate, txtName, txtPerson, txtTime,
> > txtRelease,
> >> > > TxtNextdate). I would like the data from those text fields to go to
> >> > > specific
> >> > > cells on a sheet named 'Employee List'. i.e. txtDate to G45. I would
> > then
> >> > > like it to run the following email code (that I found on Ron
> >> > > Debruin's
> >> > > site,
> >> > > Thanks Ron). I would like person to be able to fill in the boxes and
> > then
> >> > > click on one button on the form (cmdEmailtodata) and have it do both
> >> > > processes.
> >> > >
> >> > > Sorry for the long post.
> >> > > Thanks
> >> > >
> >> > > Private Declare Function ShellExecute Lib "Shell32.dll" _
> >> > > Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As
> > String, _
> >> > > ByVal lpFile As String, ByVal lpParameters As String, ByVal
> > lpDirectory As
> >> > > String, _
> >> > > ByVal nShowCmd As Long) As Long
> >> > >
> > ________________________________________________________________________
> >> > > Sub Mail_Text_in_Body_3()
> >> > > 'Creates statement for person and emails it to data entry
> >> > > Dim msg As String, URL As String
> >> > > Dim Recipient As String, Subj As String
> >> > > Dim cell As Range
> >> > > Recipient = "[email protected]"
> >> > >
> >> > > Subj = "Statement for " & " for Incident " & Sheets("Employee
> >> > > List").Range("N7").Value
> >> > >
> >> > > msg = "Statement of " & vbNewLine & vbNewLine
> >> > > For Each cell In Sheets("Employee List").Range("N3")
> >> > > msg = msg & vbNewLine & cell
> >> > > Next cell
> >> > > msg = WorksheetFunction.Substitute(msg, vbNewLine, "%0D%0A")
> >> > >
> >> > > msg = WorksheetFunction.Substitute(msg, vbLf, "%0D%0A")
> >> > > URL = "mailto:" & Recipient & "&subject=" & Subj & "&body=" & msg
> >> > > ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString,
> >> > > vbNormalFocus
> >> > > Application.Wait (Now + TimeValue("0:00:03"))
> >> > > Application.SendKeys "%s"
> >> > > End Sub
> >> > >
> >> >
> >> >
> >> >
> >
> >
>
>
>