Excel VBA To Email Row Data Based on Date

Currently am working on a Excel VBA to alert me through Outlook email based on the following conditions,

I've the deadline dates for each tasks listed in Column 'G'. Now i want the VBA to check this deadline date and if the deadline date is 7 days from today, then it should copy the particular Row data and email the data to my email address.This should be performed everyday automatically, without opening the excel sheet.

re: "This should be performed everyday automatically, without opening the excel sheet."

I don't think that Excel or VBA for Excel is not going to be able to do this with opening the file - or at least some file.

You can't have a macro running inside a closed file, constantly monitoring itself. Something has to trigger the code and/or update the formula that checks the dates, such as the file being opened or perhaps another open file pulling data from the closed file, etc.

I guess you could schedule a windows task to open the file once a day or something similar, but there is going to have to be some "action" that initiates the running of the code.

If you don't open the Excel workbook each day, where will you put the VB code to carry out this function.

You can't put the code in the workbook with the deadline dates because it will not run if the workbook is closed.

Do you have or propose to have another Excel workbook open every day, to hold the code and access the workbook containing the deadline dates every day.

It sounds as though you may require a free-standing program (for example an 'exe' created using Visual Basic or C) on your PC, and then the program is run daily using Windows Task Scheduler. This program would have to run Excel/open the workbook and perform the date tests then send the e-mail.

Anyway, see what others suggest, and have a think about the above comments.

If you need to run it unattended, the only ways I can think of is to use one of the UNIX "cron" commands that have been ported to Windows.They are very easy to use and much more flexible than the native Windows Scheduler.

See here for an article describing how "pycron", one of the many versions available, works.

The batch file will call your Excel from the command linethe script will be executed in the background, wait 100 seconds and since it is called using 'start Excel.exe' rather than just 'excel.exe', the delay should not include the script's execution time, then do everything all over again.

To endlessly run it in the background, type:

start myexcelscript.bat

in the command prompt.

NOTE: I do not know what kind of hit you will take in memory useage doing it this way.

Finally you could modify the myexcelscript.bat to just run once by removing the loop and use Windows Scheduler to run the bat file once a day.

In any case You will need some type of VBA code to "Write/Save/CLose" because I know of no way of doing that from the command line.

Its my mistake that i didn't think about the fact that VBA can't work without opening the Excel file.

Is it possible to perform the operation in the following way, which i revised based on your inputs,

1) Open a particular excel file say at around 1 am everyday automatically.2) Check the particular column say "G" and check if any of the deadline dates listed in the column "G" is 7 days from today.3) If any of the entry in the column "G" satisfies the condition, then the particular Row data should be emailed to the email address.4) Then close the excel file automatically, once the operation is completed.

The macro is attached to the workbook object.Open the Workbook.Click Alt+f11 (The Alt key and function key #11 clicked together)In the Visual Basic window that opens, look in the Project Explorer pane on the left (If it is not visible select View from the VB menu bar and select 'Project Explorer'Look for the name of your workbook - in the form of VBAProject(MyWorkbook.xls)Under this find 'ThisWorkbook' and double click on it.In the main VB window enter this code:

Note that there is a clear text password for outgoing mail authentication.I suggest that you create an e-mail account only for this purpose, so that the PW cannot be used to access someone's e-mail. Also make the project password protected, (although this is not a really secure protection).

To test this macro, click anywhere in the code itself (from Sub to End Sub).Click f8 to single step or f5 to run it.

You will need to comment out this line for testing:

'only run if between midnight and 2AM
If Hour(Now) < 2 Then

and the balancing 'End If' before 'End Sub'. This statement is designed to only run this code during the specified time window, otherwise it will run every time you open the workbook - and as it closes the workbook at the end of the code, you will never be able to access it ! I haven't tested the time window to see if it responds appropriately between 12 and 2AM!

Reinstate the

If Hour(Now) < 2 Then

and the balancing 'End If' before saving and closing the workbook.

The code should run automatically when the workbook is opened.

The code adds a datestamp after the last task in column A.

The method the code uses to locate the range of dates requires that there are no entries in column G below the last date entry.

You will need to complete the various e-mail parameters such as your e-mail system SMTP address.

You can uncomment this line:

'MsgBox strMsg

to show the message for testing purposes - you will get all the html parts as text, but at least you can see what dates/task names are being picked up.

Test this on a copy of the workbook to ensure that it works 'as expected' and always maintain an up to date backup copy. Changes made by macros cannot be undone.

Thanks for the excellent stuff and it does exactly what i required to. :-)

