Improve company productivity with a Business Account.Sign Up

x
?
Solved

Email from Microsoft Access Database to Microsoft Outlook

Posted on 2014-01-06
10
Medium Priority
?
441 Views
Last Modified: 2014-01-30
It has been well over 10 years since I did anything with access but any assistance is very welcome.
What I would like to do is, I have a table that has people’s names matched to their email address; I would like to create a form/report/query that allows me to select names from the table that I can send a canned message to something like requiring them to come to the office at a certain time.
Our mailroom is a little busy and currently we have to type the email then type in everyone’s email address instead of that I would like to use the collaboration between access (or Excel) and Outlook to simply select the names of people who have packages and have Access create the email message that I can subsequently send out…
0
Comment
Question by:juslearning
  • 5
  • 4
10 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39761733
Your question is in both the Access and Excel topic areas.  This is an MS Access solution...

The easiest method is the SendObject command:
http://msdn.microsoft.com/en-us/library/office/ff197046.aspx

Docmd.SendObject acsendnoObject,,,"email@abc.com","This is the subject", "This is the message body"

Open in new window


For a form based approach as you are describing, set up a Yes/No field in the table that contains names/emails to indicate whether that person should receive emails.

Create a form in continuous forms view to display people, email addresses and a checkbox to pick which people get sent emails.

In the continuous form header, add a textbox (call it txtMessage) where you can type a message to be sent and another textbox (txtSubject) where you can type a subject

Add a command button to send the emails, and try this code in it's click event (You will have to make adjustments to match your own field and control names):

Dim rs as DAO.recordset
Set rs = Me.recordsetClone

Do Until rs.EOF

if rs!TheNameOfYourSendEmailYesNoField = true then
   Docmd.SendObject acsendnoObject,,,Me.txtEmailAddress,Me.txtSubject, Me.txtMessage

End If
rs.MoveNext
Loop
Set rs = nothing

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 39761744
And if you need more flexibility than the SendObject method allows, there is also Outlook Automation - which essentially opens Outlook from Access or Excel (or anywhere else), and gives you the flexibility of the full Outlook UI, manipulatable through code.

The coding is more involved, but it is a very powerful method.

Take a look at Patrick Matthew's article here for details, code and sample database and spreadsheet:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_4316-Automate-Outlook-in-VBA-with-the-OutlookCreateItem-Class.html
0
 

Author Comment

by:juslearning
ID: 39761758
I appreciate the response, but to be honest it would be helpful if you dumb it down further as my memory of Access is even worse than I remembered.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
LVL 61

Expert Comment

by:mbizup
ID: 39761855
Ok - I'm assuming you have a table with the names and emails as you indicated in your original post.

1.  Add a field "SendEmail" with Yes/No Data type

2.  Create a form, setting the Recordsource property to your table, and the default view to 'Continuous Forms'

3.  Place 2 textboxes in the detail section one with the control source pointing to your Name field, the other with the control source pointing to your email address field

4.  Add a checkbox to the form's detail section, control source pointing to the SendEmail field

5.  Add textboxes txtSubject and txtMessage (with no control sources) to the header section of your form.  These are for you to manually enter subject and body content for the emails (it won't be saved in a table)

6.  Add a command button to the header section with those two textboxes.  From the command button's property sheet, under the events tab, click the ... next to On Click., then click 'Code Builder and place this between the Sub and End Sub lines (Adjust field names if needed to match those in your own database)

Dim rs as DAO.recordset
Set rs = Me.recordsetClone

Do Until rs.EOF

if rs!SendEmail = true then
   Docmd.SendObject acsendnoObject,,,Me.txtEmailAddress,Me.txtSubject, Me.txtMessage

End If
rs.MoveNext
Loop
Set rs = nothing

Open in new window


Please give it your best try, following those directions.

If needed, search for and dig into tutorials on building forms in Access,

Then if you get stuck, post a sample database with what you have tried and specific feedback about what you need help with.
0
 
LVL 86
ID: 39761869
Note too that if you'd prefer a 3rd party solution, FMS has a very nice product here:

http://www.fmsinc.com/MicrosoftAccess/Email.asp

It's highly configurable, and can generally tackle any email task needed with Access databases.
0
 

Author Comment

by:juslearning
ID: 39767167
OK mbizup,
I am good with the adding of all the textboxes and the like, where I get lost is the code for the code builder.
This is a really basic question my table is named Personnel with the following columns for data "Personnel, Email Address, Has Mail?" how would I go through what you described to get the effect that I am looking for?
Thank you...
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39770621
If this is a database that you are just getting started with, I'd recommend changing your field names to EmailAddress and HasMail (without the spaces and without the question mark).  Best practices are to avoid spaces and special characters in your naming conventions.  Avoiding these characters may save you headaches in the long run.

However, if you have to keep those names, anytime you refer to them you will have to enclose them in square brackets.

What are the names of your textboxes on the form (the Name property under the 'Other' tab in the property sheet -- see the screenshot)?

if the names of your textboxes are as follows, then you should be able to test the code I posted earlier without modifications:

txtEmailAddress:   In the detail section of your form, Control Source property set to the [Email Address] field in your table

txtSubject:  In the header section of your form, Control Source property left blank (unbound)

txtMessage: In the header section of your form, Control Source property left blank (unbound)

The code (unchanged):

Dim rs as DAO.recordset
Set rs = Me.recordsetClone

Do Until rs.EOF

if rs!SendEmail = true then
   Docmd.SendObject acsendnoObject,,,Me.txtEmailAddress,Me.txtSubject, Me.txtMessage

End If
rs.MoveNext
Loop
Set rs = nothing

Open in new window

Property sheet
Control source property
Let me know where you are getting stuck...  (or upload a sample copy of the database you are working with)
0
 

Author Comment

by:juslearning
ID: 39775696
I must be worse at Access than I recall, because I cannot get this ish to work...
Test.accdb
0
 
LVL 61

Accepted Solution

by:
mbizup earned 1500 total points
ID: 39776147
Please upload all of the relevant objects - not just the table, but the form and code as you have implemented it (table, form, code).  That will help me understand what you have tried, what the stumbling blocks are and where I need to explain things better.
0
 

Author Comment

by:juslearning
ID: 39781651
Unfortunately I cannot fill in data beyond what is given as I do not want to run into releasing information that maybe proprietory or sensitive. Suffice it to say that the table attached in an iteration of my start point, from there I needed to create a "continious form" (not sure how to do that as Access 2010 does not give that option for "continious") but I have the form created, but when following your instructions I cannot end where you concluded, either my version is different or I am missing something.
I appreaciate your time and efforts thus far, but something is missing to get me to where I need to be...
I hope you understand...
0

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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.

Join & Write a Comment

An Outlook data file aka PST is prone to corruption due to various reasons. A corrupt PST file is always inaccessible. Also, you may lose all your crucial mailbox content at any time. So to fix this corruption and protect the mailbox content from be…
Ever faced an issue with Microsoft Outlook? If you’ve been working long enough with this popular emailing platform, you’ve probably faced more than one issue. Like to restore ScanPST.exe .bak file. Here in this post we will discuss the methods to re…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

606 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question