Email Excel Workbook Automatically Daily

Discussion in 'Microsoft Office' started by Arnold132, Jan 13, 2018.

  1. Arnold132

    Arnold132

    Joined:
    Jan 13, 2018
    Likes Received:
    0
    Hello,

    I have an excel workbook file on a shared drive that multiple departments update daily. I need to figure out a way to have that file emailed to a list of people daily at a specified time.
    I have tried a couple codes in MVB from other forums but they are not exactly what I'm needing.

    -Thank you
     
    Arnold132, Jan 13, 2018
    #1
    1. Advertisements

  2. Arnold132

    AmjiBhai

    Joined:
    Feb 21, 2018
    Likes Received:
    37
    Hi,

    Lets do it in just two steps...

    run the following scheduler code and confirm if its working...
    Later we will insert the email routine..or you can do that yourself.


    Dim TimeToRun

    Sub auto_open()
    Call ScheduleEmailing
    End Sub

    Sub ScheduleEmailing()
    timestring = Format(Sheets("sheet1").Range("D7"), "00") & ":" & Format(Sheets("sheet1").Range("E7"), "00") & ":" & Format(Sheets("sheet1").Range("F7"), "00")
    TimeToRun = Now + TimeValue(timestring)
    Application.OnTime TimeToRun, "Emailing"
    End Sub

    Sub Emailing()

    Workbooks("run-code-every-hour-minute-or-second.xlsm").Activate
    'Calculate

    Sheets("Sheet1").Range("c1").Value = Sheets("Sheet1").Range("d1").Value

    Sheets("Sheet1").Range("D10") = "I am working " & Format(Now, "dd-mmm-yyyy hh:mm AM/PM")
    On Error Resume Next
    ActiveWorkbook.Save
    On Error GoTo 0

    Call ScheduleEmailing
    End Sub
     
    AmjiBhai, Feb 24, 2018
    #2
    Becky, Arnold132 and Ian like this.
    1. Advertisements

  3. Arnold132

    AmjiBhai

    Joined:
    Feb 21, 2018
    Likes Received:
    37
    upload_2018-2-24_10-19-47.png

    Please remember your sheet1 must look like this.
     
    AmjiBhai, Feb 24, 2018
    #3
    Arnold132 and Ian like this.
  4. Arnold132

    AmjiBhai

    Joined:
    Feb 21, 2018
    Likes Received:
    37
    AmjiBhai, Feb 26, 2018
    #4
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.