?
Solved

access 2010 and email form data

Posted on 2013-12-05
6
Medium Priority
?
472 Views
Last Modified: 2013-12-23
I have to create a form in access that a user fills out from a shared library using their own access clients.  When they complete the form it must go into the database and also get emailed to  a group email address at the same time.  Is there a way to submit the data to the database and at the same time email that to the group of folks who need it.  They must see every new initial entry quickly.  I am not a programmer so I won't be able to use visual basic for this unless it simple.  I saw the macro feature but did not see where it would do this from a form just from a table or a report.
0
Comment
Question by:kdschool
  • 2
  • 2
  • 2
6 Comments
 
LVL 85
ID: 39699381
You can't do this without programming, and you can't do this unless the users are entering data through a form (i.e. if they're entering data directly in a Table or Query, then you're pretty well out of luck).

Submitting to the database is done automatically if you have bound Access forms (i.e. a form that is based on a table).

Macros can be called from Forms and Reports, so you can use those. To send an email, you use the SendObject method. I haven't used macros in a very long time, but essentially you create a New Macro, and then select the SendObject action. Select the item you want to send, fill in the details (i.e. Subject, email address, etc) and then set the Macro to be run when a certain Form event occurs (like AfterInsert, which runs when a new record is added).
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 39701541
I would put a command button on the form where the users enter the data, to create an email containing the data and send it to a predefined Distribution List.  Here is some code for a similar process (it would have to be adapted to your needs):

Private Sub cmdSendEMail_Click()
'Created by Helen Feddema 23-Mar-2012
'Last modified by Helen Feddema 6-Dec-2013

On Error GoTo ErrorHandler

   Dim strToEMail As String
   Dim strMessageSubject As String
   Dim strCustomerName As String
   Dim strCompany As String
   Dim strTaskSubject As String
   Dim strBody As String
   Dim dteSend As Date
   Dim frm As Access.Form
   Dim strMessage As String
   Dim strSendTime As String
   Dim strSendDate As String
   Dim strCoverSheet As String
   Dim msg As Outlook.MailItem
   Dim tsk As Outlook.TaskItem
   Dim att As Outlook.Attachment
   
   'Test for required fields
   strToEMail = Nz(Me![txtToEMail].Value)
   If strToEMail = "" Then
      MsgBox "Please enter an email address"
      GoTo ErrorHandlerExit
   End If
   
   Set pappOutlook = GetObject(, "Outlook.Application")
   Debug.Print "Email: " & strToEMail
   strMessageSubject = Me![txtMessageSubject].Value
   strTaskSubject = Me![txtTaskSubject].Value
   If strTaskSubject = "" Then
      MsgBox "Please enter a subject"
      Me![txtSubject].SetFocus
      GoTo ErrorHandlerExit
   End If
   
   strBody = Me![txtBody].Value
   If strBody = "" Then
      MsgBox "Please enter the message text"
      Me![txtBody].SetFocus
      GoTo ErrorHandlerExit
   End If
   
   'Send email
   'Create new mail message and send it now
   Set msg = pappOutlook.CreateItem(olMailItem)
   With msg
      .To = strToEMail
      .Subject = strMessageSubject
      .BodyFormat = olFormatPlain
      .Body = strBody
      .Save
      'Use this line to display the message
      .Display
      'Use this line to send the message automatically
      '.Send
   End With
   
ErrorHandlerExit:
   Set msg = Nothing
   DoCmd.Close objecttype:=acForm, objectname:=Me.Name
   Exit Sub

ErrorHandler:
   'Outlook is not running; open Outlook with CreateObject
   If Err.Number = 429 Then
      Set pappOutlook = CreateObject("Outlook.Application")
      Resume Next
   Else
      MsgBox "Error No: " & Err.Number _
         & " in " & Me.ActiveControl.Name & " procedure; " _
         & "Description: " & Err.Description
      Resume ErrorHandlerExit
   End If
   
End Sub

Open in new window

0
 

Author Comment

by:kdschool
ID: 39708735
Helena on this code trying to figure out what  I modify ?

The DIM would be each variable or field in my form that I want to be sent through email instead of the ones you have listed.

Not clear on what I should modify on this part. Would TO = StrToEmail be my distribution email address?
'Create new mail message and send it now
   Set msg = pappOutlook.CreateItem(olMailItem)
   With msg
      .To = strToEMail
      .Subject = strMessageSubject
      .BodyFormat = olFormatPlain
      .Body = strBody
      .Save
      'Use this line to display the message
      .Display
      'Use this line to send the message automatically
      '.Send

would I then add this code to the button as a vb script or a macro that executes on submit?  Thank you so much.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

by:kdschool
ID: 39717081
could you provide a link that would show me how to do the sendobject action option
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 39717958
The Access help file has a good writeup on SendObject. Here's the online version of that:

http://msdn.microsoft.com/en-us/library/office/ff197046.aspx

If you want to just send an email, with no attached item, then you'd do this:

DoCmd.SendObject acSendNoObject, , , "bob@bob.com", , "Email Subject", "Here's the email"

Obviously, you'd need to replace the various bits to reflect what you need. If you have a dedicated email address you will be sending this to, then you can hardcode that.
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 39736889
If you use my technique, then you need to set a variable from a control on your form for each piece of information you need to use in the email, then set the appropriate email properties with the variables.  Using variables lets you check whether they have appropriate values before creating the email.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

750 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