[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

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

0
konadawg
Asked:
konadawg
  • 4
  • 3
  • 3
  • +1
1 Solution
 
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
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
Simon BallCommented:
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
 
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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now