Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform help needed
First I would like to qualify; Im not a programmer, far from it. Im 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. Ive done a little homework and so far Ive 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 Debruins 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 = " 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform help needed
Wait a sec.
I have a UserForm now (sample is he 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" schreef in bericht ... 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 = " 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform help needed
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 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 he 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" schreef in bericht ... 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 = " 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform help needed
hang on...
"Tim" schreef in bericht ... 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 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 he 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" schreef in bericht ... 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 = " 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform help needed
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" wrote in message ... 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 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 he 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" schreef in bericht ... 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 = " 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform help needed
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" wrote in message ... 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 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 he 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" schreef in bericht ... 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 = " 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform help needed
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" schreef in bericht ... 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" wrote in message ... 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 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 he 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" schreef in bericht ... 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 = " 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform help needed
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" wrote in message ... 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" wrote in message ... 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 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 he 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" schreef in bericht ... 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 = " 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform help needed
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" schreef in bericht ... 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" wrote in message ... 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 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 he 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" schreef in bericht ... 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 = " 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
UserForm code help needed. | Excel Programming | |||
UserForm code help needed. | Excel Programming | |||
UserForm code help needed. | Excel Programming | |||
UserForm code help needed. | Excel Programming | |||
A pointer or two needed using userform | Excel Programming |