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


Related posts:

  1. How to Schedule a Task in Windows 7 This article is not related to Excel Macro. This is...
  2. How to send email from Excel Macro from Outlook This is continuation of my Previous Article you learnt How...
  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

Comments

comments

  • 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