Email automatically when a form is closed

[Guest]

I have a Combobox in my form named Team Leader. The combobox has 3 columns,
1= ID, 2=Name and 3 = Email Address. The combo box actually show column 2
only. I would like to have an email sent to the email address from column 3
when the form is closed. I know I need to make this an event procedure from
the property box of the form. Problem is that I know nothing about vba and
have no clue where to go from here. I would also like to program in a few
set email addresses in addition to the one it pulls from the combo box.

Any help is very much appreciated. Thank you.

 

[Guest]

Check SendObject in Help. You didn't mention where the additional recipients
would come from, but in general it would look something like this:
DoCmd.SendObject acSendNoObject, , , Me![ComboBox].Column(2), "(e-mail address removed)
; (e-mail address removed)", , "Message Subject", "Message Body"

A couple of notes before I get to how you would add this code: Anything
identified in Help as the default choice need not be included in the code.
acSendNoObject could be left out, but the comma needs to remain. Stop when
there is nothing more to add. Commas are placeholders between options. In
the example above there are two commas before "Message Subject". Bcc (blind
copy) recipients of the e-mail would be inserted between the commas, but
there are none. Without the second comma "Message Subject" would appear in
the Bcc line on the e-mail. Note that combo box columns are numbered 0, 1,
2, etc., so Column(2) is actually the third column.
Important: There are no line breaks in the code. Enter it all on a single
line.
Regarding the code, I don't think it is possible to run the code from the
form's On Close event. However, if you use a command button to close the
form you can add the code before the instruction to close the form. To do
that, in form design view right click the command button, click Properties,
click the Event tab, click On Click, and click the three dots. If you are
creating a new command button, continue by clicking Code Builder, OK. In
either case you will see a code window with text. If you see On Error GoTo
.... at the top click below that and start typing the code. If it is a new
command button start typing where the cursor is blinking. A message may pop
up as you type, attempting to guide you through the process.
There are a lot of options, so post back if you have more questions.

 

[Guest]

Thank you, this was very helpful. Just one problem, I'm getting an error
that I'm not sure how to fix.
This is what I have,

DoCmd.SendObject acSendNoObject, , , Me![Team Leader].Column(2),
"(e-mail address removed) "
, ,"Message Subject","Message Body"

I'm getting an error starting at the commas after the email address. The
way I read your reply this is where any Bcc would be and can be blank as long
as the commas are there. Also, to enter a subject line and body do I just
replace the "Message Subject" with my text?

thanks so much!

Check SendObject in Help. You didn't mention where the additional recipients
would come from, but in general it would look something like this:
DoCmd.SendObject acSendNoObject, , , Me![ComboBox].Column(2), "(e-mail address removed)
; (e-mail address removed)", , "Message Subject", "Message Body"

A couple of notes before I get to how you would add this code: Anything
identified in Help as the default choice need not be included in the code.
acSendNoObject could be left out, but the comma needs to remain. Stop when
there is nothing more to add. Commas are placeholders between options. In
the example above there are two commas before "Message Subject". Bcc (blind
copy) recipients of the e-mail would be inserted between the commas, but
there are none. Without the second comma "Message Subject" would appear in
the Bcc line on the e-mail. Note that combo box columns are numbered 0, 1,
2, etc., so Column(2) is actually the third column.
Important: There are no line breaks in the code. Enter it all on a single
line.
Regarding the code, I don't think it is possible to run the code from the
form's On Close event. However, if you use a command button to close the
form you can add the code before the instruction to close the form. To do
that, in form design view right click the command button, click Properties,
click the Event tab, click On Click, and click the three dots. If you are
creating a new command button, continue by clicking Code Builder, OK. In
either case you will see a code window with text. If you see On Error GoTo
... at the top click below that and start typing the code. If it is a new
command button start typing where the cursor is blinking. A message may pop
up as you type, attempting to guide you through the process.
There are a lot of options, so post back if you have more questions.

I have a Combobox in my form named Team Leader. The combobox has 3 columns,
1= ID, 2=Name and 3 = Email Address. The combo box actually show column 2
only. I would like to have an email sent to the email address from column 3
when the form is closed. I know I need to make this an event procedure from
the property box of the form. Problem is that I know nothing about vba and
have no clue where to go from here. I would also like to program in a few
set email addresses in addition to the one it pulls from the combo box.

Any help is very much appreciated. Thank you.

 

[Guest]

Put whatever text you want into Message Subject and Message Body. You are
correct about Bcc going into the blank space between the commas. [Team
Leader] in the code needs to be the actual name of your combo box, not of the
underlying field. Check your combo box properties to be sure Column Count is
at least three, and check the row source to be sure that the third column is
populated. I think you said you already have done that, but I'll mention it
anyhow. Both of these things can be checked on the property sheet for the
combo box. Column Count is at the Format tab, and Row Source at the Data
tab. To check the row source, click Row Source, then click the three dots.
You will get an error message if you attempt to run the code if there is no
e-mail address associated with the name in the combo box (i.e. Column(2) is
empty).
If you are still having difficulties, what error message are you receiving?
Note that approximately one and a half hours after posting this I will be
away until Tuesday.

Thank you, this was very helpful. Just one problem, I'm getting an error
that I'm not sure how to fix.
This is what I have,

DoCmd.SendObject acSendNoObject, , , Me![Team Leader].Column(2),
"(e-mail address removed) "
, ,"Message Subject","Message Body"

