3

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?

Reg Robinson
  • 33
  • 1
  • 2
  • 6

2 Answers2

6

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 = "bob@bob.com"
            Email_Send_To = "bob@bob.com"
            Email_Cc = "bob@bob.com"
            Email_Bcc = "bob@bob.com"
            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
Dportology
  • 836
  • 1
  • 9
  • 32
  • 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". – Reg Robinson Jul 02 '15 at 17:58
  • 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! – Dportology Jul 02 '15 at 18:12
  • Upvote! Any useful answer should be upvoted, for the sake of the posters ! – SQL Police Jul 02 '15 at 18:27
0

This is somewhat late but can be helpful to you and others:

  1. Use the above answer to define the notifications.

  2. Create VB-script file with this code: (open notepad, paste code below, fit to your needs, save as: vbscript file)

    Path="complete_file_path_of_your_excel_file.xlsm"

Macro="email"

Set objApp = CreateObject("Excel.Application.16") 

objApp.Visible = True 

Set wbToRun = objApp.Workbooks.Open(Path) 

objApp.Run Macro

  3. Set Windows Task Scheduler to open the VBscript file you created on a daily basis at a certain time.

  4. You now have a set-and-forget notification bot.

Ali Z
  • 11
  • 4