Excel 2003 VBA question

[Hogan's Goat]

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

 

[Guest]

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
"(e-mail address removed); (e-mail address removed); (e-mail address removed)" (i.e. semi-colon
or comma separated list) you could avoid arrays and just append addresses to
your string.

 

[Hogan's Goat]

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 =?Utf-8?B?QmlsbCBMaW5rZXI=?=: