Solved

access 2010 and email form data

Posted on 2013-12-05
6
448 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
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:kdschool
ID: 39717081
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
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
vba sql wild card passing in code 3 24
Trying to open FORM in specific record !! 6 45
Run SQL Server Proc from Access 11 31
Access check if a table is open 4 43
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

777 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