RE: Excel 2003 VBA question
From: Bill Linker (BillLinker_at_discussions.microsoft.com)
Date: 02/04/05
- Next message: Bill Linker: "RE: text box"
- Previous message: Don Guillett: "Re: Help required putting borders around cells."
- In reply to: Hogan's Goat: "Excel 2003 VBA question"
- Next in thread: Hogan's Goat: "RE: Excel 2003 VBA question"
- Reply: Hogan's Goat: "RE: Excel 2003 VBA question"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 3 Feb 2005 16:29:03 -0800
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
"recip1@domainX.com; recip2@domainY.com; recip3@domainZ.com" (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
>
>
- Next message: Bill Linker: "RE: text box"
- Previous message: Don Guillett: "Re: Help required putting borders around cells."
- In reply to: Hogan's Goat: "Excel 2003 VBA question"
- Next in thread: Hogan's Goat: "RE: Excel 2003 VBA question"
- Reply: Hogan's Goat: "RE: Excel 2003 VBA question"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
