Create HTML Email Configuration Form for the E-statement
Our E-statement application will be used to send clients Bank Statements at certain intervals and some configurations needs to be changed when sending these mail.
In the next article we are going to create HTML email body for the e-statement but for this article we are going to manage the following settings for the email body.
- Email Body Sections
- Email Subject
- Company Header Logo
- Company Adverts Section
- Disclaimer
Email Body Sections
Apart from the PDF Statement attachment to the email, the email has a section for Adverts. We can include adverts at the bottom of the email body as part of the e-statement.
Company Header Logo
This section will hold the company logo. The company logo can be changed by providing a different logo using this configuration form.
Logo Format = PNG
Width = 632
Height = 57
Sample Logo
Company Adverts Section
The advert section is provisioned to add adverts as part of the email body. It is inform of an image.
Advert Format = PNG
Width = 566
Height = 223
Sample Advert Image
Once we have understood the HTML Email sections we can go ahead and create a configuration form which will allow us to set up the variables.
SQL App Settings Table Columns
We need to make some changes to our App Settings Table in the Smart Mail SQL database as below:
HTML Email Configuration Form
Create a Form and name it “frmEmailBody” with a caption of “Email Body Configuration”
Add the following controls on the form.
Control | Name | Caption |
Frame | Frame1 | E-Statement Message Body Configuration |
Common Dialog | cdEmail | |
Common Dialog | cdStatement | |
Text Box | txtSubject | |
Text Box | txtEmailAdvertImage | |
Text Box | txtEmailStatementLogo | |
Command Button | cmdSave | &Save |
Command Button | cmdExit | E&xit |
Command Button | cmdEMailAdvert | Browse |
Command Button | cmdStatementAdvert | Browse |
Check Box | CheckChangeLogo | Change Logo |
Label | Subject | |
Label | Advert Image | |
Label | Header Logo | |
Label | (e.g Your July e-Statement) | |
Label | Bottom Advert | |
Label | Header Logo |
SOURCE CODE
Once we are done with setting up the form. We can now add code to it. Copy and paste the code below to your form and test.
'How to develop a commercial e-Statement solution in vb6 'eStatement Application Development 'Create HTML Email Configuration 'ping using vb6 'Send Mail in VB6 Using Gmail SMTP, Ping Using VB6,Create HTML Email 'www.smarttechdiary.com Private Sub CheckChangeLogo_Click() 'Enable/Disable Changing Of Header Logo If CheckChangeLogo.Value = Checked Then txtEmailStatementLogo.Enabled = True cmdStatementAdvert.Enabled = True txtEmailStatementLogo.BackColor = &H80000005 Else txtEmailStatementLogo.Enabled = False cmdStatementAdvert.Enabled = False txtEmailStatementLogo.BackColor = &H8000000B End If End Sub Private Sub cmdEMailAdvert_Click() 'Set the Email Body Advert On Error Resume Next ' Clear errors err.Clear ' Use this common dialog control throughout the procedure With cdStatement ' Raises an error when the user press cancel .CancelError = True ' Set the file extensions to allow .Filter = "Image Files (JPEG,PNG)|*.jpg;*.jpeg;*.png;" ' Display the open dialog box. .ShowOpen txtEmailAdvertImage = .FileName ' Ignore this if the user has canceled the dialog If err <> cdlCancel Then VerifyEmailImageSize End If End With End Sub Private Sub cmdExit_Click() Unload Me End Sub Private Sub cmdSave_Click() ' Save E-Statement Email Body Settings '----------------------------------------------------------------------------------------------- On Error GoTo Error 'Check User Input If txtEmailAdvertImage.Text = "" Then Exit Sub If txtEmailStatementLogo.Text = "" Then Exit Sub If txtSubject.Text = "" Then Exit Sub Dim rsEmailBody As New ADODB.Recordset With rsEmailBody ' EmailSubject '----------------------------------------------------------------------------------------------- .Open "SELECT * FROM AppSettings WHERE SettingID=8 ", CN, adOpenDynamic, adLockOptimistic If Not rsEmailBody.EOF Then rsEmailBody("SettingValue") = txtSubject.Text .Update End If .Close ' EmailAdvertImage '----------------------------------------------------------------------------------------------- .Open "SELECT * FROM AppSettings WHERE SettingID=9 ", CN, adOpenDynamic, adLockOptimistic If Not rsEmailBody.EOF Then rsEmailBody("SettingValue") = txtEmailAdvertImage.Text .Update End If .Close ' EmailStatementLogo '----------------------------------------------------------------------------------------------- .Open "SELECT * FROM AppSettings WHERE SettingID=10 ", CN, adOpenDynamic, adLockOptimistic If Not rsEmailBody.EOF Then rsEmailBody("SettingValue") = txtEmailStatementLogo.Text .Update End If .Close End With MsgBox "Updated", vbInformation Exit Sub Error: MsgBox (err.Description) Unload Me End Sub Private Sub cmdStatementAdvert_Click() 'Set the Email Body Header On Error Resume Next ' Clear errors err.Clear ' Use this common dialog control throughout the procedure With cdStatement ' Raises an error when the user press cancel .CancelError = True ' Set the file extensions to allow .Filter = "Image Files (JPEG,PNG)|*.jpg;*.jpeg;*.png;" ' Display the open dialog box. .ShowOpen txtEmailStatementLogo = .FileName ' Ignore this if the user has canceled the dialog If err <> cdlCancel Then VerifyStatementImageSize End If End With End Sub Function VerifyEmailImageSize() 'Function to Verify Email Advert Image Size On Error GoTo ImageError Dim myPic As StdPicture, picWidth As Long, picHeight As Long Set myPic = LoadPicture(txtEmailAdvertImage.Text) ' supply valid path/filename picWidth = ScaleX(myPic.Width, vbHimetric, vbPixels) picHeight = ScaleY(myPic.Height, vbHimetric, vbPixels) lblSize.Caption = picWidth & "x" & picHeight If picWidth <> "566" And picHeight <> "223" Then MsgBox "Inalid Image Size", vbCritical, "Invalid Email Body Advert" Exit Function ' handle errors should loading the picture cause an error ImageError: MsgBox (err.Description) End Function Function VerifyStatementImageSize() 'Function to Verify Header Logo Image Size On Error Resume Next err.Clear Dim myPic As StdPicture, picWidth As Long, picHeight As Long Set myPic = LoadPicture(txtEmailStatementLogo.Text) ' supply valid path/filename picWidth = ScaleX(myPic.Width, vbHimetric, vbPixels) picHeight = ScaleY(myPic.Height, vbHimetric, vbPixels) lblStatementSize.Caption = picWidth & "x" & picHeight If picWidth <> "632" And picHeight <> "57" Then MsgBox "Inalid Image Size", vbCritical, "Invalid Email Body Advert" Exit Function ' handle errors should loading the picture cause an error End Function Private Sub Form_Load() 'Disable Header Logo Editing txtEmailStatementLogo.Enabled = False cmdStatementAdvert.Enabled = False 'Load E-Statement Message Body Settings From Database txtSubject.Text = getValueAt("SELECT SettingValue FROM AppSettings where SettingID=8", "SettingValue") txtEmailAdvertImage.Text = getValueAt("SELECT SettingValue FROM AppSettings where SettingID=9", "SettingValue") txtEmailStatementLogo.Text = getValueAt("SELECT SettingValue FROM AppSettings where SettingID=10", "SettingValue") End Sub
[…] Note that the E-Statement has different sections, see this post for more E-statement Email Body Sections […]