Tech Support Guy banner
Status
Not open for further replies.

Automatic Email from Excel based on Date in Cell

32K views 27 replies 5 participants last post by  ceejaykings 
#1 ·
Hi All,

My name is Diego.

Can anyone send me code to automatically send me an email when the date listed in "column J" is the same date as today. Also, it needs to email only once and even if I am not running excel or at my computer. I want to use Microsoft Outlook and use the ClickYes program as well if this helps that was talked about by Zack Barresse in

http://forums.techguy.org/business-a...s-using-2.html

Essentially I have to be reminded of a reapplication for specific state licensures on healthcare courses I provide. I don't want to forget which courses I have to reapply for so I need to have a program that will look at a date which I have in column J and then email me to remind me of this.

BTW - I am using Outlook 2007 and Excel 2007 on Vista.

Thanks. I appreciate your help! Also, extra points and praise for the person who solves this problem!
 
#3 ·
Thanks for the reply. I have read through some of these posts and had tried to modify them. As simple as that sounds, there is always some extra logic in there that I do not know how to change properly without giving me problems. The thing is, I have been using excel for years but VBA is GREEK to me. I have only begin to explore the idea of MACROs.

I am posting my thread because I have no CLUE. So if there is a guy like ZACK who can whip off a working code in minutes, I would really appreciate it.
 
#5 ·
Thanks.
I will put the column data in quotations so that you know which data I want included in my email. It should be as follows:

Course "B" is scheduled for renewal with the "D" licensing board on the date "J".
I hope this helps you. Thanks again.
 
#6 ·
Can you post a sample workbook? Based on what you said your best option is the one that has already been mentioned. You should use the Windows Task Scheduler to fire off a VBS script that will open the workbook in the background and evaluate the date before taking action. Using the Outlook ClickYes program is one way of handling the built in security prompt that is generated by Outlook but there are other methods of sending the email directly through your mail server without using the Outlook object model. Using one of these alternate methods will allow you to send emails without receiving a security prompt at all.

Regards,
Rollin
 
#8 ·
So I assume that once the email is automatically sent you will place a value in the "K" column? Can you also tell me how often the code should run? You will need to use Windows task scheduler to set the time interval to open and check the workbook. I also need to know what you want the email to say.

Regards,
Rollin
 
#11 ·
The code should run at least once a week.
You've indicated in your original post that you want to have the code check to see if the date in the specified column matches the current date correct? If you are going to be checking each value against the current date you would need to have the code executed daily. If you only run it once a week you will likely have cells in your workbook that have dates that are prior to the current date. If that is the case you would need to have the code check for dates that are either equal to or less than the current date. Does that make sense? How do you want to handle it? I've included some sample VBS code below. You will need to have the Outlook ClickYes program installed for this to work without user input.

Code:
Dim objExcel 
Dim objOutlook 
Dim objMail 
Dim objWB 
Dim objWS 
Dim vCell

Set objExcel = CreateObject("Excel.Application")
Set objOutlook = CreateObject("Outlook.Application")

objExcel.DisplayAlerts = False
objExcel.Workbooks.Open ("[COLOR="Red"]C:\Test.xls[/COLOR]")
Set objWB = objExcel.Activeworkbook
Set objWS = objWB.ActiveSheet
For Each vCell In objWS.Range("[COLOR="Red"]J1[/COLOR]:J" & objWS.Cells(objWS.Rows.Count, "J").End(-4162).Row).Cells

If FormatDateTime(vCell) <= FormatDateTime(Date) Then
If vCell.Offset(0, 1).Value <> "YES" Then
Set objMail = objOutlook.CreateItem(olMailItem)

objMail.To = "[COLOR="Red"]test@test.com[/COLOR]"
objMail.Subject = vCell.offset(0, -8).Value & " Email Alert"

objMail.Body = "COURSE TITLE - " & vCell.offset(0, -8).Value & vbCrLf & _
"APPROVING STATE - " & vCell.offset(0, -7).Value & vbCrLf & _
"APPROVING BODY - " & vCell.offset(0, -6).Value & vbCrLf & _
"PROFESSION - " & vCell.offset(0, -5).Value & vbCrLf & _
"CEUs - " & vCell.offset(0, -4).Value & vbCrLf & _
"APPROVAL NUMBER - " & vCell.offset(0, -3).Value & vbCrLf & _
"DATE RECEIVED - " & vCell.offset(0, -2).Value & vbCrLf & _
"EXPIRATION DATE - " & vCell.offset(0, -1).Value

objMail.Send
vCell.Offset(0, 1).Value = "YES"
End If
End If
Next


objWB.Save
objWB.Close
objExcel.Quit
objOutlook.Quit
Set objExcel = Nothing
Set objWB = Nothing
Set objWS = Nothing
Set objMail = Nothing
Set objOutlook = Nothing
Regards,
Rollin
 
