Solved

access 2010 and email form data

Posted on 2013-12-05
6
441 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 84
Comment Utility
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
Comment Utility
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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:kdschool
Comment Utility
could you provide a link that would show me how to do the sendobject action option
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

772 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

13 Experts available now in Live!

Get 1:1 Help Now