Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2004

    Unanswered: vb script to refresh & send excel pivot table via email

    Hi I have a vb script which currently open a specified excel file refresh the pivot tables within it, then sends it via email to the recipients.

    The problem I have is that the source excel file and the emailed attachment become corrupt with a "unable to read file" error message in excel.

    The following is the code of the vb script.


    sub xfile()

    file = "Sales.xls" ' a XLS file
    OutFile = (GetPath+file)

    end sub

    Sub excel()

    DIM objXL, objWb, objR ' Excel object variables
    DIM Title, Text, tmp, i, j, name

    ' create an Excel object reference
    Set objXL = WScript.CreateObject ("Excel.Application")

    objXL.Visible = false ' show window

    ' Load the Excel file from the script's folder
    Set objWb = objXl.WorkBooks.Open(GetPath+file)


    input1 = objxl.range("B2")

    year = (Left(smsinput1, 4))
    period = (trim(right(smsinput1, 2)))
    set objWB = Nothing

    objXl.Quit() ' Quit Excel

    Set objXL = Nothing

    end sub

    Sub mailitem()

    Const cdoSendUsingMethod = "", _
    cdoSendUsingPort = 2, _
    cdoSMTPServer = ""

    '// Create the CDO connections.
    Dim iMsg, iConf, Flds
    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")
    Set Flds = iConf.Fields

    '// SMTP server configuration.
    With Flds
    .Item(cdoSendUsingMethod) = cdoSendUsingPort

    '// Set the SMTP server address here.
    .Item(cdoSMTPServer) = "smtpserver"
    End With

    '// Set the message properties.
    With iMsg
    Set .Configuration = iConf
    .To = ""
    '.CC = ""
    .From = ""
    .Subject = "Report"
    .TextBody = " Attached is the Daily Report
    End With

    '// An attachment can be included.

    OutFile = (GetPath+file)

    iMsg.AddAttachment OutFile

    '// Send the message.
    iMsg.Send ' send the message.

    end sub

    Function GetPath
    ' Retrieve the script path
    DIM path
    path = WScript.ScriptFullName ' Script name
    GetPath = Left(path, InstrRev(path, "\"))
    End Function

    Does anybody know what is wrong with the script to cause it to corrupt the excel file?

  2. #2
    Join Date
    Jul 2004
    Problem was with Excel rather than the script. installing SP3 for office 2000 fixed the issue

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts