Send Email based on form criteria

[ibvolman]

Guys, I'm new here and have something I can't seem to get figured out. I am building a requisition database with access for our group to place orders while aboard our ship. There are certain things that will determine who the email will be sent to, what the subject line will be, and what the body will say.

The criteria comes from the form to place orders. I have a list box named "Group" for the vessel group that places the order, a list box named "Critical Equipment" that list critical systems to our operation, and a check box labeled "Urgent." If "Critical Equipment" or "Urgent" is empty, the email needs to be sent to our HSE department. If either or both of these fields contain a value, the email needs to go to both our captain and the HSE department. The body will inform of the group that placed the order, and the subject line will be as follows:

if it is not urgent or critical equipment -- "PR submitted awaiting approval"

if it is critical equipment -- "PR for Critical Equipment awaiting approval"

if it is urgent -- "Urgent PR is submitted requesting immediate action"

Here is the code I have that works for sending an email to one department, but the rest of the stuff is beyond me at this point. Any help is gretly appreciated.


Private Sub Command32_Click()


Email_Bcc = "HSE@ourCompany.com"
Email_Body = "A PR has been sumitted by"
Email_Subject = "PR submitted Awaiting Approval"

Set Mail_Object = CreateObject("Outlook.Application")
Set Mail_Single = Mail_Object.CreateItem(0)

With Mail_Single
.Subject = Email_Subject
.To = Email_Send_To
.cc = Email_Cc
.BCC = Email_Bcc
.Body = Email_Body & " " & I need to concatenate the group name here
.send
End With
End Sub

Again, thanks for any help.