Use VBA variables inside an Access table field

Question

I'm placing my email body inside a table to easily edit the content. The problem is that there is some variables I need to use inside that body content.

For example:

I use the code bellow to send the email. And as you can see, my email body comes from a memo field inside my table (.HTMLBody = "" & Me.Html_Email_Body & "") and I need to use some variables inside the Html_Email_Body field like so:

(This is the text that I have inside my memo field)

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head></head>
<body>
Hi " & Me:PersonName & ", how are you?
</body>
</html>

The output result is: Hi " & Me.PersonName & ", how are you?

And the output result should be: Hi Bob, how are you?

Is this possible?

(This is the code I use to send my emails)

Sub SendEmail_Click()
Dim NewMail As CDO.Message
Set NewMail = New CDO.Message

'Enable SSL Authentication
NewMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True

'Make SMTP authentication Enabled=true (1)

NewMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1

'Set the SMTP server and port Details
'To get these details you can get on Settings Page of your Gmail Account

NewMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"

NewMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

NewMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

'Set your credentials of your Gmail Account

NewMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusername") = "[email protected]"

NewMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "mypassword7"

'Update the configuration fields
NewMail.Configuration.Fields.Update

'Set All Email Properties

With NewMail
Dim strPath As String
strPath = ".mysite/wp-content/uploads/2017/07/myimage.png"
.subject = "this is the subject"
.From = "<[email protected]>"
.To = Me.EMAIL
'.CC = ""
'.BCC = ""
.HTMLBody = "" & Me.Html_Email_Body & ""
.AddAttachment "https://mysite/temp/contacts.vcf"
End With

NewMail.Send
MsgBox ("This email was sent!")

'Set the NewMail Variable to Nothing
Set NewMail = Nothing

End Sub

That's not gonna work. You need to reference to the person's name using VBA code. — Rene
– Rene, Commented Sep 15, 2018 at 23:20

Jericho Johnson · Accepted Answer · 2018-09-16 01:45:07Z

I do this kind of thing in many of my applications. I insert Field References into my email templates and then use a routine I wrote to replace them dynamically with the correct values at runtime. In my case, this is usually done in a loop with a RecordSet that contains several people who are each receiving an individual copy of the email message and I am customizing the template for each recipient.

Here is a small sample email template:

<p>Hello [RecipFirstName],</p>  This auto-generated email has been sent to notify you that:

<h4>Approval Mailed is <b>LATE</b>.</h4>
Approval Mailed Date: [ApprovalMailed_Date]

[ProjectTemplate1]

Then my code to fill the template looks like:

'[mBody] is a string that will be the Body of the email
'[templateBody] is a string that was previously set to the email
'  template for the message being processed
'[rstProject] is a DAO.RecordSet that was previously set to the
'  required dataset for my purposes
'Notice that I use a combination of [Replace] functions and 
'  my custom function [sitsProcessFieldReferences] to update the
'  template with the appropriate values.

'In my case, replace CRLF in the template with <BR>
mBody = Replace(templateBody, vbCrLf, "<BR>")
mBody = sitsProcessFieldReferences(mBody, rstProject)
'The following examples use variables I have already defined and
'  populated to replace the field refernces.
mBody = Replace(mBody, "[RecipFirstName]", FirstName)
mBody = Replace(mBody, "[RecipLastName]", LastName)
mBody = Replace(mBody, "[RecipFullName]", FirstName & " " & LastName)
mBody = Replace(mBody, "[ProjectTemplate1]", pTemplate1)

Finally the function that does the field reference replacement. Notice that I have a special case that if I name a field reference with "price" in the name, I want the replacement value formatted as Currency. You can customize this code for any situation. It just requires some pre-planning to keep a consistent naming convention for your field references.

This function takes an email template (or any text string) and searches it for field names matching any field in the RecordSet (enclosed in square brackets) and replaces that reference with the value from the corresponding field in the RecordSet

