Sending email from excel automatically based on da

问题:

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?

回答1:

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 = "[e-mail]"
            Email_Send_To = "[e-mail]"
            Email_Cc = "[e-mail]"
            Email_Bcc = "[e-mail]"
            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

回答2:

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.