How can I email users from multiple contact drop down lists on same form

Hello,

Access 2010
I have a form that has multiple dropdown lists (same user contacts in all) that are then displayed on the form as team members for a task.  I would like to send an email notification to those user when they are selected for a task. There is a Save and Close button that is clicked when the form is complete.

Thank you in advance!
1212pro
1212proAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SheilsCommented:
This can be done by using vba coding on the after update event of the form.

Create a module and paste in the following code

Public Function SendEnail(strRecipient as String,strSubject as String,strBodyText as string)

Dim olApp As Object
Dim objMail As Object

On Error Resume Next 'Keep going if there is an error

Set olApp = GetObject(, "Outlook.Application") 'See if Outlook is open

If Err Then 'Outlook is not open
Set olApp = CreateObject("Outlook.Application") 'Create a new instance of Outlook

End If

'Create e-mail item
Set objMail = olApp.CreateItem(olMailItem)

With objMail

'Set body format to HTML
.BodyFormat = olFormatHTML
.To = strRecipient
.Subject = strSubject
.HTMLBody = strBodyText
.send

End With

MsgBox "Email Send successfully"

End Function

Open in new window


Then call the send email function from the afterupdate event of your form.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
1212proAuthor Commented:
Hello Sheils,
I'm pretty much a novice at this.  How will outlook know where to look for the email addresses of the team members?  Here are some screen shots of what I have.Team-1.docx
0
SheilsCommented:
You need a table t store the name and email address of all your contacts.

Let's call that tblContact and it will have the following fields:

fldContactId, fldContactName,fldContactEmail

Then you can use the following code to call the sendemail function

Private Sub Form_AfterUpdate()

Dim strEmailAddress as string
Dim strSubject as String
Dim strBodyText as String

strEmailAddress=DLookup("fldContactEmail","tblContact ","fldContactId=" & Me.cboDelegatedTo)
strSubject ="New Task"
strBodyText ="You have a new text"

Call SendEnail(strEmailAddress,strSubject,strBodyText)

End Sub

Open in new window


This code is just to send email to the person in the delegated to combo. I assume that your combo box is bounded to the id of that person which should come from the contact table.

Once you've got that working and understand how the code works I will help you expand it to also send email to all team members. However, I note that it will be much easier to use a team member table and then use a teammember subform on your main form. In addition to making your code easier it will also give you the flexibility of have bigger teams if necessary.

The tblTeamMember should have the following fields:

fldControlNumber,fldTeamMember (lookup fldContactId in tblContact).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.