Getting table/form content into generated emails using VBA

[Monterey_Manzer]

So here is my situation:



I am building a form that will be used to track issues requiring action by multiple parties. There are several fields like "So&So Action Needed". Due to privacy act concerns, the table this form directly relates to cannot contain names of the people whose issues these are. Thanks to some crafty people on this forum, I've got a cool combo box set up that checks the ID numbers we use to identify people against a separate table and returns the name for the individual you are looking at. This made my coworkers very happy. However, now I need to do something similar with Access generated emails. My predicament is as follows-

I'm generating emails using the DoCmd.SendObject command once someone updates the "So&So Action Needed" field. I have managed to get text from those "Action Needed" fields into the text of the message (thanks once again to crafty people on this forum). However, now I need to grab that name from the combo box and put it in the email subject. However, when I execute the following code I get an email with the ID number in the subject.

DoCmd.SendObject acSEndNoOBject,,,So&So@blahblah.org,,,"So&So Action Needed for " & Combo90.Value,,True

The combo box has the ID number as it's control source but uses a query that links the number to a name as it's row source. How do I get that name to appear instead of the number?

Follow Up: Does anyone know how to layer in an If Then command such that the SendObject command will use different email addresses depending on some other criteria?

Wow...I know I'm a verbose dude but that's a pretty involved story. Anyway, I'd appreciate any help from fellow code junkies. Thanks!

[June7]

The combobox is multi-column and one of the columns has the name? Reference the column that has the name. Column index starts with 0. If the name is in column 2 the index is 1.

& Combo90.Column(1)

As for the conditional email address, sure, could be done. What is the source for the alternate email? Is it included in the form's recordsource?

[Monterey_Manzer]

Thanks a million! Due to the goofiness of the Query/table that was the source for the combo box is was Column(0) but I'm just so stoked it works!

As for the conditional email address, well that's tricky. The record source for the form is a table of issues pertaining to individuals. The records of these individuals that have the value which would set the email address are found on another table. I am using a unique identifier for each individual but since one person may have many issues the relationship between the personnel details and the issues log is a one to many. I'm assuming there must be a way to let Access know that it needs to find the value of a particular field in a named table by using the primary key for that table (our unique identifier) but that is a level of code reference I have yet to develop.

Thanks for the awesome help thus far!

[Monterey_Manzer]

Okay June7, I might have made this a little easier for both of us.

I've used another unbound combobox to pull in the value that would be the source for the alternate email. Here's the situation: There are 4 possible values for this field (let's call it a division of a company) in any record. What I would be looking for (and I might be answering my own question here) is using a long If, Else If, Else If, Then statement to define a variable in the DoCmd.SendObject command.

Is it possible to generate a variable in the SendObject command that will have it's value defined by the If, Then statements? Then I'm assuming I would write a long If, Then statement that defined the variable as a particular email address depending on which of the 4 possible values is in the earlier mentioned combobox. Let me know if I'm running down a rabbit hole or if I'm actually on to something that might solve my problems.

Cheers!

[Monterey_Manzer]

Alright...so this probably a much messier way to handle this problem, but I did get the result I wanted.

Here's how I went about it: Using the same principles behind my original unbound combobox that returned a name, I generated another combobox that returned the email address for the person who needs to be notified of the action. I then wrote the DoCmd.SendObject statement such that it used Combo100.Column(0) as the "To" in the email. In order to keep my form looking clean and basic, I made the combobox not visible to users. This might be an ugly way to achieve these results, and probably leaves me vulnerable to some sort of crazy macro hick ups but it will do what I want while I have time to further educate myself about VBA concerns.

One last question. Currently I have Yes/No checkbox that initiates these emails. The only problem is that it initiates "On Click", so even if you are deselecting the button it initiates the VBA code which generates the email. I know there must be a way (probably If, Then) to adjust it so that only when this box is checked it generates the emails.

Thanks June7 and others who have helped me out in the past. Getting into Access design is significantly less scary with a cool group of people to query on random stuff.

[Monterey_Manzer]

Nevermind, solved that one too.