Emailing an excel file automatically at preset time

shreeram.maroo
2StarLounger
Posts: 155
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Emailing an excel file automatically at preset time

Post by 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

User avatar
HansV
Administrator
Posts: 72864
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Emailing an excel file automatically at preset time

Post by HansV »

Will the workbook be open all the time?
Regards,
Hans

shreeram.maroo
2StarLounger
Posts: 155
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re: Emailing an excel file automatically at preset time

Post by shreeram.maroo »

No, it may not be

User avatar
HansV
Administrator
Posts: 72864
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Emailing an excel file automatically at preset time

Post by HansV »

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

shreeram.maroo
2StarLounger
Posts: 155
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re: Emailing an excel file automatically at preset time

Post by shreeram.maroo »

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

User avatar
HansV
Administrator
Posts: 72864
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Emailing an excel file automatically at preset time

Post by 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.
Regards,
Hans

shreeram.maroo
2StarLounger
Posts: 155
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re: Emailing an excel file automatically at preset time

Post by shreeram.maroo »

Thanks Hans, will try this.