Sending an email from a form in Access 2007

Hello,

I'm still a noob when it comes to Access so appreciate any help here.

I am creating a change request form and want a button that will act as an approval button and send an email to a recipient confirming that the change can go ahead.

I have some code that I borrowed from the web but I'm not sure how to add the list of email recipients.

This is the code I have as an on-click event

Function sendEmail(emailAddresses As String, subjectLine As String, message As String)
    ' send the email
    DoCmd.SendObject acSendNoObject, , , emailAddresses, , , subjectLine, message, False
 
End Function

We use exchange 2003 and outlook
 
Can anyone point me in the right direction?

Thanks and Regards

konadawgAsked:
Who is Participating?
 
Simon BallConnect With a Mentor Commented:
speech marks around the "string" of emails  as i said earlier


static:

dim xaddress as string
xaddress = "i.moffat@vascutek.com;t.erskine@vascutek.com;p.goretti@vascutek.com;c.barr@vascutek.com;m.zawadzki"

sendEmail(xaddress, "Change Request Descision", "Go Ahead")

Open in new window

0
 
Simon BallCommented:
the addresses will need to be a string, probably seperating each address with ";"

so you will need to define a list.

will it need to be dynamic, or from a table the end user can edit, or just a static list?

static:

dim xaddress as string
xaddress = "sudo@nim.com;nim@sudo.com"

sendEmail(xaddress, "my email subject", "my message data")

0
 
Simon BallCommented:
if its dynamic,
you could have a table for instance "emailtable" with 2 columns, column 1 is contact type, column 2 - is email.

contact type, email
main, sudo@nim.com
main, nim@sudo.com
special, sudo@nim.com

then depending on the workflos item, your code can use a recordset or query to select from emailtable where contact type = 'main'

and have a function which returns "email addresses" seperated by ";" or whatever charater s required.

i can help with code for that if you want it.

also, you might find you cannot send multiple emails from one

DoCmd.SendObject acSendNoObject, , , emailAddresses, , , subjectLine, message, False

you might have to do a loop and send individual message to each person in address list.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
To use that sendEmail function, you'd call it like this:

sendEmail "usera@mydomain.com;userb@mydomain.com;userc@mydomain.com", "This is an email", "This is the body of the email"

You can string together two or more email addresses by separating them with a semicolon.

Note also that your "function" does nothing more than call the SendObject function, so you could simply do this directly in code:

DoCmd.SendObject acSendNoObject, , , "usera@somedomain.com;userb@somedomain.com;userC@somedomain.com", , , "This is the Subject", "This is the body", False

Note the last item on that line (the "False" argument) tells Access to NOT show the email, and to instead send it immediately.

Note also that you may get caught by the security features in Outlook. In many cases, OUtlook will throw a dialog box which you must click in order to allow the outside process (msaccess.exe) to interat with OUtlook. There are ways around this (vbMAPI is my favorite: www.everythingaccess.com), but be aware of this.
0
 
peter57rCommented:
You can create a linked table to your Outlook Contacts.  That way you could build a list box or subform enabling you to select multiple recipients from a list.
0
 
konadawgAuthor Commented:
Thank you for the very quick responses!  I need to work on this and will get back to each of you.

Thanks for now.
0
 
konadawgAuthor Commented:
Just thinking... Its going to be a static list of about 5 addresses btw.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Then use the method I suggest. You can "hard code" the email values using the semicolon syntax.
0
 
konadawgAuthor Commented:
Hmmm, wish I had some spare time to learn this stuff rather than chip away at it randomly...

Function sendEmail(emailAddresses As String, subjectLine As String, message As String)
    ' send the email
    DoCmd.SendObject acSendNoObject, , , i.moffat@vascutek.com;t.erskine@vascutek.com;p.goretti@vascutek.com;c.barr@vascutek.com;m.zawadzki, , , Change Request Descision, Go Ahead, False
 
End Function
 
This dosen't seem to work so I'm missing something...  Its the sendemail bit right??  Probelm is, I'm not sure where that should go........

thanks for the help so far!
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
As Sudonim indicates,  you should enclose all string values in double quotes ( " ). Note that you should NOT enclose a Variable in double quotes, but only the actual string value itself.
0
 
konadawgAuthor Commented:
Thank you.
0
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.

All Courses

From novice to tech pro — start learning today.