How to Send Email Automatically from Excel

June 30, 2012| Posted by : | Filled in Excel Macro, Popular Articles

Hello friends,


In the previous Article, you learnt how to Schedule a Program in Windows. In this article you are going to learn, How can we send email from Excel on a Scheduled date and Time automatically without any manual intervention. I have taken an Example from Live Scenario, where you want to send an Email on a daily basis based on certain conditions and cafeterias.


Example:Let’s assume that You are working as a Team Lead. You have 10 members in your team. You are responsible to assign Task to each member with a dead-line (Date of Completion). You need to track that each team member is completing their assigned task on or before the dead-line. If any of the team member is crossing the deadline one email should be sent to that person automatically from the Outlook configured on your system or Server wherever this Excel Tracker is kept.
During your day time your team will be working and status will be updated by them. In morning by 9:00 AM you want that Excel Macro should go and verify all the Tasks against each one of your team member and send an email to them if they are crossing the dead-line.


The Excel Template which I have prepared looks like this:


Send Email Automatically

Send Email Automatically



How to Set this up for your System

Follow the below Steps to Set up this Macro in your System:
 
Step 1. Download the Workbook.
Step 2. Save this Workbook at some Location in your system.
Step 3. Now Schedule this Workbook in Task Scheduler in your System. To know How to Schedule a Task in Windows Read this article.
Note: In the Slide Number 11, of the previous article, You need to give the Complete path of your Workbook where it is saved
If the Path is not correct then this Scheduler will not run and through Error.

You can also download the “How to Schedule a Task in Windows PPT” from here

Download Now


Comments

comments

Related posts:

  1. How to send email from Excel Macro from Outlook This is continuation of my Previous Article you learnt How...
  2. How to Schedule a Task in Windows 7 This article is not related to Excel Macro. This is...
  3. How to Send an Email using Excel Macro from Gmail or Yahoo Dear Readers, In this article we are going to learn...
  4. How to Send ActiveWorkbook as attachment in Email In previous Article, i had written how to Send Email...
  5. Download : Excel Macro Application to Send Multiple Emails Hello Friends, I was getting queries on : How to...

Subscribe To Get New Posts delivered directly to your Mailbox