I'm getting an error starting at the commas after the email address. The
way I read your reply this is where any Bcc would be and can be blank as long
as the commas are there. Also, to enter a subject line and body do I just
replace the "Message Subject" with my text?

thanks so much!

Check SendObject in Help. You didn't mention where the additional recipients
would come from, but in general it would look something like this:
DoCmd.SendObject acSendNoObject, , , Me![ComboBox].Column(2), "(e-mail address removed)
; (e-mail address removed)", , "Message Subject", "Message Body"

A couple of notes before I get to how you would add this code: Anything
identified in Help as the default choice need not be included in the code.
acSendNoObject could be left out, but the comma needs to remain. Stop when
there is nothing more to add. Commas are placeholders between options. In
the example above there are two commas before "Message Subject". Bcc (blind
copy) recipients of the e-mail would be inserted between the commas, but
there are none. Without the second comma "Message Subject" would appear in
the Bcc line on the e-mail. Note that combo box columns are numbered 0, 1,
2, etc., so Column(2) is actually the third column.
Important: There are no line breaks in the code. Enter it all on a single
line.
Regarding the code, I don't think it is possible to run the code from the
form's On Close event. However, if you use a command button to close the
form you can add the code before the instruction to close the form. To do
that, in form design view right click the command button, click Properties,
click the Event tab, click On Click, and click the three dots. If you are
creating a new command button, continue by clicking Code Builder, OK. In
either case you will see a code window with text. If you see On Error GoTo
... at the top click below that and start typing the code. If it is a new
command button start typing where the cursor is blinking. A message may pop
up as you type, attempting to guide you through the process.
There are a lot of options, so post back if you have more questions.

I have a Combobox in my form named Team Leader. The combobox has 3 columns,
1= ID, 2=Name and 3 = Email Address. The combo box actually show column 2
only. I would like to have an email sent to the email address from column 3
when the form is closed. I know I need to make this an event procedure from
the property box of the form. Problem is that I know nothing about vba and
have no clue where to go from here. I would also like to program in a few
set email addresses in addition to the one it pulls from the combo box.

Any help is very much appreciated. Thank you.

 

[Guest]

Complie error: syntax error and it goes to the code and highlights in blue
the ,,"Message Subject","Message Body"

Yes I have 3 columns and the email addresses are in the last column.

thanks for your help and have a great New Year if I missed you.

Put whatever text you want into Message Subject and Message Body. You are
correct about Bcc going into the blank space between the commas. [Team
Leader] in the code needs to be the actual name of your combo box, not of the
underlying field. Check your combo box properties to be sure Column Count is
at least three, and check the row source to be sure that the third column is
populated. I think you said you already have done that, but I'll mention it
anyhow. Both of these things can be checked on the property sheet for the
combo box. Column Count is at the Format tab, and Row Source at the Data
tab. To check the row source, click Row Source, then click the three dots.
You will get an error message if you attempt to run the code if there is no
e-mail address associated with the name in the combo box (i.e. Column(2) is
empty).
If you are still having difficulties, what error message are you receiving?
Note that approximately one and a half hours after posting this I will be
away until Tuesday.

Thank you, this was very helpful. Just one problem, I'm getting an error
that I'm not sure how to fix.
This is what I have,

DoCmd.SendObject acSendNoObject, , , Me![Team Leader].Column(2),
"(e-mail address removed) "
, ,"Message Subject","Message Body"

I'm getting an error starting at the commas after the email address. The
way I read your reply this is where any Bcc would be and can be blank as long
as the commas are there. Also, to enter a subject line and body do I just
replace the "Message Subject" with my text?

thanks so much!

Check SendObject in Help. You didn't mention where the additional recipients
would come from, but in general it would look something like this:
DoCmd.SendObject acSendNoObject, , , Me![ComboBox].Column(2), "(e-mail address removed)
; (e-mail address removed)", , "Message Subject", "Message Body"

A couple of notes before I get to how you would add this code: Anything
identified in Help as the default choice need not be included in the code.
acSendNoObject could be left out, but the comma needs to remain. Stop when
there is nothing more to add. Commas are placeholders between options. In
the example above there are two commas before "Message Subject". Bcc (blind
copy) recipients of the e-mail would be inserted between the commas, but
there are none. Without the second comma "Message Subject" would appear in
the Bcc line on the e-mail. Note that combo box columns are numbered 0, 1,
2, etc., so Column(2) is actually the third column.
Important: There are no line breaks in the code. Enter it all on a single
line.
Regarding the code, I don't think it is possible to run the code from the
form's On Close event. However, if you use a command button to close the
form you can add the code before the instruction to close the form. To do
that, in form design view right click the command button, click Properties,
click the Event tab, click On Click, and click the three dots. If you are
creating a new command button, continue by clicking Code Builder, OK. In
either case you will see a code window with text. If you see On Error GoTo
... at the top click below that and start typing the code. If it is a new
command button start typing where the cursor is blinking. A message may pop
up as you type, attempting to guide you through the process.
There are a lot of options, so post back if you have more questions.
:

I have a Combobox in my form named Team Leader. The combobox has 3 columns,
1= ID, 2=Name and 3 = Email Address. The combo box actually show column 2
only. I would like to have an email sent to the email address from column 3
when the form is closed. I know I need to make this an event procedure from
the property box of the form. Problem is that I know nothing about vba and
have no clue where to go from here. I would also like to program in a few
set email addresses in addition to the one it pulls from the combo box.

Any help is very much appreciated. Thank you.