Posted on 2004-11-08
Last Modified: 2010-05-02
I have a report that is to be run for 10 states and after I run it and send it to the user , the user has to mail it through fedex to the 15 differernt states, within 2 days. Is there something that I could do (some people talk about web reporting etc..) so that the process of mailing within  2 days does not fall on the user..
Currently I am using sql and VB to produce the report in excel format ; I have scheduled  the report as a job that runs on a specific day of very month.. Kindly let me know what are my options… and the issues involved in it…
Question by:Sara_j_11
    LVL 5

    Expert Comment

    If you have it in Excel why not email it?  

    You can get VB to build an email using Outlook and send to each person in turn (including your attachment).  

    A little security can be added by giving the Excel file a password that is required to open it (though cracking this isn't out of the question, it justs prevents a casual attempt).


    Web reporting is also an option assuming you have a webserver to do it from but if the report content is confidential you will have to set up security (logins/passwords) or use encrypted 'keys' or something to ensure only those that should see the report do.


    Author Comment

    yes the report is very confidential . Can u pl. tell me more about use encrypted 'keys' or something to ensure only those that should see the report do.
    This is the procedure that I us to save the file to an excel format. Can u pl. pate the code for emailing it using security so that I can follow you better?

    Private Sub WriteExcelReport(ByVal sFileName As String)
       'remove_file (sFileName)
         'Create a new instance of Excel
       Dim oExcel As Object
       Dim oBook As Object
       Dim oSheet As Object
       Set oExcel = CreateObject("Excel.Application")
       Dim numberOfRows As Integer
       Dim rowNumber As Integer
       Dim x As Integer
       Dim Varchar As String
       Dim Varchar1 As String
       Dim aseries As String
       'Open the text file
       Set oBook = oExcel.Workbooks.Open(sFileName)

       'Save as Excel workbook and Quit Excel
       oBook.SaveAs sFolderLoc & "\G-Reports_" & Format(Now, "mm""-""dd""-""yyyy") & ".xls", xlWorkbookNormal
       Set oExcel = Nothing
        remove_file (sFileName) 'this removes the csv file so that I only  see  the excel file
    End Sub


    Author Comment

    I am just an intern in the company that I am working for.. COuld you pl. suggest some articcles to read about web server (web reporting)
    LVL 5

    Accepted Solution


    If the contents are very confidential then email isn't really secure.  

    There are things you can do to encrypt the file or the contents before sending it via email but the users at the other end would have to decrypt them again.

    A web-based solution sounds better to me (you get to retain all the control of the data - they come to you for it) but I don't do those sort of developments so I can't suggest how best to proceed.  We'd hire a third party to handle something like this.

    I am sure other will know the relevant links for you.

    Good luck

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Introduction In a recent article ( for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
    When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now