Public Function sitsProcessFieldReferences(ByVal orgString As String, rstFields As DAO.Recordset) As String
On Error GoTo Err_PROC
    Dim ErrMsg As String
    Dim fld As DAO.Field

    For Each fld In rstFields.Fields
        If InStr(fld.Name, "price") Then
            orgString = Replace(orgString, "[" & fld.Name & "]", Format(Nz(fld.Value, 0), "Currency"))
        Else
            orgString = Replace(orgString, "[" & fld.Name & "]", Nz(fld.Value, ""))
        End If
    Next fld
    Set fld = Nothing

Exit_PROC:
    sitsProcessFieldReferences = orgString
    Exit Function

Err_PROC:
    Select Case Err.Number
        Case Else
            ErrMsg = "Module: " & strModName & vbCrLf
            ErrMsg = ErrMsg & "Error: " & Err.Number & vbCrLf
            ErrMsg = ErrMsg & "Line: " & Erl() & vbCrLf
            ErrMsg = ErrMsg & Err.Description
            DoCmd.Hourglass False
            MsgBox ErrMsg, vbOKOnly + vbCritical, "Function sitsProcessFieldReferences"
            Resume Exit_PROC
            Resume
    End Select

End Function

In your email template, you would change the following line:

Hi " & Me:PersonName & ", how are you?

to something like:

Hi [PersonName], how are you?

Then either do a Replace(emailTemplate, [PersonName], "Bob") if you have the replacement values already in a variable or something.

Or, if the value is in a RecordSet, you would change [PersonName] in the template to match the name of the field in the RecordSet that contains the value Bob and then use my custom function: sitsProcessFieldReferences(emailTemplate, YourRecordSet)

bpy · Accepted Answer · 2018-09-16 14:35:06Z

1

I manage to find the solution myself because I couldn't implement @Jericho Johnson although it was somehow useful...

What I did was setup a new variable (MyHTMLBody) for the email body and several replacements as I need (see bellow).

After that, I setup the .HTMLBody = MyHTMLBody this way, and now I can use some bookmarks in the HTML like this: Hi [r_name], how are you? This is your [r_email].

  MyHTMLBody = Me.Body
  MyHTMLBody = Replace(MyHTMLBody, "[r_name]", Me.Client_Name)
  MyHTMLBody = Replace(MyHTMLBody, "[r_email]", Me.Client_Email)

  .HTMLBody = MyHTMLBody

edited Sep 16, 2018 at 14:35

answered Sep 16, 2018 at 12:22

bpy

1,18011 silver badges35 bronze badges

7 Comments

user2261597 Over a year ago

That was exactly what Jericho Johnson suggested to you.

Jericho Johnson Over a year ago

Actually, Alex, if you READ my entire solution, you will find that I present BOTH options, a custom function to handle replacing multiple values all at once from a RecordSet AND the use of the Replace function. Additionally, I provided the recommended reformatting of your template html to facilitate the replacement of embedded reference fields. You may disagree with @peakpeak and I, but being rude to others is uncalled for.

bpy Over a year ago

@JerichoJohnson First of all, you present a small template: Here is a small sample email template: then you place my code to fill the template looks like: finally you present the function that does the field reference replacement. If I read this right, your answer (such as it is, now), you have to place (my code to fill the template looks like:) and then, use your function (the function that does the field reference replacement) to make this work. I tried to combine both and this didn't work. (to be continued bellow...)

bpy Over a year ago

@JerichoJohnson I tried to combine both and this didn't work. If they work independently, please clarify... Meanwhile, I never intend to be rude with anyone, but I can't stand someone who down-vote people without any justification, just because... I appreciate your help (as I previously state), your answer was useful and I voted up for that reason. So, if you feel I was rude to you, please accept my apologies.

Jericho Johnson Over a year ago

I feel that your solution derived directly from my answer. I appreciate the upvote, but in my opinion, you should have also "Accepted" the answer since you used the information in my answer to directly solve your problem. The fact that you did not need the second field replacement method (filling from a RecordSet) is irrelevant. Your question did not specify where you would be getting the data, therefore I provided two options. No hard feelings. This is how we learn. Good Luck.

|

Collectives™ on Stack Overflow

Use VBA variables inside an Access table field

2 Answers 2

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related