1 Reply Latest reply: May 3, 2016 8:35 AM by tom herman RSS

    save an excel file as date.xlsx

    tom herman

      Hello.

      i have a macro that exports a table into an excel file and i need to save it as current date.xlsx and send it as an email.

      please help me.

       

      this is what i have tried:

      Sub SendMail

         

          

           'Create the Excel spreadsheet

           Set excelFile = CreateObject("Excel.Application")

           excelFile.Visible = true

        Path = "D:\Qlikview\DEV\"

        'FileName=  Text(ActiveDocument.Evaluate("=Date(Today()-1,'DDMMYYYY')"))

        FileName = ActiveDocument.Evaluate("=Date(Today(),'DDMMYYYY')") & ".xlsx"

        msgbox(Path)

        msgbox(FileName)

          ' name=ActiveDocument.Evaluate("=Date(Today()-1,'DDMMYYYY')")

           'excelFile.Name = ActiveDocument.Evaluate("=Date(Today(),'DDMMYYYY')")

          

           'fileName = excelFile.Name

           ' msgbox(Year(now())&month(now())&day(now()))

             'Set the path where the excel will be saved

             filePath = string.concatenate(Path,FileName)

            msgbox(filePath)

            'msgbox(Year(now())&month(now())&day(now()))

           

           'Create the WorkBook

           Set curWorkBook = excelFile.WorkBooks.Add

           'Create the Sheet

           Set curSheet = curWorkBook.WorkSheets(1)

           'Get the chart we want to export

           Set tableToExport = ActiveDocument.GetSheetObject("CH686")

           Set chartProperties = tableToExport.GetProperties

           tableToExport.CopyTableToClipboard true

           'Get the caption

           chartCaption = tableToExport.GetCaption.Name.v

           'MsgBox chartCaption

           'Set the first cell with the caption

           curSheet.Range("A1") = chartCaption

           'Paste the rest of the chart

           curSheet.Paste curSheet.Range("A2")

           excelFile.Visible = true

           'Save the file and quit excel

          

           curWorkBook.SaveaAs filePath

          

           curWorkBook.Close

           excelFile.Quit

           'Cleanup

           Set curWorkBook = nothing

           Set excelFile = nothing

          

           Set objMsg = CreateObject("CDO.Message")

      Set msgConf = CreateObject("CDO.Configuration")

      ' Server Configuration

      msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

      msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "********"

      msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

      msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 0

      msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = 0

      msgConf.Fields.Update

      ' Email

      objMsg.Subject = "******"

      objMsg.To = "*****"

      objMsg.From = "*****"

      objMsg.AddAttachment filePath

      objMsg.HTMLBody = "<html><body>test mail from*****<br><img src=""******""></body></html>"

      Set objMsg.Configuration = msgConf

      ' Send

      objMsg.Send

      Msgbox("Email Sent")

      ' Clear

      Set objMsg = nothing

      Set msgConf = nothing

      End Sub