How to send an email at specific time of a day in Excel?

In some cases, you may need to delay sending an email at a specific time during your work. Do you know how to achieve this? This article is going to show you a method of sending an email at the specific time of a day in Excel.

Send an email at specific time of a day with VBA code


Send an email at specific time of a day with VBA code


The following VBA codes can help you send an email at the specific time of a day in Excel. Please do as follows.

1. Press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then copy below VBA code into the Module window.

VBA code 1: Send an email at specific time of a day in Excel

Sub SendEmail()
'Updated by Extendoffice 2018/3/5
Set olApp = CreateObject("Outlook.application")
Set objMail = olApp.CreateItem(olMailItem)
With objMail
 .Display
 .To = "Email address"
 .Subject = "Send email"
 'HTML for fun
 '.BodyFormat = olFormatHTML
 .HTMLBody = "<HTML><H2>Email Body</BODY></HTML>"
 '.Send
 
End With
End Sub

3. Double click ThisWorkbook in the left pane to open the ThisWorkbook (Code) window. And then copy below VBA code 2 into the window. See screenshot:

VBA code 2: Send an email at specific time of a day in Excel

Private Sub Workbook_Open()
'Updated by Extendoffice 2018/3/5
    Application.OnTime TimeValue("11:00:00"), "SendEmail"
End Sub

Note: In the code 2, please specify the sending time as you need.

4. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.

5. Click File > Save As > Browse.

6. In the Save As dialog box, pleas choose a folder to save the workbook, name it as you need, select Excel Macro-Enabled Workbook format from the Save as type drop-down list, and finally click the Save button. See screenshot:

From now on, as soon as the specific time arriving, the certain email will display automatically. Please click the Send button to send this email.

Related articles:


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    hiba · 2 years ago
    I have a list of email addresses in excel that i need to send emails to The subject and body are in cells besides the email address and i have the script below but i need to send 100 emails per 1 hour how i can and what the code the use for it ,please your support



    Sub SendEm()
    Dim i As Integer, Mail_Object, Email_Subject, o As Variant, lr As Long
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    Set Mail_Object = CreateObject("Outlook.Application")
    For i = 2 To lr
    With Mail_Object.CreateItem(o)
    .Subject = Range("B" & i).Value
    .To = Range("A" & i).Value

    .Body = Range("C" & i).Value
    .attachments.Add (Sheets("Sheet1").Range("H" & i).Text)
    .attachments.Add (Sheets("Sheet1").Range("I" & i).Text)
    .attachments.Add (Sheets("Sheet1").Range("J" & i).Text)
    .attachments.Add (Sheets("Sheet1").Range("K" & i).Text)
    .Send

    '.display 'disable display and enable send to send automatically
    End With
    Next i
    MsgBox "E-mail successfully sent", 64
    Application.DisplayAlerts = False
    Set Mail_Object = Nothing
    End Sub