How to Send Access Form Values in an Email Body
Question details
The user wants to successfully include specific text box values from a Microsoft Access form within the body of an automated approval email.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Triggering an automated email from an Access database form using VBA to request approval.
- Observed behavior
- The values from the form text boxes are not appearing correctly in the message body; the email incorrectly displays the VBA code syntax rather than the actual text-box contents.
Ensure you have your Microsoft Access form open in Design View and take note of the exact names of the text box controls you wish to include in your email.
Concatenate Form Controls into a VBA String Variable
Build the email body explicitly as a string variable before passing it to the email command, ensuring Access evaluates the form variables as text.
When passing values directly into the DoCmd.SendObject method, the syntax can become confusing, causing Access to output raw code instead of the evaluated data. By explicitly building the email body as a string variable first and utilizing standard VBA string concatenation, you ensure that the text box data is correctly parsed.
In your Access form's Design View, right-click the button used to send the email, select 'Build Event...', and choose 'Code Builder' to open the VBA editor.
Before generating the email, add a command to save any pending changes on the form so the email captures the latest data. Type: If Me.Dirty = True Then Me.Dirty = False
Create a string variable using concatenation (the & symbol) to link standard text with your form control values. For example: strBody = Me.txtFirstName & " " & Me.txtLastName & " is requesting a Grand Master Key. Do you approve?"
Add the line Debug.Print strBody right after your string declaration. You can open the Immediate Window (Ctrl+G) to verify that the generated text evaluates correctly before sending.
Finally, use the string variable in your email command: DoCmd.SendObject acSendNoObject, , , "approver@example.com", , , "Approval Request", strBody, True
Looking for a Free Office Suite? Try WPS Office
While Microsoft Access is highly specialized for database management, you may also need reliable software for word processing, spreadsheets, and presentations. WPS Office is a lightweight, intuitive, and highly compatible alternative to Microsoft Office that is entirely free to use.
- 1. Visit the Official Website: Go to the WPS Office website to find the free download link.
- 2. Download and Install: Click the download button for your operating system and run the quick installer.
- 3. Open and Edit: Launch WPS Office and immediately start working on your documents, spreadsheets, or PDFs seamlessly.
Frequently Asked Questions
Why does my Access email body show the VBA code instead of the text box values?
This usually happens when form variables are placed inside quotation marks. VBA treats everything inside quotes as literal text. To fix this, place variables outside the quotes and connect them to the text using an ampersand (&).
Do I need to save the Access form before triggering the email script?
Yes. If a user has typed in a text box but the record hasn't been saved, the VBA script might pull outdated or blank information. Adding 'If Me.Dirty = True Then Me.Dirty = False' ensures all current inputs are saved to the record before the email is generated.
Can I use TempVars to pass form values to an email body in Access?
Yes, TempVars can be used, but you still need to concatenate them properly into a string variable. If you don't use proper concatenation, the email body will output the TempVars syntax rather than the actual values stored inside them.
