Email templates in table to be accessed by VBA – referring to fields in another table

[HadererDirndl]

I have a data base from which I can send emails via Outlook, using VBA. One use case is sending meeting invites. The information for the emails – meeting topic, date and location – is stored in tblMeetings. On the form that displays a particular meeting to the user, the user can click a button, and an Outlook email “invite” is generated. The information about the specific meeting is pulled from the currently open record. But, currently, the rest of the email subject and body are hardcoded in the VBA code. Example:

.Subject = "Reminder: Upcoming Meeting " & " on " & Me.MeetingDate & " (" & Me.MeetingTopic & ")"
.Body = "Hello staff," & Chr(13) & Chr(13) & "we want to remind you of our upcoming staff meeting:" & Chr(10) & Chr(13) & Me.MeetingTopic & Chr(13) & Me.MeetingDate & " from " & Me.MeetingStartTime & " to " & Me.MeetingEndTime & Chr(13) & "Location: " & Me.MeetingLocation & Chr(13) & Chr(13) & "Thank you!"
.Display

As a next step, I was hoping to expose the email texts to the user as “email templates”, so they can change them without anyone having to go into the code. (I am not talking about Outlook templates. This should all be within Access!)

For this, I thought I could construct a table called tblEmailTemplates, with (for simplicity’s sake) three fields: [EMailPurpose], [EmailSubject], and [EmailBody]. Then in the code, I would refer to those fields as follows:

.Subject = DLookup("EmailSubject", "tblEMailTemplates", "EmailPurpose = 'MeetingInvite'")
.Body = DLookup("EmailBody", "tblEMailTemplates", "EmailPurpose = 'MeetingInvite'")

This works mostly fine (I have not worked out email body formatting) if the subject and body do not need to refer to fields in tblMeetings. So for [EmailSubject]=”Upcoming Meeting”, it works as expected: An email with the subject Upcoming Meeting is generated. However, I have been unable to figure out how to refer to the respective fields from tblMeetings in the fields in tblEMailTemplates, in a way that they are then recognized by the code. The code just returns the content of the field EmailSubject as a string. So if I store the text as

"Reminder: Upcoming Meeting " & " on " & Me.MeetingDate & " (" & Me.MeetingTopic & ")"​

that’s literally what it will output into the email’s subject line, quotation marks and all.

Does anyone know how to store references in a table field that can be accessed with VBA? Is that even possible? (I am a VBA novice.) Thank you!

 

[Cronk]

Take the approach that the subject of the email is broken up into two parts, one the common text that will apply no matter when the meeting, and the second part the particulars.

The template type might be "ReminderMeeting" with the subject template text
"Reminder: Upcoming Meeting".

So your code will read

.Subject = DLookup("EmailSubject", "tblEMailTemplates", "EmailPurpose = 'ReminderMeeting'")  & " on " & Me.MeetingDate & " (" & Me.MeetingTopic & ")"

 

[MrHans]

Or maybe try to replace certain words in the template with your form fields.

So in the template you write %meetingdate%
Then use the replace function on that string to replace %meetingdate% with me.MeetingDate

 

[HadererDirndl]

Thank you for your suggestions. I will try them and let you know how it goes. I was surprisingly offline for a couple of months, apologies for the delay!

 

[Minty]

Try creating your text strings as variables outside of the outlook methods, then refer to the variable, something like; Also a debug.Print is a handy fault finding method

Dim sSubject as string 
Dim sBody as string

sSubject = DLookup("EmailSubject", "tblEMailTemplates", "EmailPurpose = 'MeetingInvite'"

sBody = DLookup("EmailBody", "tblEMailTemplates", "EmailPurpose = 'MeetingInvite'")

Debug.Print sBody

.Subject = sSubject
.Body = sBody

 

[HadererDirndl]

Thanks everyone! You are all awesome. I have solved this with a combination of your suggestions. See code below, in case anyone ever wants to do something similar.

Note: this assumes the existence of a table called tblEMailTemplates, which has the fields EmailPurpose, EmailSubjectTemplate, EmailBodyTemplate

Private Sub cmdEmailStaffMeeting_Click()

Dim oOutlook As Outlook.Application
Dim oEmailItem As MailItem
Dim rs As Recordset
Dim CurrentStaffEmailDL As String
Dim EmailSubjectTemplate As String
Dim EmailSubject As String
Dim EmailBodyTemplate As String
Dim EmailBody As String
Dim EmailPurpose As String
 

If oOutlook Is Nothing Then
    Set oOutlook = New Outlook.Application
End If
Set oEmailItem = oOutlook.CreateItem(olMailItem)
With oEmailItem
   .BodyFormat = olFormatHTML
   'create a string of email addresses that this email will be sent to
   Set rs = CurrentDb.OpenRecordset("qryEmailStaff")
    If rs.RecordCount > 0 Then
   rs.MoveFirst
    Do Until rs.EOF
        If IsNull(rs!StaffEmail) Then
        rs.MoveNext
         Else
            CurrentStaffEmailDL = CurrentStaffEmailDL & rs!StaffEmail & ";"
            .To = CurrentStaffEmailDL
            rs.MoveNext
        End If
    Loop
    Else
       MsgBox "No Email addresses in the query"
    End If
    
 'Accessing the email template to be used, and replacing the 'variables' in the templates using the Replace function
    EmailSubjectTemplate = DLookup("EmailSubject", "tblEMailTemplates", "EmailPurpose = 'Staff Meeting'")
    EmailSubject = Replace(EmailSubjectTemplate, "%TOPICandDATE%", Me.MeetingTopic & " on " & Me.MeetingDate)
    EmailBodyTemplate = DLookup("EmailBody", "tblEMailTemplates", "EmailPurpose = 'Staff Meeting'")
    EmailBody = Replace(EmailBodyTemplate, "%MEETINGDETAILS%", Me.MeetingTopic & Chr(13) & Me.MeetingDate & " from " & Me.MeetingStartTime & " to " & Me.MeetingEndTime & Chr(13) & "Location: " & Me.MeetingLocation & Chr(13) & Chr(13))
    EmailBody = Replace(EmailBody, "%RSVPDETAILS%", Me.MeetingHost & " by " & Me.RSVPbyDate & ".")
    EmailBody = Replace(EmailBody, "%HOSTINFO%", Me.MeetingHost)
        
    .To = CurrentStaffEmailDL
    .CC = ""
    .Subject = EmailSubject
    .HTMLBody = "<font face=Calibri, size=10pt>" & EmailBody & "</font>"
    .Display
End With

Set oEmailItem = Nothing
Set oOutlook = Nothing

End Sub