Emailing an excel file automatically at preset time

[shreeram.maroo]

Hi,

I have an excel file and i need to send this file to a specified mail ID everyday at let's say 9 pm Indian Time. Is there any way i can automate this ?

Regards
Shreeram

[HansV]

Will the workbook be open all the time?

[shreeram.maroo]

No, it may not be

[HansV]

Will the computer that stores the file be on 24 hours per day?

[shreeram.maroo]

Not 24 hours. But i can ensure that it is on at the time when i need to share the file.

[HansV]

In a standard module:

Code: Select all

Public NextTime As Date

Sub CheckSend()
    If ThisWorkbook.CustomDocumentProperties("LastDate") < Date Then
        If Hour(Now()) >= 21 Then
            ThisWorkbook.SendMail Recipients:="someone@somewhere.in", Subject:="Daily Report"
            ThisWorkbook.CustomDocumentProperties("LastDate") = Date
            NextTime = Date + 1 + TimeSerial(21, 0, 0)
        Else
            NextTime = Date + TimeSerial(21, 0, 0)
        End If
    Else
        NextTime = Date + 1 + TimeSerial(21, 0, 0)
    End If
    Application.OnTime EarliestTime:=NextTime, Procedure:="CheckSend"
End Sub

In the ThisWorkbook module:

Code: Select all

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Application.OnTime EarliestTime:=NextTime, Procedure:="CheckSend", Schedule:=False
End Sub

Private Sub Workbook_Open()
    Dim LastDate As Date
    On Error Resume Next
    LastDate = Me.CustomDocumentProperties("LastDate")
    If Err Then
        Me.CustomDocumentProperties.Add Name:="LastDate", LinkToContent:=False, _
            Type:=3, Value:=Date - 1
    End If
    Call CheckSend
End Sub

Create a scheduled task in Windows that opens the Excel workbook. See How to run Excel file from Windows 10 Task Scheduler for instructions.

Remark: you should also start your default email client, otherwise, the message might remain in the Outbox folder.

[shreeram.maroo]

Thanks Hans, will try this.