Download E-Statement Solution Source Code for Free
You can now Download E-Statement Solution Source Code for Free. This solution was developed through a series of posts on this site. For those who feel/felt a bit lazy to follow the E-Statement Development Series in VB 6 you can now Download the source code plus the database for free. You just need to register and we will send you the download link to your confirmed email address.
Here is a summary on what this series has been about:
- Basics of E-statement Solution
- Setting up your development environment
- System Database and Tables
- Modules and Menus
- E-statement Login System
- Send Mail Using Gmail SMTP Server
- E-Statement User Management
- E-Statement Client Management
- E-Statement SMTP Configuration and Server Pinging
- E-Statement Email Configuration Form
- E-Statement Email Body and Mailer
How To Run The Source Code
Before running the source code make sure you have VB 6 and SQL Server 2008 r2(Express Version Will Work Fine) installed. Download the source code plus the database
Restore the Microsoft SQL “SmartMail.bak” Database Backup in SQL Server 2008 R2
- Login to Microsoft SQL Management Studio using your credentials.
- Right Click on Databases -> Restore Database
- On the next Restore Database window – select the From Device option and browse for the “SmartMail.bak” file
- Click on Add button, browse for the Smartmail.bak file you have downloaded, click Ok when done
- Tick on the restore and click ok to restore the Smart Mail Database
Create an ODBC Connection, Make the “SmartMail” Database as Default
- Go to Control Panel -> Administrative Tools -> Choose ODBC Data Sources (32-bit)
- Under the User DNS click the Add button
- Select SQL Server database and click finish
- Type the Name (SmartMail), Description (Database Connection to Smart Mail) and Server (Your SQL Instance name). Click Next
- Provide login credentials to the SQL Server . Choose SQL Authentication option. Type sa for username and the password for sa. Click Next
- Change the default database to SmartMail database that we just restored. Click Next
- Click Finish and Test Data Source
Change the modConnection Module
Replace the SmartMail (see line 10) and the myPass variable with your ODBC Name and SQL sa password(see line 1) respectively.
Public Const myPass As String = "saSQLPassword" 'Function to Open a Connection to SQL Through Data Source Public Function OpenDB() As Boolean Dim isOpen As Boolean Dim ANS As VbMsgBoxResult isOpen = False On Error GoTo err Do Until isOpen = True CN.CursorLocation = adUseClient CN.Open "SmartMail", "sa", myPass 'SmartMail is the Data Source Name.Change if needed isOpen = True Loop OpenDB = isOpen Exit Function err: ANS = MsgBox("Error Number: " & err.Number & vbCrLf & "Description: " & err.Description, _ vbCritical + vbRetryCancel) If ANS = vbCancel Then OpenDB = vbCancel ElseIf ANS = vbRetry Then OpenDB = vbRetry End If End Function
Using the System
Unzip the SmartMailSource.zip to a folder, run the SmartMail project in VB 6
Login to the system
On the login screen, choose Admin from the dropdown and login with password ‘a’ without the single quotes. The password for all the other usernames is the same.
Main Menu Screen
Once we are logged in to the system we will have the main menu screen.
- Manage Users – To Manage System Users
- Manage Customer Emails – Manage Customer Details
- Application Settings – Manage SMTP Server Settings and Email Body Settings
- Send E-Statements – Send Mass E-statements
- File – Logout or Exit
One the Main Menu, click on Manage Users, the following screen will appear.
- To Manage and existing username, select the user under the drop down box and you can update the details and click on the update button.
- To create a new username, fill in the Username , Full name, type a password and confirm. To make the username have admin rights (To Mange Users and Application Settings) check the ‘Make User Administrator’ checkbox and click the ADD button.
- To disable a user select a user under the drop down box and check the ‘Disable User’ checkbox and click update. The user will not be able to login t the E-Statement System.
- To Delete a user, select the username you want to delete and click the DELTE button. The username will be completely deleted.
Click Manage Customer Emails to manage the customer list. we can ADD,SEARCH,UPDATE and DELETE customers in the system.
- To search for a username, select the search criteria under the Search By drop down box. We can search by Account Number, Name or Email address of the client. The click on the SEARCH button.
- To make any changes on the existing clients, search for the client, make the necessary change and then click on the UPDATE button.
- To add a new client, type the Account No, Client Name and Email then click on the ADD button.
- To delete a client, search for the client and then click on the DELETE button.
Setup SMTP Server
Before we are able to send any emails, we need to configure our SMTP server details. In this E-Statement we are using Google/Gmail SMTP server to send the emails. For more details refer to this article. You can use any other SMTP Server to send the emails. Just set up the SMTP settings according to your SMTP Server settings.
Click on Application Setting on the Main Screen, on the screen that appears click on SMTP button.
The following screen will appear.
Make appropriate changes to the configuration and click on the Save Configuration File button
Server: IP/SMTP Server Name
SMTP Users SSL: True or False
SMPT Authentication: 1 or 0
Username: SMTP Username(Senders email address)
Password: Password for you email
Send Using: We have 3 options in this the one the application uses is cdoSendUsingPort = 2
- cdoSendUsingPickup (1)
- cdoSendUsingPort (2)
- cdoSendUsingExchange (3)
Ping SMTP Server and Send a Test Mail
You can click on the Ping button to see if your SMTP Server is reachable. Send a test mail to see if the SMTP Server settings a working correctly.
Setup Email Body and Advert
We can configure 3 main parts of the email under this section.
- Email Subject – Subject of the Email
- Header Logo – Company header logo
- Advert Image – Advert image/ Promotions
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
Note: The size of your logo should be the same
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
Note: The size of your advert should be the same
Sample Advert Image
Prepare your PDFs Statements
The E-Statements will be sent inform of a PDF file. Note that the PDF of a client should be named as the exact account number of the client. When the system pick a PDF from a folder it will use the PDF name to query for the client details in the clients table. It will pick the client name and email address and then send the PDF Statement as an attachment to the clients email address. Once the PDF has been sent, it will be moved to the sent item folder which will be created by the system.
The screen short below shows the PDF name should be = Account Number
Once we have our PDFs Statements ready we can begin sending them using the E-Statement solution.
Depending on your network speed and place of work, you need to consult with the network administrator to see how many E-Statements you can fire out for a particular period of time. For example you can send 500 E-statement Batch during pick hours and 1000 statements batches during off pick hours.
To send batches create a folder and put your PDFs inside that you want to send. Login to the system. On the main menu click on the Send E-Statements and the following window will appear.
Browse for the folder where the PDFs statements are located and double click it , so that the folder icon look like opened.
Click on the Start Sending button and let the system send the E-Statements automatically. The time taken to send the E-statements will depend on various factors like:
- PDFs Size
- Number of PDFS in a Batch
- Network speed
- Computer speed