| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send workbook automatically as attachment based on input from user
I have looked through several posts and on the ron bruin sites trying to find
the answer to my question. I currently have a workbook in which a user submits a document to us by clicking a command button. All is working well here because I am using an array with all the email addresses. What I want to do to try & minimize the amount of people and the amount of emails people are receiving is to open a userform and have checkboxes. Each checkbox would be tied to an email address or group of email addresses. Based on one or more of these being true, when the users would click on the command button it would automatically send an email with the workbook attached as I am doing now. I am not sure if the code I am currently using can be modified to do this or if I have to start from scratch again. Below is the current code I am using: Private Sub CommandButton1_Click() Dim Msg, Style, Title, Response Msg = "Request Denied" Style = vbYesNo + vbCritical + vbSubReq Title = "Request Denied" Response = MsgBox(Msg, Style, Title) If Response = vbYes Then ActiveWorkbook.SendMail "), ("Request Denied by") MsgBox ("The Denied Notification has been sent") Thank you in advance for your help. -- Sony |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Send workbook automatically as attachment based on input from user
You can loop through your checkboxes and check the value
If the value = true then add the mail address you have in a worksheet cell ? to the array See the example how I fill a array here with addresses in a range http://www.rondebruin.nl/mail/tips1.htm "Send only to the visible Addresses in column C" If you need help to adapt it post back and tell where you have the addresses -- Regards Ron de Bruin http://www.rondebruin.nl "Sony" wrote in message ... I have looked through several posts and on the ron bruin sites trying to find the answer to my question. I currently have a workbook in which a user submits a document to us by clicking a command button. All is working well here because I am using an array with all the email addresses. What I want to do to try & minimize the amount of people and the amount of emails people are receiving is to open a userform and have checkboxes. Each checkbox would be tied to an email address or group of email addresses. Based on one or more of these being true, when the users would click on the command button it would automatically send an email with the workbook attached as I am doing now. I am not sure if the code I am currently using can be modified to do this or if I have to start from scratch again. Below is the current code I am using: Private Sub CommandButton1_Click() Dim Msg, Style, Title, Response Msg = "Request Denied" Style = vbYesNo + vbCritical + vbSubReq Title = "Request Denied" Response = MsgBox(Msg, Style, Title) If Response = vbYes Then ActiveWorkbook.SendMail "), ("Request Denied by") MsgBox ("The Denied Notification has been sent") Thank you in advance for your help. -- Sony |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Send workbook automatically as attachment based on input from
I have the email addresses being populated on a sheet called "Hidden Tab" in
Column "A" -- Sony "Ron de Bruin" wrote: You can loop through your checkboxes and check the value If the value = true then add the mail address you have in a worksheet cell ? to the array See the example how I fill a array here with addresses in a range http://www.rondebruin.nl/mail/tips1.htm "Send only to the visible Addresses in column C" If you need help to adapt it post back and tell where you have the addresses -- Regards Ron de Bruin http://www.rondebruin.nl "Sony" wrote in message ... I have looked through several posts and on the ron bruin sites trying to find the answer to my question. I currently have a workbook in which a user submits a document to us by clicking a command button. All is working well here because I am using an array with all the email addresses. What I want to do to try & minimize the amount of people and the amount of emails people are receiving is to open a userform and have checkboxes. Each checkbox would be tied to an email address or group of email addresses. Based on one or more of these being true, when the users would click on the command button it would automatically send an email with the workbook attached as I am doing now. I am not sure if the code I am currently using can be modified to do this or if I have to start from scratch again. Below is the current code I am using: Private Sub CommandButton1_Click() Dim Msg, Style, Title, Response Msg = "Request Denied" Style = vbYesNo + vbCritical + vbSubReq Title = "Request Denied" Response = MsgBox(Msg, Style, Title) If Response = vbYes Then ActiveWorkbook.SendMail "), ("Request Denied by") MsgBox ("The Denied Notification has been sent") Thank you in advance for your help. -- Sony |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Send workbook automatically as attachment based on input from
I go to London now so I try to answer it Sunday when I am back
-- Regards Ron de Bruin http://www.rondebruin.nl "Sony" wrote in message ... I have the email addresses being populated on a sheet called "Hidden Tab" in Column "A" -- Sony "Ron de Bruin" wrote: You can loop through your checkboxes and check the value If the value = true then add the mail address you have in a worksheet cell ? to the array See the example how I fill a array here with addresses in a range http://www.rondebruin.nl/mail/tips1.htm "Send only to the visible Addresses in column C" If you need help to adapt it post back and tell where you have the addresses -- Regards Ron de Bruin http://www.rondebruin.nl "Sony" wrote in message ... I have looked through several posts and on the ron bruin sites trying to find the answer to my question. I currently have a workbook in which a user submits a document to us by clicking a command button. All is working well here because I am using an array with all the email addresses. What I want to do to try & minimize the amount of people and the amount of emails people are receiving is to open a userform and have checkboxes. Each checkbox would be tied to an email address or group of email addresses. Based on one or more of these being true, when the users would click on the command button it would automatically send an email with the workbook attached as I am doing now. I am not sure if the code I am currently using can be modified to do this or if I have to start from scratch again. Below is the current code I am using: Private Sub CommandButton1_Click() Dim Msg, Style, Title, Response Msg = "Request Denied" Style = vbYesNo + vbCritical + vbSubReq Title = "Request Denied" Response = MsgBox(Msg, Style, Title) If Response = vbYes Then ActiveWorkbook.SendMail "), ("Request Denied by") MsgBox ("The Denied Notification has been sent") Thank you in advance for your help. -- Sony |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Send workbook automatically as attachment based on input from
Hi Sony
Test this Userform with 3 checkboxes named checkbox1, checkbox2 and checkbox3 and one button. In Sheets("Hidden Tab") in A1,A2 and A3 E-mail addressses. a1 = address for checkbox1 a2 = address for checkbox2 a3 = address for checkbox3 This is the button code Private Sub CommandButton1_Click() Dim Arr() As String Dim N As Integer N = 0 For I = 1 To 3 If Me.Controls("checkbox" & I).Value = True Then N = N + 1 ReDim Preserve Arr(1 To N) Arr(N) = Sheets("Hidden Tab").Range("A" & I).Value End If Next If N 0 Then ActiveWorkbook.SendMail Arr, "This is the Subject line" End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... I go to London now so I try to answer it Sunday when I am back -- Regards Ron de Bruin http://www.rondebruin.nl "Sony" wrote in message ... I have the email addresses being populated on a sheet called "Hidden Tab" in Column "A" -- Sony "Ron de Bruin" wrote: You can loop through your checkboxes and check the value If the value = true then add the mail address you have in a worksheet cell ? to the array See the example how I fill a array here with addresses in a range http://www.rondebruin.nl/mail/tips1.htm "Send only to the visible Addresses in column C" If you need help to adapt it post back and tell where you have the addresses -- Regards Ron de Bruin http://www.rondebruin.nl "Sony" wrote in message ... I have looked through several posts and on the ron bruin sites trying to find the answer to my question. I currently have a workbook in which a user submits a document to us by clicking a command button. All is working well here because I am using an array with all the email addresses. What I want to do to try & minimize the amount of people and the amount of emails people are receiving is to open a userform and have checkboxes. Each checkbox would be tied to an email address or group of email addresses. Based on one or more of these being true, when the users would click on the command button it would automatically send an email with the workbook attached as I am doing now. I am not sure if the code I am currently using can be modified to do this or if I have to start from scratch again. Below is the current code I am using: Private Sub CommandButton1_Click() Dim Msg, Style, Title, Response Msg = "Request Denied" Style = vbYesNo + vbCritical + vbSubReq Title = "Request Denied" Response = MsgBox(Msg, Style, Title) If Response = vbYes Then ActiveWorkbook.SendMail "), ("Request Denied by") MsgBox ("The Denied Notification has been sent") Thank you in advance for your help. -- Sony |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Send workbook automatically as attachment based on input from
Great thank you Ron. This was very helpful.
-- Sony "Ron de Bruin" wrote: Hi Sony Test this Userform with 3 checkboxes named checkbox1, checkbox2 and checkbox3 and one button. In Sheets("Hidden Tab") in A1,A2 and A3 E-mail addressses. a1 = address for checkbox1 a2 = address for checkbox2 a3 = address for checkbox3 This is the button code Private Sub CommandButton1_Click() Dim Arr() As String Dim N As Integer N = 0 For I = 1 To 3 If Me.Controls("checkbox" & I).Value = True Then N = N + 1 ReDim Preserve Arr(1 To N) Arr(N) = Sheets("Hidden Tab").Range("A" & I).Value End If Next If N 0 Then ActiveWorkbook.SendMail Arr, "This is the Subject line" End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... I go to London now so I try to answer it Sunday when I am back -- Regards Ron de Bruin http://www.rondebruin.nl "Sony" wrote in message ... I have the email addresses being populated on a sheet called "Hidden Tab" in Column "A" -- Sony "Ron de Bruin" wrote: You can loop through your checkboxes and check the value If the value = true then add the mail address you have in a worksheet cell ? to the array See the example how I fill a array here with addresses in a range http://www.rondebruin.nl/mail/tips1.htm "Send only to the visible Addresses in column C" If you need help to adapt it post back and tell where you have the addresses -- Regards Ron de Bruin http://www.rondebruin.nl "Sony" wrote in message ... I have looked through several posts and on the ron bruin sites trying to find the answer to my question. I currently have a workbook in which a user submits a document to us by clicking a command button. All is working well here because I am using an array with all the email addresses. What I want to do to try & minimize the amount of people and the amount of emails people are receiving is to open a userform and have checkboxes. Each checkbox would be tied to an email address or group of email addresses. Based on one or more of these being true, when the users would click on the command button it would automatically send an email with the workbook attached as I am doing now. I am not sure if the code I am currently using can be modified to do this or if I have to start from scratch again. Below is the current code I am using: Private Sub CommandButton1_Click() Dim Msg, Style, Title, Response Msg = "Request Denied" Style = vbYesNo + vbCritical + vbSubReq Title = "Request Denied" Response = MsgBox(Msg, Style, Title) If Response = vbYes Then ActiveWorkbook.SendMail "), ("Request Denied by") MsgBox ("The Denied Notification has been sent") Thank you in advance for your help. -- Sony |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Send active workbook as attachment via hyperlink | Excel Worksheet Functions | |||
| Send keys with user input | Excel Worksheet Functions | |||
| Copy cell data from workbook based on user input | Excel Programming | |||
| Send active workbook as attachment | Excel Programming | |||
| CODE to select range based on User Input or Value of Input Field | Excel Programming | |||
Linear Mode
