MSAccess reference a field name from a form into a query and email

Question

I have a table "emailtocustomer" with 3 fields, an ID, type_id (relationship to typeid in types table (number 1-6)), Subject (type text) and Body (type memo), so that I or another user can easily change the text or subject if I need to on down the road. This table references emails to send to customers.

The intended design is that the user of the application will generate a reference number (in this case an RMA ticket number) using the customer's email, contact name and company name, and click a button on the form in which the RMA Number is generated to generate an email to the customer with instructions on how to return their product.

Access then dumps the information to an email window. I have the below VBA code in the "OnClick" of the button to generate the email:

' Generate Email to customer
Dim EmailTo, Subject, EmailBody, strSQL As String
Dim RMAType As Integer
Dim olApp As Object
Dim objMail As Object
Dim rs As DAO.Recordset
Dim db As Database

If IsNull(Me.contactemail) Then
    MsgBox "You cannot generate an email for this RMA - there is no email address!"
    Exit Sub
End If
Set olApp = CreateObject("Outlook.Application")
Set objMail = olApp.CreateItem(0)

EmailTo = Me.contactemail

RMAType = Me.type

Set db = CurrentDb

strSQL = "SELECT * FROM emailtocustomer WHERE type_id = " & RMAType & ""

Set rs = db.OpenRecordset(strSQL)

Subject = rs!email_subject
EmailBody = rs!EmailBody

With objMail
    '.BodyFormat = olFormatHTML
    .To = EmailTo
    .Subject = Subject
    .HTMLBody = EmailBody
    .Display
End With

Set rs = Nothing
Set db = Nothing

On the form the user selects the "type" of RMA which is the reason the RMA is generated, a single digit ID number (me.type). The user also fills out the contact name (me.contactfirst), the contact email (me.contactemail) and the application generates the RMA Number (me.rmanbr)

emailtocustomer.emailbody contains the body of the email that is to be sent. It and the emailtocustomer.email_subject fields I need to have contain references to the fields 'contactfirst', 'companyname', and 'rmanbr' from the "New RMA" form.

Here's an example excerpt from the emailtocustomer table:

email_subject:

RMA Information for RMA# R" & forms![New RMA]![rmanbr] & "

emailbody:

<html>
<head><body style='font-family: tahoma'></head>
<body>Hello " & forms![New RMA]![contactfirst] & ",<br /><br />
Thank you for requesting an RMA from us. For quick and 
consistent communications regarding this RMA, please reply directly to 
this email, and leave the subject line intact.<br /><br />
Your RMA Number is R" & forms![New RMA]![rmanbr] & ". This RMA number should 
be <u>marked on the outside of your package(s)</u>. Packages received 
without an RMA number may be returned to the sender at their expense.<br />
<br />

In the placeholders " & forms![New RMA]![contactfirst] & " I need the appropriate field to carry over into this email text. Is there anyway to accomplish this? I've tried the designation "Me." but that doesn't seem to do it either.

Thanks for all the help.

Parfait Parfait · Accepted Answer · 2017-05-18T18:44:13

Consider using placeholders %...% in the EmailBody field in table. Note: percents mean nothing and can use any other marker, special characters, or none at all.

<html>
<head><body style='font-family: tahoma'></head>
<body>Hello %ContactFirst%,<br /><br />
Thank you for requesting an RMA from us. For quick and 
consistent communications regarding this RMA, please reply directly to 
this email, and leave the subject line intact.<br /><br />
Your RMA Number is R %RMAnbr%. This RMA number should 
be <u>marked on the outside of your package(s)</u>. Packages received 
without an RMA number may be returned to the sender at their expense.<br />
<br />

Then, in developing the email, replace the placeholders with form values using Replace:

...
Subject = "RMA Information for RMA# R" & forms![New RMA]![rmanbr]

EmailBody = Replace(rs!EmailBody, "%ContactFirst%", forms![New RMA]![contactfirst])    
EmailBody = Replace(rs!EmailBody, "%RMAnbr%", forms![New RMA]![rmanbr])

With objMail
    '.BodyFormat = olFormatHTML
    .To = EmailTo
    .Subject = Subject
    .HTMLBody = EmailBody
    .Display
End With 
...

MSAccess reference a field name from a form into a query and email

2 Answers