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...

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

  1. Sergio says:

    I managed to schedule the task in Windows. However, when the spreadsheet opens, there is no email being sent to my inbox. I entered my email address as just to test the spreadsheet, but I don’t receive anything. Thanks!

  2. Sarabjit Singh says:

    Hello Everyone,

    Does anyone know the way to use alignment section (merge) in protected excel sheet without vba code.

    Thanks

  3. Sarabjit Singh says:

    Hello Sir,

    Can you please tell me if it is done. If I can use the line of code that can send emails automatically on given date and time with the code I send you.

    Thanks and regards

    Sarabjit Singh

    • Vishwamitra Mishra Vishwamitra Mishra says:

      Hi Sarabjit,

      To send email automatically you can read this article:
      http://www.learnexcelmacro.com/wp/2012/06/send-email-automatically/

      If you are still not be able to achieve what you want, let me know

      • Sarabjit Singh says:

        Hello Sir,

        Thanks for your reply. Sir, I havent tried your given code, but from the description given here in your like tells me that it can send everyday on particular time. What I need is to send emails on particular day (which I will mention. Once a week or Once a month) and on particular time. If you can give me code just for setting up emails to be send automatically particularly on set day or date and time. I will add that code with the existing one. As I am not a programmer so if you can give me straight code as per my requirement, I shall be very very thankful to you. Today I will try to use the code you have mentioned in the link and will get back to you. I shall be very thankful to you.

        Regards

        Sarabjit Singh
        Regards

        Sarabjit Singh

  4. 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.

  5. 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

  6. Vineet says:

    what do we need to put in SMTP settings ?

  7. 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

  8. 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

  9. 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

  10. 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

  11. [...] 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. [...]

    • sushil sethi says:

      I am trying to use this macro but it's showing error compile error: Can't find project or library.. please help

      • Vishwamitra Mishra Vishwamitra Mishra says:

        Hi Sushil,
        Can you let me know where it is throwing this error. Can you send me the error screen shot to my id : info@learnexcelmacro.com

        • Sarabjit Singh says:

          Hello Sir,
          Sir, I have this code for sending emails to students with short attendance. It is working perfectly. Only thing is that it has to send manually for every sheet for every week. I have 60 sheets like these to send every week to inform students about their attendance. Sir, I want to apply some code with the existing code, that can open attendance sheet automatically on specific day and on specific time and send emails to students automatically and then close the file. At this point I have to keep the outlook open to send the emails. Can it be done automatically also. I am pasting the code for your consideration. Please have a look and recommend according. Thanks. Sarab

          Sub Check_Attendance()

          ActiveSheet.Select

          Dim row, pcol, emailcol, namecol As Integer
          Dim OutApp As Object
          Dim OutMail As Object

          pcol = 28
          namecol = 3
          emailcol = 34
          ccopy = 35
          row = 19
          For row = 19 To 45

          ‘MsgBox (Cells(row, emailcol))
          If (Cells(row, pcol) = 0.9) Then
          ‘MsgBox (“90-95″)
          Application.ScreenUpdating = False
          Set OutApp = CreateObject(“Outlook.Application”)
          Set OutMail = OutApp.CreateItem(olMailItem)

          With OutMail

          .To = Cells(row, emailcol).Value
          .Cc = Cells(row, ccopy).Value

          .Subject = “Unsatisfactory Attendance”
          .body = “Dear” & Cells(row, namecol).Value & vbCrLf & vbCrLf & “Please be informed that it has been brought to our attention that you have not attended some classes this study block. As an on shore international student holding a student visa, enrolled in Diploma in Business Level , you are required to comply with a number of condition related to that visa [as per immigaration Act 2009], including attending at least 85% of your scheduled class hours in a learning block.Your projected attendance for the semester is currently approximately 95%.” & vbCrLf & vbCrLf & “Failure to complay with your attendance requirements can lead to the cancellation of your visa. It should also be noted that, under the country law, The school must report a student who can no longer achieve 85% attendance to the immigration author” & _
          “ities.” & vbCrLf & vbCrLf & “If you continually miss your scheduled class hours and your attendnce falls below 85% for learning block or paper that you are enrolled in we will be forced to notify your breach of satisfactory attendance to the conserned authority.” & vbCrLf & vbCrLf & “It is important to ensure that you attend all classes. If you are sick you may submit a doctor’s certificate, however, you will still be recorded as absent.” & vbCrLf & vbCrLf & “In order to discuss any difficulties you may be experiencing that are affecting your attendance please contact me on telephone 123456780 or email abc@yahoo.com.” & vbCrLf & vbCrLf & “you may also wish to conatct the Manager of the Student Services Deapartment to discuss this matter in confidence. The contact details are:” & vbCrLf & vbCrLf & “Person’s Name” & vbCrLf & vbCrLf & “Student Service Manager” & vbCrLf & vbCrLf & “Telephone:12345678″ & vbCrLf & vbCrLf & “abc@yahoo.com.” & vbCrLf & vbCrLf & “Yours since” & _
          “rely,” & vbCrLf & vbCrLf & vbCrLf & “Name of person” & vbCrLf & vbCrLf & “Director Academy ” & vbCrLf & vbCrLf & ” Physical Address” & vbCrLf & vbCrLf & “Physical Address” & vbCrLf & vbCrLf & vbCrLf & “Ph: 12345678″ & vbCrLf & vbCrLf & “Mob 123456″ & vbCrLf & vbCrLf & “Registered Provider Number 123456″
          .send
          End With
          ‘Turn back on screen updating
          Application.ScreenUpdating = True
          Set OutMail = Nothing
          Set OutApp = Nothing

          ElseIf (Cells(row, pcol) >= 0.85) And (Cells(row, pcol) < 0.9) Then
          'MsgBox ("85-95")
          ActiveSheet.Select
          'Dim OutApp As Object
          'Dim OutMail As Object

          Application.ScreenUpdating = False
          Set OutApp = CreateObject("Outlook.Application")
          Set OutMail = OutApp.CreateItem(olMailItem)
          With OutMail
          'MsgBox ((Cells(k, row).Value))

          .To = Cells(row, emailcol).Value

          .Subject = "Unsatisfactory Attendance"
          .body = "Dear" & Cells(row, namecol).Value & vbCrLf & vbCrLf & "Please be informed that it has been brought to our attention that you have not attended some classes this study block. As an on shore international student holding a student visa, enrolled in Diploma in Business Level , you are required to comply with a number of condition related to that visa [as per immigaration Act 2009], including attending at least 85% of your scheduled class hours in a learning block.Your projected attendance for the semester is currently approximately 95%." & vbCrLf & vbCrLf & "Failure to complay with your attendance requirements can lead to the cancellation of your visa. It should also be noted that, under the country law, The school must report a student who can no longer achieve 85% attendance to the immigration author" & _
          "ities." & vbCrLf & vbCrLf & "If you continually miss your scheduled class hours and your attendnce falls below 85% for learning block or paper that you are enrolled in we will be forced to notify your breach of satisfactory attendance to the conserned authority." & vbCrLf & vbCrLf & "It is important to ensure that you attend all classes. If you are sick you may submit a doctor's certificate, however, you will still be recorded as absent." & vbCrLf & vbCrLf & "In order to discuss any difficulties you may be experiencing that are affecting your attendance please contact me on telephone 123456780 or email abc@yahoo.com." & vbCrLf & vbCrLf & "you may also wish to conatct the Manager of the Student Services Deapartment to discuss this matter in confidence. The contact details are:" & vbCrLf & vbCrLf & "Person's Name" & vbCrLf & vbCrLf & "Student Service Manager" & vbCrLf & vbCrLf & "Telephone:12345678" & vbCrLf & vbCrLf & "abc@yahoo.com." & vbCrLf & vbCrLf & "Yours since" & _
          "rely," & vbCrLf & vbCrLf & vbCrLf & "Name of person" & vbCrLf & vbCrLf & "Director Academy " & vbCrLf & vbCrLf & " Physical Address" & vbCrLf & vbCrLf & "Physical Address" & vbCrLf & vbCrLf & vbCrLf & "Ph: 12345678" & vbCrLf & vbCrLf & "Mob 123456" & vbCrLf & vbCrLf & "Registered Provider Number 123456"

          .send

          End With
          'Turn back on screen updating
          Application.ScreenUpdating = True
          Set OutMail = Nothing
          Set OutApp = Nothing

          '
          ElseIf (Cells(row, pcol) < 0.85) Then
          'MsgBox ("85")

          ActiveSheet.Select
          Application.ScreenUpdating = False
          Set OutApp = CreateObject("Outlook.Application")
          Set OutMail = OutApp.CreateItem(olMailItem)
          With OutMail
          'MsgBox ((Cells(row, emailcol).Value))

          .To = Cells(row, emailcol).Value
          .Subject = "Unsatisfactory Attendance"
          .body = "Dear" & Cells(row, namecol).Value & vbCrLf & vbCrLf & "Please be informed that it has been brought to our attention that you have not attended some classes this study block. As an on shore international student holding a student visa, enrolled in Diploma in Business Level , you are required to comply with a number of condition related to that visa [as per immigaration Act 2009], including attending at least 85% of your scheduled class hours in a learning block.Your projected attendance for the semester is currently approximately 95%." & vbCrLf & vbCrLf & "Failure to complay with your attendance requirements can lead to the cancellation of your visa. It should also be noted that, under the country law, The school must report a student who can no longer achieve 85% attendance to the immigration author" & _
          "ities." & vbCrLf & vbCrLf & "If you continually miss your scheduled class hours and your attendnce falls below 85% for learning block or paper that you are enrolled in we will be forced to notify your breach of satisfactory attendance to the conserned authority." & vbCrLf & vbCrLf & "It is important to ensure that you attend all classes. If you are sick you may submit a doctor's certificate, however, you will still be recorded as absent." & vbCrLf & vbCrLf & "In order to discuss any difficulties you may be experiencing that are affecting your attendance please contact me on telephone 123456780 or email abc@yahoo.com." & vbCrLf & vbCrLf & "you may also wish to conatct the Manager of the Student Services Deapartment to discuss this matter in confidence. The contact details are:" & vbCrLf & vbCrLf & "Person's Name" & vbCrLf & vbCrLf & "Student Service Manager" & vbCrLf & vbCrLf & "Telephone:12345678" & vbCrLf & vbCrLf & "abc@yahoo.com." & vbCrLf & vbCrLf & "Yours since" & _
          "rely," & vbCrLf & vbCrLf & vbCrLf & "Name of person" & vbCrLf & vbCrLf & "Director Academy " & vbCrLf & vbCrLf & " Physical Address" & vbCrLf & vbCrLf & "Physical Address" & vbCrLf & vbCrLf & vbCrLf & "Ph: 12345678" & vbCrLf & vbCrLf & "Mob 123456" & vbCrLf & vbCrLf & "Registered Provider Number 123456"
          .send

          End With
          'Turn back on screen updating
          Application.ScreenUpdating = True
          Set OutMail = Nothing
          Set OutApp = Nothing

          Else

          'GoTo DONE
          End If

          'MsgBox ((Cells(row, namecol).Value))

          Next

          LAST:
          DONE:
          End Sub

Leave a Reply

Comments

comments