Excel 2003 VBA question
 

Hi, sorry if I appear totally lame but I know just enough VBA to be
highly dangerous. I am trying to make a small dialog that will do many
things, including email the active workbook to user's choice of email
addresses.

I have checkboxes by each name which will correspond to a specific email
address, plus one with a text box that will allow a user to supply an
email address, like this:

cbCustomer
|-tbCustomerEmail
cbSales
cbAdmin
cbMfg
cbSupport
cbMgmt

What I want to do is first check to see if all of the checkboxes are not
selected, and throw up a msgbox, then build an array based out of the
selected checkboxes and add them into this line of code:

ActiveWorkbook.SendMail Recipients:=arrayEmail, Subject:="Please
process this order immediately"
Application.Dialogs(xlDialogSendMail).Show

My questions:
1 - How can I build the arrayEmail depending on which checkboxes are
selected?
2 - Is that the best way to email the active workbook? It pops up
the dialog about "Something is trying to send email from this app - OK?"
which I would rather not have.

Best regards,
Me

Excel 2003 VBA question
 

Well, looking at the events available to checkboxes...there isn't much that
intrinsically gives you much help. Un-needed mouse and keyboard events and
the "default" event "Change". This event only tells you that the value
changed...so you still have to check the value to determine the current
value....not very useful to bother checking because you do not know if the
user is even done making changes yet.

I made something similar to this to allow batch processing of multiple
reports. I just have a "Done" and "Cancel" button and do not bother checking
the state of checkboxes until the user clicks "Ok". I just use a series of
If statements, here are the first two from my program:

Private Sub ButtonOK_Click()

If (CheckBoxPSOS.Value = True) Then
Call formatPSOS(TextBoxPSOS.Value, useDefault)
End If
If (CheckBoxInventory.Value = True) Then
Call formatDailyInv(TextBoxInventory.Value, useDefault)
End If

....

In your case you have 6 possible recipients. Make a temp array of 6
elements. Add the beginning of the Click() event initialize a counter
(recipientCount) to zero. Then check each of your checkboxes and add email
addresses as needed and increment the recipientCount (also your array index)
before examining the next checkbox. When you are done, check your counter,
if still 0, use the built-in MsgBox(strMsg as string) function to popup a
warning message. Otherwise create a new array of the correct size (using
Redim) and copy your email list over to the new object.

The warning message "Something is trying to send email from this app - OK?"
is to allow you to stop a potentially malicious macro from sending email. I
am not sure if this can be turned off, but if so, it would probably be by
setting Macro Security to Low (Tools-Macros-Macro Security). You may also
be able to set yourself up as a trusted publisher (I am not familiar with
that process).


If the sendMail function will work with a recipient string like
; ; " (i.e. semi-colon
or comma separated list) you could avoid arrays and just append addresses to
your string.

"Hogan's Goat" wrote:

Hi, sorry if I appear totally lame but I know just enough VBA to be
highly dangerous. I am trying to make a small dialog that will do many
things, including email the active workbook to user's choice of email
addresses.

I have checkboxes by each name which will correspond to a specific email
address, plus one with a text box that will allow a user to supply an
email address, like this:

cbCustomer
|-tbCustomerEmail
cbSales
cbAdmin
cbMfg
cbSupport
cbMgmt

What I want to do is first check to see if all of the checkboxes are not
selected, and throw up a msgbox, then build an array based out of the
selected checkboxes and add them into this line of code:

ActiveWorkbook.SendMail Recipients:=arrayEmail, Subject:="Please
process this order immediately"
Application.Dialogs(xlDialogSendMail).Show

My questions:
1 - How can I build the arrayEmail depending on which checkboxes are
selected?
2 - Is that the best way to email the active workbook? It pops up
the dialog about "Something is trying to send email from this app - OK?"
which I would rather not have.

Best regards,
Me

Excel 2003 VBA question
 

OK, let me simplify my question a little:

I have five checkboxes within a frame on a form I am building. They are
linked to a button. If you click the button when none of the checkboxes
are checked, I want to throw up a MsgBox sanying, "Woopsy! Check a box
first."

Must I build an array of the checkboxes and, when clicking on the button,
check for the .Enabled value of each? How would you do it?

Thanks!

Here's a cheerful little earful from ?B?QmlsbCBMaW5rZXI=?=:

Well, looking at the events available to checkboxes...there isn't much
that intrinsically gives you much help. Un-needed mouse and keyboard
events and the "default" event "Change". This event only tells you
that the value changed...so you still have to check the value to
determine the current value....not very useful to bother checking
because you do not know if the user is even done making changes yet.

I made something similar to this to allow batch processing of multiple
reports. I just have a "Done" and "Cancel" button and do not bother
checking the state of checkboxes until the user clicks "Ok". I just
use a series of If statements, here are the first two from my program:

Private Sub ButtonOK_Click()

If (CheckBoxPSOS.Value = True) Then
Call formatPSOS(TextBoxPSOS.Value, useDefault)
End If
If (CheckBoxInventory.Value = True) Then
Call formatDailyInv(TextBoxInventory.Value, useDefault)
End If

...

In your case you have 6 possible recipients. Make a temp array of 6
elements. Add the beginning of the Click() event initialize a counter
(recipientCount) to zero. Then check each of your checkboxes and add
email addresses as needed and increment the recipientCount (also your
array index) before examining the next checkbox. When you are done,
check your counter, if still 0, use the built-in MsgBox(strMsg as
string) function to popup a warning message. Otherwise create a new
array of the correct size (using Redim) and copy your email list over
to the new object.

The warning message "Something is trying to send email from this app -
OK?" is to allow you to stop a potentially malicious macro from
sending email. I am not sure if this can be turned off, but if so, it
would probably be by setting Macro Security to Low
(Tools-Macros-Macro Security). You may also be able to set yourself
up as a trusted publisher (I am not familiar with that process).


If the sendMail function will work with a recipient string like
; ; " (i.e.
semi-colon or comma separated list) you could avoid arrays and just
append addresses to your string.

"Hogan's Goat" wrote:

Hi, sorry if I appear totally lame but I know just enough VBA to be
highly dangerous. I am trying to make a small dialog that will do
many things, including email the active workbook to user's choice of
email addresses.

I have checkboxes by each name which will correspond to a specific
email address, plus one with a text box that will allow a user to
supply an email address, like this:

cbCustomer
|-tbCustomerEmail
cbSales
cbAdmin
cbMfg
cbSupport
cbMgmt

What I want to do is first check to see if all of the checkboxes are
not selected, and throw up a msgbox, then build an array based out of
the selected checkboxes and add them into this line of code:

ActiveWorkbook.SendMail Recipients:=arrayEmail,
Subject:="Please
process this order immediately"
Application.Dialogs(xlDialogSendMail).Show

My questions:
1 - How can I build the arrayEmail depending on which
checkboxes are
selected?
2 - Is that the best way to email the active workbook? It
pops up
the dialog about "Something is trying to send email from this app -
OK?" which I would rather not have.

Best regards,
Me