Excel VBA To Email Row Data Based on Date

Microsoft Windows xp professional w/serv...
March 31, 2010 at 02:18:15
Specs: Windows XP, 2.5
Hi Guys,

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.

Am very new to VBA. So,Your help is much appreciated.

Cheers,
PK


See More: Excel VBA To Email Row Data Based on Date

Report •


#1
March 31, 2010 at 05:03:45
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.


Report •

#2
March 31, 2010 at 05:21:11
Hi,

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.


Regards


Report •

#3
March 31, 2010 at 08:29:55
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.

http://www.bigbluehost.com/article4...


You could also put it all into an "endless loop" bat file, like:

:START
start excel.exe "c:\Complete\Path\My Folder\book1.xls"
choice /ty,100 > nul
goto START


The batch file will call your Excel from the command line
the 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.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
March 31, 2010 at 10:30:59
re: To endlessly run it in the background...

How do you stop it?


Report •

#5
March 31, 2010 at 19:46:08
Taskmanager
Kill Process

MIKE

http://www.skeptic.com/


Report •

#6
March 31, 2010 at 21:45:22
Hi Guys,

Thanks for the amazing responses.....

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.

Thanks in advance for your help !!!

Cheers,
PK


Report •

#7
April 1, 2010 at 05:51:56
Hi,

This macro is triggered by the On Open event.

The macro looks for all dates in column G starting at row 2 (assumes a header in row 1) and can be changed here:

    'set start of date range
    Set rngStart = .Range("G2")
on Sheet1. If the worksheet is not named Sheet1, change it here:
'Worksheet name
With Worksheets("Sheet1")
The macro creates a message using a task name in column A. To use another column change the column offset here:
            'add to message - use task name from column A (offset -6)
            'change as required
            strMsgBody = strMsgBody & "Task: " & rngCell.Offset(0, -6).Text _
                & " is due on: " & rngCell.Text & "<br />"

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:
Option Explicit

Private Sub Workbook_Open()
Dim rngStart As Range
Dim rngEnd As Range
Dim rngCell As Range
Dim strHtmlHead As String
Dim strHtmlFoot As String
Dim strMsgBody As String
Dim strMsg As String
Dim objEmail As Object

On Error GoTo ErrHnd

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

'setup basic HTML message header and footer
strHtmlHead = "<html><body>"
strHtmlFoot = "</body></html>"

'setup start of body of message
strMsgBody = "The following task(s) are due in less than 7 days<br />"

'Worksheet name
With Worksheets("Sheet1")
    'set start of date range
    Set rngStart = .Range("G2")
    'find end of date range
    Set rngEnd = .Range("G" & CStr(Application.Rows.Count)).End(xlUp)
    
    '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 A (offset -6)
            'change as required
            strMsgBody = strMsgBody & "Task: " & rngCell.Offset(0, -6).Text _
                & " is due on: " & rngCell.Text & "<br />"
        End If
    Next rngCell
    
    'Note last test time/date
    rngEnd.Offset(1, -6) = Now
    rngEnd.Offset(1, -6).NumberFormat = "dd/mmm/yy \a\t hh:mm"
End With

'put message together
strMsg = strHtmlHead & strMsgBody & strHtmlFoot

'test message
'MsgBox strMsg

'create the e-mail object
Set objEmail = CreateObject("CDO.Message")

With objEmail
    'setup e-mail parameters
    With .Configuration.Fields
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
            = "SMTP.XXXXX.com"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") _
            = "30"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") _
            = "2"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") _
            = "25"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendemailaddress") _
            = "YYYYY@XXXXX.com"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") _
            = "1"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") _
            = "YYYYY@XXXXX.com"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") _
            = "PPPPP"
        .Update
    End With
    
    'create e-mail message
    .To = "A@B"
    .From = "B@C"
    .Subject = "Task Alert"
    .HtmlBody = strMsg
    
    'send e-mail
    .send
End With

'remove the e-mail object
Set objEmail = Nothing

'close workbook
Me.Close savechanges:=True

End If
Exit Sub

