Send workbook automatically as attachment based on input from user

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 Array("(e-mail address removed)"), ("Request Denied by")
MsgBox ("The Denied Notification has been sent")

Thank you in advance for your help.
 
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
 
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
 
Ron,

One more question for you, how do I group more than one email address to a
checkbox. I have tried entering it in the cell as you would in outlook
(e-mail address removed); (e-mail address removed) but it is not working. What am I missing
here?

Thanks again!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top