But, when i try to run this automatically using a batch script, am stuck up with the message that asks the user to select :enable macros". If i need to avoid this message, then i should set the macros security setting to "LOW" or i should create digital certificate. Both these options are not feasible in my working condition.

Can the same script be run as a external VB script ?? So that the external script (batch/VB) can open the file, perform the operation and close the file.

If you save the workbook in the Excel startup folder (varies by version - mine is:C:\Documents and Settings\<user name>\Application Data\Microsoft\Excel\XLSTART)Then just start Excel at 1 AM.Your workbook will be opened automatically.Don't include the workbook name in the call to open Excel, - if you do it will try and open it twice.

Thanks for our kind response. First time i came to know about saving the file in excel start up folder. It was very useful.

But, now i couln't use either options,

1) Setting the macros security setting to low2) Saving the file to excel start up folder ( as i need to save it in specific network drive)

So, am trying to use your idea to create a external VB script to perform the same function. This is the first time am trying to write VB script :-( Kindly correct me if am wrong totally... :-)

Also am am trying to use blat tool for sending email, since the email password changes frequently... and blat tool doesn't need password for mail id.

Here is the first part where am trying to check the deadline date in column "G" and sending the data in column "B" based on the date ( this is the modified version of your script )and the output should be written to a text file. This text file content will be emailed through blat tool.

I know that the VB script below is wrong, but i don't know how to correct it... :-(

'loop through all used cells in column G For Each rngCell In .Range(rngStart, rngEnd) 'test if date is less than 7 days from today If rngCell.Value - Int(Now) < 7 Then 'add to message - use task name from column B (offset -5) 'change as required strMsgBody = strMsgBody & "Task: " & rngCell.Offset(0, -5).Text _ & " is due on: " & rngCell.Text & "<br />" End If Next

I have very little experience with VB scripts, but I have been able to get your script to run.

A number of points:

1. You do not need to create a Workbook object. Once you have created the Excel object, you can just handle workbooks as normal.2. Your sPath and sFilename objects are not required. You already know the filename and path, so it just goes in once:

Set oFiletxt = oFilesys.CreateTextFile("C:\temp\RA_Tracking.txt")

3. I think that you can remove the If Hour(Now) > 1, as that was only needed when the Excel file was opened so that the On Open event did not run during the day.4. The main loop which is inside With Worksheets("2010") needs to 'attach' the Worksheet back to the Excel object, like this:

With objExcel.Workbooks("Tracking.xls").Worksheets("2010")

5. As a result of a comments from Razor2.3 and Darth Sidious in this post, I realized that it is necessary to remove xl constants such as xlUp and replace them with the actual values.

The line that creates the message body: strMsgBody = strMsgBody & .... has been split on to two lines for ease of viewing. You will need to put it back on one line. I don't think that you can use the line continuation characters in VB script.

Also when you added the word 'success' you have added it before the html header. You need to add it here:strMsg = strHtmlHead & "Success" & strMsgBody & strHtmlFootso its inside the message body.

Humar:I don't think that you can use the line continuation characters in VB script.You can.

praveenjammy:blat tool doesn't need password for mail id.The need for a password isn't from CDO.Message nor BLAT. It's from your email server. Make sure to do some tests to see if BLAT's emails are actually sent before incorporating it into your script.

My script created an error on the line with the line continuation, and I wrongly assumed it was the line continuation that caused the error, but now I see that it was because I split the lines inside a quoted string.Changing it to split after the " at the end of the string worked fine.

The information on Computing.Net is the opinions of its users. Such
opinions may not be accurate and they are to be used at your own risk.
Computing.Net cannot verify the validity of the statements made on this
site. Computing.Net and Compnet Ventures, LLC hereby disclaim all responsibility
and liability for the content of Computing.Net and its accuracy.