'error handler
ErrHnd:
Err.Clear
Me.Close savechanges:=False
End Sub

Select Save from the VB menu.

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.


Regards


Report •

#8
April 3, 2010 at 03:14:42
Hi Humar,

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.

Thank you once again for your much appreciated help....
PK


Report •

#9
April 3, 2010 at 05:26:52
Hi,

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.

The information is in this Microsoft article

Regards


Report •

#10
April 6, 2010 at 04:30:31
Hi Humar,

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 low
2) 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... :-(

Dim rngStart
Dim rngEnd
Dim rngCell
Dim strHtmlHead
Dim strHtmlFoot
Dim strMsgBody
Dim strMsg
Dim oFilesys, oFiletxt, sFilename, sPath

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\WINNT\Profiles\PKumar\RA Tracking.xls")
Set oFilesys = CreateObject("Scripting.FileSystemObject")
Set oFiletxt = oFilesys.CreateTextFile("C:\WINNT\Profiles\PKumar\RA_Tracking.txt", True)
sPath = oFilesys.GetAbsolutePathName("C:\WINNT\Profiles\PKumar\RA_Tracking.txt")
sFilename = oFilesys.GetFileName(sPath)

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

'setup basic HTML message header and footer
strHtmlHead = "<html><body>"
strHtmlFoot = "</body></html>"

'setup start of body of message
strMsgBody = "The following task(s) are due in less than 7 days<br />"

'Worksheet name
With Worksheets("2010")
'set start of date range
Set rngStart = .Range("G2")
'find end of date range
Set rngEnd = .Range("G" & CStr(Application.Rows.Count)).End(xlUp)

'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

'Note last test time/date
rngEnd.Offset(1, -5) = Now
rngEnd.Offset(1, -5).NumberFormat = "dd/mmm/yy \a\t hh:mm"
End With

'put message together
strMsg = strHtmlHead & strMsgBody & strHtmlFoot

oFiletxt.WriteLine("success"&strMsg)
oFiletxt.Close'

'close workbook
Me.Close savechanges = True

End If


Kindly advice :-)



Report •

#11
April 7, 2010 at 09:42:26
Hi,

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.

Anyway here is the code:

Dim objExcel
Dim objWorkbook
Dim rngStart
Dim rngEnd
Dim rngCell
Dim strHtmlHead
Dim strHtmlFoot
Dim strMsgBody
Dim strMsg
Dim oFilesys
Dim oFiletxt
Dim sFilename
Dim sPath

Set objExcel = CreateObject("Excel.Application")
Set oFilesys = CreateObject("Scripting.FileSystemObject")
Set oFiletxt = oFilesys.CreateTextFile("C:\temp\RA_Tracking.txt")

'open Workbook
objExcel.Workbooks.Open ("C:\temp\Tracking.xls")

'setup basic HTML message header and footer
strHtmlHead = "<html><body>"
strHtmlFoot = "</body></html>"

'setup start of body of message
strMsgBody = "The following task(s) are due in less than 7 days<br />"

'Worksheet name
With objExcel.Workbooks("Tracking.xls").Worksheets("2010")
    'set start of date range
    Set rngStart = .Range("G2")
    'find end of date range
    Set rngEnd = .Range("G" & Cstr(objExcel.Application.Rows.Count)).End(-4162)

    '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

    'Note last test time/date
    rngEnd.Offset(1, -5) = Now
    rngEnd.Offset(1, -5).NumberFormat = "dd/mmm/yy \a\t hh:mm"
End With

'put message together
strMsg = strHtmlHead & "Success" & strMsgBody & strHtmlFoot

oFiletxt.WriteLine (strMsg)
oFiletxt.Close '

'close workbook
objExcel.Workbooks("Tracking.xls").Close (True)

'remove the Excel object
objExcel.Quit

You will need to replace the path I used with yours.

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 & strHtmlFoot
so its inside the message body.

Regards


Report •

#12
April 7, 2010 at 12:54:59
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.


Report •

#13
April 8, 2010 at 04:33:19
Hi, Razor2.3

Thanks for that.

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.

Regards


Report •

Ask Question