locked
Excel file automate excel RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I have an excel file which updates data in sheet when open. This data is pulled from sql server database.

    I would like to make this automate. I want this file to be store in a location and daily morning(ex at 6 am) this automatically pulls the data from sql server(which already doing when open the file, but manual work for which we need to open the file manually) and save the file at same location and then close it and email this file to me.

    is there a way out for this?

    Sunday, November 10, 2013 4:08 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    You can use the windows Task schedule for opening the file. Then use the event before close and call the following subrotine for sending it as attachment.

    Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Call CreateEmail

    ThisWorkbook.Close savechanges:=True

    End Sub

    'Add Reference to outlook library in your project

    Sub CreateEmail()

    Dim OlApp As Object
    Dim OlMail As Object

    Set OlApp = CreateObject("Outlook.Application")
    Set OlMail = OlApp.createitem(olmailitem)

    OlMail.Subject = "Test"
    OlMail.To = "youremail@xxx.com"
    OlMail.Attachments.Add ThisWorkbook.Path & "/" & ThisWorkbook.Name

    OlMail.send


    End Sub

    Guy Zommer


    Monday, November 11, 2013 6:20 AM