#12 ·
Yeah that makes perfect sense. Based on your question, it's clear that you got the nature of the request I have. In that case, the code should run daily. My only worry is that these events happen only several times a year. So having excel open up daily might be bothersome. However, I am willing to live with that.
On the other hand, if you have a solution that opens it once a week and the date in question happens to fall within the next 7 days, then an email can go out. In other words. The program will scan the dates to see which events will occur each week and send an email out.

BTW - in case you're wondering, I wouldn't mind if it was one email that had all events for that week or a separate email for each event in the same week.

Whatever is easiest for you to code is good for me. I appreciate your work and your help!
 
#13 ·
I've changed the code above to include logic to check to see if the date is equal to or less than the current date. Just copy the code above into a plain text file and then manually rename the file extension from .txt to .vbs

You'll want to change the portions that are highlighted in red. The first highlighted portion is the path to your workbook. The second highlighted portion represent the first date cell that will be evaluated. In my example above it would check the first date value in cell J1 so you'll want to change it to reflect the correct row number in your workbook. The last highlighted portion is the email address that you want to receive the email. After the changes have been made and the file saved with the .vbs extension you can run the code by either double clicking the .vbs file or using the Windows Task Scheduler to create a new weekly task and point it to the VBS file. Try the code out by making a copy of your workbook and changing some of the dates to equal todays date or earlier and let me know if it works for you.

As it stand right now I have a separate email being generated for each event. If I have time I'll tweak the code so that all events are generated in one single email. Also keep in mind that the code assumes that there are no blank lines of data between records.

Regards,
Rollin
 
#14 ·
I tried the code above by saving it to it's own vbs extensions which I names approvalswarning.vbs

I put in the appropriate email info and I renamed the xls file to the one I have. There is one concern and one error.

When I double click the vbs file, it states a VBSscript runtime error code 800A00D which states Type Mismatch: "Format Date Time"

The concern I have is that when I try to open up MS EXcel it says the file is read only. I will need to update this file with newly approved courses and future expiry dates.

Am I doing something wrong. The dates are formatted the way they I sent them to you. Please let me know.
 
#17 ·
Diego,

I ran the VBS script on the workbook that you emailed to me and it also works fine.

Did you remember to change the starting cell number in the code below? In my example below I used cell J1 as the starting cell (see portion highlighted in red)

In the sample workbook you sent me cell J1 contains the column header and not a date. You would need to change the portion in red to reflect the correct first date cell which is cell J2 on your workbook. Make the change and try the code again.

For Each vCell In objWS.Range("J1:J" & objWS.Cells(objWS.Rows.Count, "J").End(-4162).Row).Cells
Regards,
Rollin
 
#18 ·
Rollin, you my friend, are a GENIUS!! I can't thank you enough. It works beautifully.

I have a question - do I need a clickyes program? Because I don't have one and it still worked. It sent me two emails as it should have and it updated column K as it was supposed to.

Please let me know. Other than that - I bow to your superior intellect and ability!! You have done what I would have gone crazy trying to do.
 
#19 ·
Glad to help you out Diego!

The ClickYes program is only needed if Outlook gives you a security prompt advising that another program is trying to send mail on your behalf. The ClickYes program may or may not be required depending on the version of Outlook you are using? Do you know what version Outlook is installed on the sending PC?

Regards,
Rollin
 
#21 ·
Diego,

I think you'll be ok without the ClickYes program. It appears that Microsoft has relaxed the security models for the newer versions of Outlook starting with Outlook 2007. It appears that you will not get the security prompt as long as the PC in question has acceptable antivirus software running on it. If you plan on running the code in prior Outlook versions you will need to use the ClickYes program or select a different email method

Regards,
Rollin
 
#22 ·
There is one problem - perhaps this has to do with clickyes.

The code works fine its just that if I have outlook open and then execute the code, then I get an ACT message that says: "Object reference not sent to an instance of an object. Then it shuts down Outlook If possible, If would rather it just left outlook open. I still get the same message if outlook is closed.

The fact remains that it still works, I only have to click ok for each email that is trying to be generated. Then when I open Outlook back up, I can see that I have the two email alerts from my excel file. Also, the excel file is updated with "YES" under the "SENT EMAIL" column.

I don't use ACT to send emails, I use Outlook. So I don't know why this message is occuring.
 
#25 ·
I have a similar problem on this subject matter and will be happy if someone help me out.
I have lists of some certifications with the expiry dates on an excel spreadsheet. I want to code the excel sheet to be able to generate auto emails few days before, on and after the date of expiration. I tried following the examples on this thread but the program I created is giving a couple of errors. Find attached the program and the an extract of the excel sheet.
Thanks Guys
Regards,
CJ
 

Attachments

Status
Not open for further replies.
You have insufficient privileges to reply here.
Top