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 emails 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 on your team. You are responsible to assign Tasks to each member with a deadline (Date of Completion). You need to track that each team member is completing their assigned task on or before the deadline. If any of the team members are 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 daytime, your team will be working and your status will be updated by them. In the morning by 9:00 AM you want that Excel Macro should go and verify all the Tasks against each one of your team members and send an email to them if they are crossing the deadline.
The Excel Template which I have prepared looks like this:
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.
How to Schedule a Task in Windows ?
You can also download the “How to Schedule a Task in Windows PPT” from here
![Distance Calculator in Excel VBA [REVISED]](https://vmlogger.com/excel/wp-content/uploads/sites/11/2019/03/Distance-400x250.gif)
![Methods to save a Macro FREE copy of a workbook [FREE DOWNLOAD]](https://vmlogger.com/excel/wp-content/uploads/sites/11/2018/07/saveasMacroFree-1-400x250.png)
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.
This error may be because of the Keyword "DATE".
Try the below line for DATE
Format(Now, "dd/mm/yyyy")
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
Hi Bala,
I have replied to your mail, kindly check your mailbox.
Thanks,
Vish
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
Hi Melissa,
Now you can download the file. Date issues is fixed now. Let me know if there is any issue.
Thanks,
Vish
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
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
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
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
what do we need to put in SMTP settings ?
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
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
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.
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
Hi Sarabjit,
To send email automatically you can read this article:
http://learnexcelmacro.com/wp/2012/06/send-email-automatically/
If you are still not be able to achieve what you want, let me know
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
Hello Everyone,
Does anyone know the way to use alignment section (merge) in protected excel sheet without vba code.
Thanks
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!
Hi, not too sure if I’m at the correct forum. Hope you can perhaps help.
I have a training schedule with a list of names in column “A”. Now, all the other column (B to F) have certain criteria a person needs training on, with expiry dates – I have added a column next to each expiry date which counts down the days left to expiry.
Can you please help with a code that can get excel to send a mail automatically to various email addresses to notify them that the expiry is near?
Do I need to set a task in Windows?
Please help as this will make my life a lot easier.
Dear Mr. Mishra,
Thanks a lot for your forum.
I’ve tried this. Its working well. But, it VBA code i’ve changed some data i.e. “Following Task is still not Completed. The task completion date was on” as per my requirement.
after using my data it is not sending email.
Please guide
hi sir, i required macro code for at a time particular excel data send to particular mail id (include to, cc, bcc).please provide code for that.
Guptha
Just I am looking for your help to fine tune the doc further as I don’t know anything about the macros planning.
I am an Program manager in an automotive industry and my work is to chase and remind the team for delay and risks.
In the current format that I have downloaded from your site:
1) It sends the email only on next day (even if I rescheduled the task in schedule task in computer), I think it is the part of your program or please suggest if can be done from schedule task
2) Is it possible to get an additional send option on format so that we can send some of the mail whenever it required
3) Is it possible to add CC and BCC option in format
Hi Param Hansh,
Yes, this can be done. you can modify the code and customize the same for your specific requirement. you can refer articles around sending emails from my tutorial pages:
http://learnexcelmacro.com/wp/excel-macro-tutorial/
Hi Vishwa, is it possible to write a macro where when i select cells and run the macro it fills the cells with a particular text.Please note the cells selected may be filled or may be empty.Can u please suggest a macro for this.
hi,
I’ve created an a macro wherein you can send multiple emails at in short period of time for multiple receivers, it works fine but for some reason, some users needs to press send manualy on outlook to send the email.
is this just a setting in excel that needed to be change?
hi there, I can’t send email. whenever I tried to send it, it tells me “compile error module: Module1”
i am using windows 10 and office 2013
Hi,
I wanted to send the same email content with 450 different attachments for 450 people. The attachments will be customized for all 450 people.
Since I am not well versed with excel or macro, could you please list out the steps in getting this task done through excel? I would highly appreciate any work arounds that you can provide.
Very good information. Lucky me I recently found your blog by chance (stumbleupon). I have saved it for later!
Hi,
Is this code work for IBM notes?
How to modify the code to be use in IBM notes?
Thanks 🙂
Hi I just downloaded your file and it’s really good.
I was wondering if there is a way you could add the date in a cell where it will send from that date?
hi,
heed u r help i have my own company.
i need to sent multiple mails to my client on daily basis.
my mail includes one attachment , one short summary of for every client
and fixed text body.
1.attachment in excel form
2.summary in excel form
please help me…………
nicen unden postenerun
Good Day
I downloaded your workbook and followed the step (if those indicated in the previous article for tasks scheduler)
this does not work, task scheduler opens a request stating “with what app or program would you like to open the request) . I browsed the file, its a excel macro enabled saved file, surely the system should see that
Further, when manually opening the work book, surely the code should run VBA and put a mail in my outlook, that also does not happen
can you help here
I wanted to thank you for this great read!! I definitely enjoying every little bit of it I have you bookmarked to check out new stuff you post…
Hi,
First of all, thanks a lot.
When i compile i get the error “Compile error: method or data member not found”
highlightning “label1”
on “If Sheet1.Label1.Caption VBA.Date Then”
Can you please help me?
Hi Did you delete all the contents from Sheet1? because it looks like label1 from Sheet1 is deleted. Which is used to check if scheduler has already run today or not? If yes, then do not run again.
Hi Vish,
Now how can we recreate the Lebel in the workbook so that it could work fine?
Thanks for your help.
Regards
Chirag Agrawal
Hi Vish,
Thanks for the wonderful Macro. I added some additional details in column A and tried to open the file. But the mail is not working for incomplete tasks. I tried to run the macro manually. Still the mail is not going :(.
Can you help in this.
Hi Vish,
Thanks for the wonderful Macro. But this macro is working for one time only 🙁 that too when I open for the first time only.
Next time when I open the file the macro is not working.
Could you please tell me how to solve this issue.
Thanks in advance
I want to create one macro which is share data on body of the mail every hour via automation.
Great site you’ve got here.. It’s hard to find good quality writing like
yours these days. I really appreciate people like you!
Take care!!
Thanks a bunch for one more valuable post. Ive been checking this website frequently for the past number of months, and its currently one of my favorite sites! . I work on an internet site that has very similar content, would you’ve got any interest write a guest post on it? Shoot me an email if you are interested!
Major thankies for the blog article. Really Cool.
I am trying to use this macro but it's showing error compile error: Can't find project or library.. please help
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
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
Sir can you give your whatsapp number ,we want a some help.
Hi , you can post your question here in the comment, or twitter or facebook page. I will respond. At last you can also send me an email at info@learnexcelmacro.com.