This is a portion of my sheet that I receive help on from Barry Houdini and Tom Urtis for the formulas in columns G:H.
http://www.mrexcel.com/board2/viewtopic.php?t=266549
I am trying to send an email notice with the portion of the filtered list attached so the boss can send his regards to the individual prior to the Birthday or YRS - SVC date.
I have loaded the workbook in the XL Start folder so when Excel is opened this book will also open and the code execute.
If Outlook is open, prior to Excel open, it will send a Draft. If Outlook is closed, prior to Excel open it will send an email. Both are okay.
The problem is, it will send the same email every day (or each time Excel is opened)
What I would like it to do is once the email is sent, it will somehow mark that it has been sent and will not include that particular range in the filter next time the book opens.
That is, in the above example, the book opens and sends email with all info from rows 5, 7, 9, & 12. The next time Excel opens I would not need those rows since the email was already generated, but I would need any new rows that fell within 30 days prior to the dates in "C & D".
This is the code that I am using, found through Search.
If further explanation is required, let me know.
Thanks
Harry
http://www.mrexcel.com/board2/viewtopic.php?t=266549
| Birthday 3.xls | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 1 | 4/7/2007 | FILTER | |||||||||
| 2 | B'DAY | YRS-SVC | FILTER | ||||||||
| 3 | BRANCH | NAME | DOB | HIREDATE | AGE | YRS-SVC | MESSAGE | MESSAGE | FILTER | ||
| 4 | GSP | TOMCLANCEY | 1/10/1953 | 4/4/1981 | 54 | 26 | |||||
| 5 | GSP | SIDNEYSHELDON | 4/20/1961 | 10/26/1990 | 45 | 16 | YES | 1 | |||
| 6 | GSP | ROSEMARYROGERS | 6/8/1965 | 5/9/2001 | 41 | 5 | |||||
| 7 | J-FSP | STEVENKING | 4/28/1940 | 5/4/1995 | 66 | 11 | YES | YES | 1 | ||
| 8 | J-FSP | JUDITHMcNAUGHT | 10/10/1936 | 6/19/2002 | 70 | 4 | |||||
| 9 | J-FSP | ROSEMARYROGERS | 3/6/1971 | 5/1/1994 | 36 | 12 | YES | 1 | |||
| 10 | L-IWS | JOHNGRISHAM | 8/14/1946 | 12/22/1985 | 60 | 21 | |||||
| 11 | L-IWS | SCOTTTUROW | 1/26/1946 | 2/14/1987 | 61 | 20 | |||||
| 12 | L-IWS | DANIELLESTEEL | 5/1/1970 | 6/27/2004 | 36 | 2 | YES | 1 | |||
Employees | |||||||||||
I am trying to send an email notice with the portion of the filtered list attached so the boss can send his regards to the individual prior to the Birthday or YRS - SVC date.
I have loaded the workbook in the XL Start folder so when Excel is opened this book will also open and the code execute.
If Outlook is open, prior to Excel open, it will send a Draft. If Outlook is closed, prior to Excel open it will send an email. Both are okay.
The problem is, it will send the same email every day (or each time Excel is opened)
What I would like it to do is once the email is sent, it will somehow mark that it has been sent and will not include that particular range in the filter next time the book opens.
That is, in the above example, the book opens and sends email with all info from rows 5, 7, 9, & 12. The next time Excel opens I would not need those rows since the email was already generated, but I would need any new rows that fell within 30 days prior to the dates in "C & D".
This is the code that I am using, found through Search.
Code:
Sub SendRange()
'Sends a specified range in an Outlook message and retains Excel formatting
'**************************
'http://www.danielklann.com
'**************************
'Dimension variables
Dim oOutlookApp As Object, oOutlookMessage As Object
Dim oFSObj As Object, oFSTextStream As Object
Dim rngeSend As Range, strHTMLBody As String, strTempFilePath As String
Dim ws As Worksheet
Set ws = Worksheets("Employees")
Worksheets("Employees").Activate
With ws.Columns("I:I").Select
Selection.AutoFilter Field:=1, Criteria1:="<>"
End With
'Procedure constants
Const lFSO_OPEN_FOR_READING As Long = 1
Const lFSO_TEMP_FOLDER As Long = 2
Const lOUTLOOK_MAILITEM As Long = 0
'Select the range to be sent
On Error Resume Next
Set rngeSend = Worksheets("Employees").Range("A1:H300")
If rngeSend Is Nothing Then Exit Sub 'User pressed Cancel
On Error GoTo 0
'Get the temp folder path
Set oFSObj = CreateObject("Scripting.FilesystemObject")
strTempFilePath = oFSObj.GetSpecialFolder(lFSO_TEMP_FOLDER)
strTempFilePath = strTempFilePath & "\XLRange.htm"
'Now create the HTML file - NOTE! xlSourceRange and xlHtmlStatic have been replaced by their
'numeric values due to a potential error (unexplained) noted by Ivan F Moala 15/5/03
ActiveWorkbook.PublishObjects.Add(4, strTempFilePath, _
rngeSend.Parent.Name, rngeSend.Address, xlSourceWorkbook, "", "").Publish True
'Create an instance of Outlook (or use existing instance if it already exists
Set oOutlookApp = CreateObject("Outlook.Application")
'Create a mail item
Set oOutlookMessage = oOutlookApp.CreateItem(lOUTLOOK_MAILITEM)
'Open the HTML file using the FilesystemObject into a TextStream object
Set oFSTextStream = oFSObj.OpenTextFile(strTempFilePath, lFSO_OPEN_FOR_READING)
'Now set the HTMLBody property of the message to the text contained in the TextStream object
strHTMLBody = oFSTextStream.ReadAll
'By default the range will be centred. This line left aligns it and you can
'comment it out if you want the range centred.
'strHTMLBody = Replace(strHTMLBody, "align=center", "align=left", , , vbTextCompare)
oOutlookMessage.To = "harry@test.com" '//masked for posting
oOutlookMessage.Subject = "Regards Notice"
oOutlookMessage.HTMLBody = strHTMLBody
'oOutlookMessage.Display
oOutlookMessage.Save
ws.AutoFilterMode = False
'Clean up
oFSTextStream.Close
Set oFSTextStream = Nothing
Set oFSObj = Nothing
Kill strTempFilePath
End SubIf further explanation is required, let me know.
Thanks
Harry
