Categories
Blog Posts

Daily Sales by Email

This post shows a fairly easy way to setup a VBScript that will email you a CSV file of the days sales. It requires no special programs to be installed. It does not require an ODBC DSN to be created. It can be run as a scheduled task so you get an email every day with a list of that day’s sales.

Implementation requires the following:

  1. Save the main script to a file DailySalesByEmail.VBS
  2. Edit the script to contain the settings necessary
  3. Create a batch file to run the script.

Configuring the Script

There are two sections of the script that need to be configured. One is the Spire database information, the second is your email settings.

This is the first section. Some notable points are the strDate parameter is configured to use today’s date. For testing with Inspire demo data, remove the apostrophe so that strDate = “2014-11-11” is executed. Secondly, the strDriver parameter is for a 32-bit ODBC driver. For the 64-bit ODBC driver, use the “(x64)” version (if installed).

	strFolderName = "C:\Users\peter\OneDrive\Documents\Development\Scripts\"
	strDate = year(date) & "-" & month(date) & "-" & day(date)

' for testing remove the apostrophe from the beginning of the next line
'       strdate = "2014-11-11"
        strFileName = "DailySales" & strDate & ".csv"

	strDriver = "PostgreSQL Unicode"
' for 64-bit use "PostgreSQL Unicode(x64)	
	strSpireDB = "INSPIRE33"
	strServer = "localhost"
	strSpirePort = ""
	strSpireUser = "spireapp" 
	strSpirePassword = "nv-Mj,mhJTxZ-0Tk"

The second section of configuration is for email settings. These will be the same as your Outlook client configuration. Note that if you are using gmail and have ‘2 factor authentication’ enabled, you will need to generate a special ‘App Password’. Instructions are here. You may want to consider creating a special gmail account just for this purpose as if you change your main email password, this App Password will no longer work.

SMTPServer = "smtp.gmail.com"
SMTPPort = 465
SMTPAuthentication = 1
SendUsing = 2
SendUserName = "peter@wright-stuff.com"
ToEmailAddress = "peter@wright-stuff.com"
SendPassword = "specialPassword"
UseSSL = true

Creating the Batch File

The final step is to create a batch (.BAT) file to run the script. Here the batch file will be different depending on whether you are using 32-bit or 64-bit ODBC drivers. Put the .BAT file in the same folder as the script file.

For 32-bit ODBC drivers:

%WINDIR%\syswow64\cscript.exe DailySalesByEmail.vbs

For 64-bit ODBC drivers:

%WINDIR%\system32\cscript.exe DailySalesByEmail.vbs

The Script

Copy and paste the following into a text file and save the file as “DailySalesByEmail.vbs” Both files should be in the same folder.

	dim conn               'adodb connection
	Dim Connect            'adodb connection string
	dim rs1                'adodb recordset
	dim strSQL             'recordset SQL string
        dim objFSO             'file system object
        dim strOutput          'output string
	dim objFile            'file object
        dim strFileName        'file name string
        dim strDate            'today's date formatted as YYYY-MM-DD
	dim strFolderName      'folder name string
	dim fld                'recordset field
	dim strSpireDB         'spire database name
        dim strDriver          'Postgress driver to use
        dim strServer          'Spire Server
        dim strSpirePassword   'postgress password (usually the password for the user 'spireapp')
        dim strSpireUser       'postgress user ID (usually 'spireapp')
'----------------------------------------------------------------------------------------------------
'  Configurable Settings
'----------------------------------------------------------------------------------------------------
	strFolderName = "C:\Users\peter\OneDrive\Documents\Development\Scripts\"
	strDate = year(date) & "-" & month(date) & "-" & day(date)

' for testing remove the apostrophe from the beginning of the next line
'       strdate = "2014-11-11"
        strFileName = "DailySales" & strDate & ".csv"

	strDriver = "PostgreSQL Unicode"
' for 64-bit use "PosgreSQL Unicode(x64)	
	strSpireDB = "INSPIRE33"
	strServer = "localhost"
	strSpirePort = ""
	strSpireUser = "spireapp" 
	strSpirePassword = "nv-Mj,mhJTxZ-0Tk"

' connect to database with adodb
'-------------------------------
	set conn = createobject("adodb.connection")
        Set Rs1 = CreateObject("adodb.recordset")
	Connect = "DRIVER=" & strDriver & ";" & _
		  "DATABASE=" & strSpireDB & ";" & _
		  "SERVER=" & strServer & ";" & _
		  "PORT=" & strSpirePort & ";" & _
		  "UID=" & strSpireUser & ";" & _
		  "PWD=" & strspirepassword & ";"
	conn.open Connect

' open query with daily sales
'--------------------------------
	strSQL = "SELECT invoice_no, invoice_date,cust_no, cust_name, territory_code, salesperson_no, subtotal, total_discount," & _
			"freight, sales_tax, total FROM sales_history " & _
			"where invoice_date = '" & strDate & "'"
        Rs1.open  strSQL,conn,adopenforwardonly

' open output file
'----------------------------------
	set objFSO = createobject("Scripting.FileSystemObject")
	Set objFile = objFSO.CreateTextFile(strFolderName & strFileName, True)

' loop through fields to build and out the column names
'----------------------------------------------
	for each fld in rs1.fields
	      strOutput = strOutput & """" & fld.name & """" & ","
	next 
	objFile.Write stroutput & chr(13) & chr(10)

' loop through the records and output them	
'------------------------------------------
       Do While Rs1.EOF = False
		stroutput = ""
		for each fld in rs1.fields
			strOutput = strOutput & """" & fld.value & """" & ","
		next 
		objFile.Write stroutput & chr(13) & chr(10)
           Rs1.MoveNext
       Loop
       
' clean up
'-----------------
	rs1.close
	objfile.close
        conn.close

'------------------------------------------------
' email the file
'------------------------------------------------

dim SMTPServer
dim SMTPPort
dim SMTPAuthentication
dim SendUserName
dim ToEmailAddress
dim SendPassword
dim UseSSL
dim SendUsing

'--------------------------------------------------------------------------------
' Configurable Settings: see notes if using gmail and 2 factor authentication
'--------------------------------------------------------------------------------

SMTPServer = "smtp.gmail.com"
SMTPPort = 465
SMTPAuthentication = 1
SendUsing = 2
SendUserName = "peter@wright-stuff.com"
ToEmailAddress = "peter@wright-stuff.com"
SendPassword = "specialpassword"
UseSSL = true
'---------------------------------------------------------------------------------

Set MyEmail=CreateObject("CDO.Message")

MyEmail.Subject="Daily Sales"
MyEmail.From = SendUserName
MyEmail.To = ToEmailAddress
MyEmail.TextBody="Here are today's sales."

MyEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = SendUsing
MyEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTPServer
MyEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = SMTPPort 
MyEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = SMTPAuthentication
MyEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusername") = SendUserName
MyEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = SendPassword
MyEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True

MyEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60


' add attachment and send email
'------------------------------
MyEmail.AddAttachment strFolderName & strFileName

MyEmail.Configuration.Fields.Update
MyEmail.Send

set MyEmail=nothing

Leave a Reply

Your email address will not be published. Required fields are marked *