Excel to mail by choosing from check box control

Hi,

I have a macro using Ron de bruin VBA codes.

Work is applications are received and rejected for some reasons. this rejection intimation should be sent to applicants why it was rejected.

In my excel file i have the rejection reasons in check boxes, if choose one or more check boxes and run the macro.
it should send mail with the reasons one by one (if multiple check boxes are being chose)

Can anyone help me to achieve this. Attached sample excel. Thanks.

seems you are missing data from your sheet
like what is sheet "APP"

the important bit is being the body of email
i created macro called "test" in module 3
if you run it it will produce a msgbox with corresponding email body text

i dont like using form controls...for some reason i could not rename the 2nd column of check box names
so i had to amend the code to work around it

managed to fix the check box names to be sequential again via ALT + F10
so no need to have split way of determining checkbox equivalent response

also fixed part of the code so it generates in proper order

Please Login or Register  to view this content.

Please Login or Register  to view this content.

applying to your outmail would look something like this

Hi Hum,

The above outlook code gives the reason, but its not working for multiple reasons. Please help.

I have tried a part of codes given below and its showing compile error: can't assign to array.


In module 3

Please Login or Register  to view this content.

I know i have wrong but i thought this for i statement will help in this.....

Please help me in this codes to get the rejection reason to be displayed in the body of the mail. and multiple reasons if multiple selection is made.

or give me a solution in your way...

sorry attached wrong file in previous post

your sample file does not have worksheet Sheets("App")

now if you just took the code and plonked it into your file it wont work
i renamed all your checkboxes in your sample file in order for the code to work

Hum,

Thanks a lot...

works fine...

i have an question, if i suppose add some more check boxes and reason.. in which part of this below code i need to make changes.

Please Login or Register  to view this content.

Also please tell, is this code indicates the column N where i put the reasons. if i need to move that reasons to some other column... how i need to tell VBA take the reasons from that new column where i moved those reasons.

if you are going to have more checkboxes

This is the important line to focus on

Please Login or Register  to view this content.

change Right to focus on 2 characters instead of 1
rest of the code can be left as is

make sure you assign the checkbox name to match your list in column N
as it stands
check box 2 = row 5
because (Right(shp.Name, 2) + 3, 14)
shp.name 2 gives 2 then plus 3 = 5
Column n = 14
so cells(5,14) = reason for checkbox 2

etc

so make sure your check box Name is correct
as this is very important for the code to work
use ALT+f10 to see list of "shapes" on your worksheet

Hi Hum,

Thanks...

Please Login or Register  to view this content.

in above code how this (shp.name,2) works... sorry i cant get you in this below you said

"change Right to focus on 2 characters instead of 1"

in my case i have 8 checkboxes, if i incerase it to 20 checkboxes with 20 reasons , what sould i put there in (shp.name, ___ )


My doubt is as you said

check box 2 = row 5
because (Right(shp.Name, 2) + 3, 14)
shp.name 2 gives 2 then plus 3 = 5
Column n = 14
so cells(5,14) = reason for checkbox 2

for checkbox 3 it gives (6,14)
for checkbox 4 it gives (7,14)

thats correct.... but in the code there is only one line code is there to tell VBA that for shape 2 take reson from column row (5,14)
but how it is taking for other shapes... is there any loop here...

how i can find this and use for more checkboxes suppose i need for 20 checkboxes.


Please clarify thanks for your time.

using the right formula
http://www.techonthenet.com/excel/formulas/right.php

Formula:

Please Login or Register  to view this content.

RIGHT (Text,2) = 2 characters from the right
as the original code had 1

as your naming check box #number
there is a space after check box you can have a blank space and it wont impede on the expected result
ie
check box 2 = " 2" = 2
check box 20 = "20" = 20
if you have over 99...then you need to redesign your code but cross that bridge when you get there

Regarding your question on the loop

Please Login or Register  to view this content.

this codes runs thru every single shape in your sheet

Please Login or Register  to view this content.

then checks if shape is a control

Please Login or Register  to view this content.

Then checks if type of control is Checkbox

Please Login or Register  to view this content.

then checks if checkbox is enabled

that is why even if you have +100 check boxes
this code will go thru all of them to see if they are enabled

Hi Hum,

Wow.... your answer is clear as crystal.... Thanks a lot...

I take an advantage on your kindness.... i have last 2 questions.

1. VBA is always reading the last check box as first.... for an example: if i choose check box 2 and check box 20, VBA is populating the mail with the reason of check box 20 as first and check box 2 as next... always am getting the last check box as first in descending order. Can it be fixed.... as from first to last,

2. Can you please give code, that reset all check boxes.. when i run the code it should deselect all check boxes.


Thanks for your time and consideration.

Please Login or Register  to view this content.

sorry which version are you using?
this version should be first to last
as it builds the message with body first then appends subsequent email body

for you other request i will look at it tomorrow

Cheers
Hum

Code to clear out all checkboxes

Please Login or Register  to view this content.