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

    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.



    xfile()
    excel()
    MailItem()


    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)

    objwb.RefreshAll


    input1 = objxl.range("B2")

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


    objwb.save
    objwb.close
    set objWB = Nothing

    objXl.Quit() ' Quit Excel

    Set objXL = Nothing



    end sub

    Sub mailitem()



    Const cdoSendUsingMethod = "http://schemas.microsoft.com/cdo/configuration/sendusing", _
    cdoSendUsingPort = 2, _
    cdoSMTPServer = "http://schemas.microsoft.com/cdo/configuration/smtpserver"

    '// 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"
    .Update
    End With

    '// Set the message properties.
    With iMsg
    Set .Configuration = iConf
    .To = "recipient@email.com"
    '.CC = "recipient@email.com"
    .From = "sender@email.com"
    .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
    Posts
    10
    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
  •