Solved

Email from Microsoft Access Database to Microsoft Outlook

Posted on 2014-01-06
10
369 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 84
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 500 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

773 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