Sending email from excel automatically based on date

Question

I have an excel sheet with dated scheduling information. I would like to send daily updates on how many calls and how many appointments have been scheduled every day. The spreadsheet looks as follows:

Date        Scheduled     Called    Notes
07/06/2015    0             5        None
07/07/2015    5            12        None
07/08/2015    2            10        None

I am trying to write a program that, say on 7/06/2015, an email will be generated with that days scheduled, calls, and notes in the body and automatically sent. Is this possible?

Solution

Here's what I think could be a solid start. You'll obviously have to resolve what email address the message should be sent to and how to format the body and whatnot.

The range given to r was based on the sample data you provided, which occupied A2-A4, but change this to whatever is correct.

Option Explicit

Sub email()

    Dim r As Range
    Dim cell As Range

    Set r = Range("A2:A4")

    For Each cell In r

        If cell.Value = Date Then

            Dim Email_Subject, Email_Send_From, Email_Send_To, _
            Email_Cc, Email_Bcc, Email_Body As String
            Dim Mail_Object, Mail_Single As Variant

            Email_Subject = "subject"
            Email_Send_From = "[email protected]"
            Email_Send_To = "[email protected]"
            Email_Cc = "[email protected]"
            Email_Bcc = "[email protected]"
            Email_Body = "body"

            On Error GoTo debugs
            Set Mail_Object = CreateObject("Outlook.Application")
            Set Mail_Single = Mail_Object.CreateItem(0)
            With Mail_Single
            .Subject = Email_Subject
            .To = Email_Send_To
            .cc = Email_Cc
            .BCC = Email_Bcc
            .Body = Email_Body
            .send
            End With

        End If

    Next


    Exit Sub

debugs:
        If Err.Description <> "" Then MsgBox Err.Description
End Sub

Comments

[Reg Robinson]

Thank you, this is great! However, do you know how I can put references to Scheduled, Called, and Notes cells in the body of the email? Right now, it only sends an email with whatever text I sub in for "body".

[Dportology]

I don't have time to edit my code right now (sorry!) but I'd use an index to track which row you're on and then you can grab the appropriate data form the other columns. Good luck!

[SQL Police]

Upvote! Any useful answer should be upvoted, for the sake of the posters !