Solved

Email from Microsoft Access Database to Microsoft Outlook

Posted on 2014-01-06
10
349 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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 84
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:juslearning
Comment Utility
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
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Create high volume marketing opportunities using email signatures with these top 10 DOs and DON'Ts of email signature marketing.
This process describes the steps required to Import and Export data from and to .pst files using Exchange 2010. We can use these steps to export data from a user to a .pst file, import data back to the same or a different user, or even import data t…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

763 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now