17 Responses to “How to Send Email Automatically from Excel”

  1. [...] This article is not related to Excel Macro. This is useful when you want to do some Job by excel Macro automatically. For example, if you want to send an Email Every Day automatically, without any manual intervention, then this task scheduling will come in to the picture. If you want to know how to Send Email Automatically from your system on a specific day and time.. then read my Next Article. [...]

  2. Bala says:

    Hi,
    i followed the above steps and scheduled the tasks, but when the time gets triggered the excel macro sheet gets opened and throwing some error message

    Error in line:
    <<If Sheet1.Label1.Caption Date Then >>
    highlighting “Date” and saying the error as “compile error: Can’t find project or library”

    kindly advise.

    • Vishwamitra Mishra Vishwamitra Mishra says:

      This error may be because of the Keyword “DATE”.
      Try the below line for DATE

      Format(Now, “dd/mm/yyyy”)

      • Bala says:

        Tried with the same but I’m not finding the solution.

        Mishra, here is my requirement.
        1. From the above mentioned example, the macro need to send a mail automatically whenever the mentioned date = today’s date.
        2. Please Guide me with step by step procedure so that i can make use of your valuable input and strive towards success.

        hope you understood my requirement.looking for reply

    • Melissa says:

      Pardon my ignorance, but I am just now beginning to be introduced to VBA. I have been tasked to set up a macro to do exactly what this lesson is outlining, however this error is still present when I downloaded the workbook.

      <>
      highlighting “Date” and saying the error as “compile error: Can’t find project or library”

      Would it be possible for you to send to me what you sent to Bala?

      Thank you in advance for your assistance

      • Vishwamitra Mishra Vishwamitra Mishra says:

        Hi Melissa,

        Now you can download the file. Date issues is fixed now. Let me know if there is any issue.

        Thanks,
        Vish

  3. saurabh says:

    Dear Vishwamitra,
    I downloaded your tool for mailing to multiple people from excel mailing list. But i want a mail to carry a image and formatted in html. with personal signature etc.
    request for your help
    cheers
    saurabh

    • Vishwamitra Mishra Vishwamitra Mishra says:

      Hi Saurabh,

      In the mail body you need to add this for HTML body:

      With NewMail
      .Subject = Subject
      .To = StrToEmail
      .Cc = StrCCEmail
      .BCC = StrBCCEmail
      .HTMLBody = “Here you can put the HTML code for your email with your Signature, Image etc.”

      End With

  4. amar says:

    hi vish,
    i need a code like
    i have 3 checkboxes
    if i checked first checkbox then single name(“anil”) should be enter from A2 to A10 cells and if i checked first and second so it should devide in two names anil and ankur in A2 to A10 and if i checked first and third then it should devide in first anil and third ajeet and if i checked second and third then it should devide in ankur and ajeet to fill the A2 to A10 cell
    please reply

  5. Avinash says:

    looking for small help, need to attach image in the excel sheet while sending email via macro
    using below macro

    Sub EMEAMail_Sheet_Outlook_Body()
    ‘ Don’t forget to copy the function RangetoHTML in the module.
    ‘ Working in Office 2000-2010
    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
    With Application
    .EnableEvents = False
    .ScreenUpdating = False
    End With

    Set rng = Nothing
    Set rng = ActiveSheet.UsedRange
    ‘You can also use a sheet name
    ‘Set rng = Sheets(“YourSheet”).UsedRange

    Set OutApp = CreateObject(“Outlook.Application”)
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
    .to = “”
    .CC = “”
    .BCC = “”
    .Subject = “SUN” & Format(Now, ” dd/mm/yyyy hh:mm:ss”)
    .HTMLBody = RangetoHTML(rng)
    .Send ‘or use .Display
    End With
    On Error GoTo 0

    With Application
    .EnableEvents = True
    .ScreenUpdating = True
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
    End Sub
    Function RangetoHTML(rng As Range)
    ‘ Changed by Ron de Bruin 28-Oct-2006
    ‘ Working in Office 2000-2010
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook

    TempFile = Environ$(“temp”) & “/” & Format(Now, “dd-mm-yy h-mm-ss”) & “.htm”

    ‘Copy the range and create a new workbook to past the data in
    rng.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
    .Cells(1).PasteSpecial Paste:=8
    .Cells(1).PasteSpecial xlPasteValues, , False, False
    .Cells(1).PasteSpecial xlPasteFormats, , False, False
    .Cells(1).Select
    Application.CutCopyMode = False
    On Error Resume Next
    .DrawingObjects.Visible = True
    .DrawingObjects.Delete
    On Error GoTo 0
    End With

    ‘Publish the sheet to a htm file
    With TempWB.PublishObjects.Add( _
    SourceType:=xlSourceRange, _
    Filename:=TempFile, _
    Sheet:=TempWB.Sheets(1).Name, _
    Source:=TempWB.Sheets(1).UsedRange.Address, _
    HtmlType:=xlHtmlStatic)
    .Publish (True)
    End With

    ‘Read all data from the htm file into RangetoHTML
    Set fso = CreateObject(“Scripting.FileSystemObject”)
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.ReadAll
    ts.Close
    RangetoHTML = Replace(RangetoHTML, “align=center x:publishsource=”, _
    “align=left x:publishsource=”)

    ‘Close TempWB
    TempWB.Close savechanges:=False

    ‘Delete the htm file we used in this function
    Kill TempFile

    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
    End Function

  6. Vineet says:

    what do we need to put in SMTP settings ?

  7. djemai says:

    Good morning,

    I want to automatically send e-mails to suppliers to notify them of their payment(bank transfers) and I want to do it automatically from excel .How can this be achieved based on a file that contains invoices details and I want the macro to be launched as soon as I put “Paid” in special cell.

    I would be very grateufull if you can help me as I am stuck in that problem and suppliers are complaining about thier payements.
    Thank you in advance

  8. Brett says:

    On schedule time i just see THIS sample excel file on my monitor, but it is not sending emails automatically? What am i missing, not able to trace out, can you help me to find solution,

    Thanks,
    Brett

    • Vishwamitra Mishra Vishwamitra Mishra says:

      Are you getting any error? Or it just does not send emails? Did you check the dates and all for which email should get triggered? Kindly let me know in detail then I will be able to help you on this.

Leave a Reply

  • About
  • Downloads
  • Excel Formula
  • Excel Tips
  • Excel Macro Tutorial
  • VBA Functions
  • Archives
  • Contact
  • Excel Formula
  • Excel Functions
  • Excel Macro
  • Excel Macro Basics
  • Excel Macro Tutorial
  • Excel Tips
  • HP QC
  • Interesting VBA Functions
  • New to Excel Macro ?
  • Personal
  • Popular Articles
  • 2014
  • 2013
  • 2012
  • 2011