Email template selector/editor

[dimmech]

Hello,
I'm having difficulty figuring this out (still green). I have a form that returns the customers information and a button that creates an email based on that info using "obj.CreateItem" method.

Instead of having one formatted email Ive created a form that I could select from several templates or create new ones.

I want to dynamically change the code below. Based on the users input in the new form and the current record in my customer form.

Looking for advice, tutorials, examples on how to do this please.

Code:
Dim obj As New Outlook.Application, objEmail As Object, cn As ADODB.Connection, cn1 As ADODB.Connection, cn2 As ADODB.Connection
Dim rsInfo As ADODB.Recordset, rsEmp As ADODB.Recordset, rsPub As ADODB.Recordset, sSig As String, sGreet As String, sPub As String

Set cn = CurrentProject.AccessConnection
Set rsInfo = New ADODB.Recordset
With rsInfo
Set .ActiveConnection = cn
.Source = "select * from tblCompanyInfo;"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With
If Not IsNull(cboEmployee) Then
Set cn1 = CurrentProject.AccessConnection
Set rsEmp = New ADODB.Recordset
With rsEmp
Set .ActiveConnection = cn1
.Source = "select * from tblEmployee where lEmployeeID = " & cboEmployee & ";"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With
sSig = "Sincerely, " & vbCrLf
sSig = sSig & rsEmp!sFirstName & " " & rsEmp!sLastName & vbCrLf
End If
'Signature
sSig = sSig & "Account Executive" & vbCrLf
sSig = sSig & vbCrLf
sSig = sSig & rsInfo!sAddress & vbCrLf
sSig = sSig & rsInfo!sCity & ", " & rsInfo!sState & " " & rsInfo!sZip & vbCrLf
sSig = sSig & "Phone: " & rsInfo!sPhone & " Ext." & Nz(rsEmp!sExt, "") & vbCrLf
sSig = sSig & "Toll Free: " & rsInfo!sTollFree & " Ext." & Nz(rsEmp!sExt, "") & vbCrLf
sSig = sSig & "http:\\

http://www.blahblahblah.com"

& vbCrLf
sSig = sSig & "mailto:" & rsEmp!sEmailAdd
If Not IsNull(cboPublications) Then
Set cn2 = CurrentProject.AccessConnection
Set rsPub = New ADODB.Recordset
With rsPub
Set .ActiveConnection = cn2
.Source = "SELECT * FROM tblPublication where lPubID = " & Nz(cboPublicationName, 0) & ";"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With
If rsPub.RecordCount > 0 Then sPub = rsPub!sPubName
End If
sGreet = "Dear " & Nz(txtTitle, "") & " " & txtFirstName & "," & vbCrLf
sGreet = sGreet & vbCrLf
sGreet = sGreet & Nz(rsInfo!sEmailNote1, "") & " "
sGreet = sGreet & Format(Nz(sPub, ""), "")
sGreet = sGreet & Nz(rsInfo!sEmailNote2, "") & " "
sGreet = sGreet & Nz(txtPageCount, "")
sGreet = sGreet & Nz(rsInfo!sEmailNote3, "") & " "
sGreet = sGreet & Nz(cboProductName, "")
sGreet = sGreet & Nz(rsInfo!sEmailNote4, "") & " "
sGreet = sGreet & "$" & Nz(txtUnitPrice, "") & ".00"
sGreet = sGreet & Nz(rsInfo!sEmailNote5, "") & " "
sGreet = sGreet & Nz(txtFollowUp, "")
sGreet = sGreet & Nz(rsInfo!sEmailNote6, "")
sGreet = sGreet & vbCrLf
sGreet = sGreet & vbCrLf

Set objEmail = obj.CreateItem(olMailItem)
objEmail.To = Nz(txtEmailAdd.Value, "")
objEmail.Subject = "Your article in " & Nz(sPub, "") & " on " & Nz(txtPublicationDate.Value)
objEmail.Body = sGreet & sSig
objEmail.Attachments.Add "C:\blah\1.jpg"
objEmail.Attachments.Add "C:\blah\2.jpg"
objEmail.Attachments.Add "C:\blah1.pdf"
objEmail.Display

Set rsInfo = Nothing
Set rsEmp = Nothing
Set rsPub = Nothing
Set cn = Nothing
Set cn1 = Nothing
Set cn2 = Nothing

Thanks,
-dimmech

 

[rac2]

dynamically change the code below

One way to do this is to use INCLUDEs and IF THEN blocks.

<%
Dim template_to_use
...
template_to_use = "Gold"
...
If template_to_use = "Gold" Then
%>
  <!-- #include virtual="/templates/gold.asp" -->
<%
ElseIf template_to_use = "RedFlag" Then
%>
  <!-- #include virtual="/templates/redflag.asp" -->
<%
Else  
%>
  <!-- #include virtual="/templates/standard.asp" -->
<%
End If
%>

Or use the more elegant SELECT CASE structure

<%
Dim template_to_use
...
template_to_use = "Gold"
...
Select Case template_to_use
  Case "Gold"
%>
  <!-- #include virtual="/templates/gold.asp" -->
<%
  Case "RedFlag"
%>
  <!-- #include virtual="/templates/redflag.asp" -->
<%
  Case Else  
%>
  <!-- #include virtual="/templates/standard.asp" -->
<%
End Select
%>

 

[dimmech]

Thanks rac2,

access 2003 ado but I can use your example.

As for editing or creating templates I thought it may be possible to create a cbobox driven sql query builder that I could merge with user input text fields and save the results into template file.

Every come across that?

-dimmech