# Automatic Email from Excel based on Date in Cell



## DMazz (Aug 30, 2009)

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!


----------



## OBP (Mar 8, 2005)

There are quite a few Threads already covering this topic.
http://forums.techguy.org/business-applications/831231-email-data-excel.html
http://forums.techguy.org/business-applications/816760-excel-email-distribution-list.html
http://forums.techguy.org/business-applications/814146-solved-how-send-email-excel.html
http://forums.techguy.org/business-applications/812709-solved-simple-email-excel.html


----------



## DMazz (Aug 30, 2009)

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.


----------



## OBP (Mar 8, 2005)

If you want the message sent when Excel is not running you will need to use Windows Scheduler to open the workbook that sends emails.
What do you want the email to say?


----------



## DMazz (Aug 30, 2009)

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.


----------



## Rollin_Again (Sep 4, 2003)

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


----------



## DMazz (Aug 30, 2009)

Thanks for your help Rollin. Please have a look at the sample data. I will follow your instructions once you are ready:


----------



## Rollin_Again (Sep 4, 2003)

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


----------



## DMazz (Aug 30, 2009)

Yes you are correct - I would like it to say "Yes".
The code should run at least once a week.


----------



## Rollin_Again (Sep 4, 2003)

I've written and tested some simple VBS code and it appears to work. 

What info would you like to appear in the email? 

Regards,
Rollin


----------



## Rollin_Again (Sep 4, 2003)

DMazz said:


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


```
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"][email protected][/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


----------



## DMazz (Aug 30, 2009)

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!


----------



## Rollin_Again (Sep 4, 2003)

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


----------



## DMazz (Aug 30, 2009)

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.


----------



## Rollin_Again (Sep 4, 2003)

I created my own workbook and tested the code on it and it works perfectly. Can you email me your actual workbook so I can debug it? You can send to rollin_again at hotmail dot com


Regards,
Rollin


----------



## DMazz (Aug 30, 2009)

I'll do it right now. Thanks.


----------



## Rollin_Again (Sep 4, 2003)

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


----------



## DMazz (Aug 30, 2009)

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.


----------



## Rollin_Again (Sep 4, 2003)

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


----------



## DMazz (Aug 30, 2009)

Yes I have the complete 2007 office suite.


----------



## Rollin_Again (Sep 4, 2003)

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


----------



## DMazz (Aug 30, 2009)

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.


----------



## Rollin_Again (Sep 4, 2003)

Check in Outlook to see if there are any ACT add-ons loaded that may be causing the issue. I believe you can manage add-ons in Outlook 2007 through *TOOLS >> TRUST CENTER*

Regards,
Rollin


----------



## GLComputing (Apr 27, 2007)

What version of ACT!?
Outlook 2007, right?


----------



## ceejaykings (Sep 7, 2009)

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


----------



## ceejaykings (Sep 7, 2009)

attached is the program I tried to create.


----------



## Rollin_Again (Sep 4, 2003)

Ceejay,

Please start a new thread for your issue and I'll take a look at it.

Regards,
Rollin


----------



## ceejaykings (Sep 7, 2009)

will do Rollin, thanks


----------

