Dear Friends,
Greetings to you all!
I used Mr.Domenic code for this:
Option ExplicitSub test() Dim OutlookApp As Outlook.Application Dim MItem As Outlook.MailItem Dim Rng As Range Dim Rw As Range Application.ScreenUpdating = False ActiveSheet.AutoFilterMode = False With ActiveSheet.UsedRange .AutoFilter field:=5, Criteria1:=">=" & Date, Operator:=xlAnd, Criteria2:="<=" & Date On Error Resume Next Set Rng = .Resize(.Rows.Count - 1).Offset(1, 0).SpecialCells(xlCellTypeVisible) On Error GoTo 0 End With If Not Rng Is Nothing Then Set OutlookApp = CreateObject("Outlook.Application") For Each Rw In Rng.Rows Set MItem = OutlookApp.CreateItem(olMailItem) With MItem .To = Rw.Cells(1, 1).Value .CC = "" .BCC = "" .Subject = Rw.Cells(1, 3).Value .Body = Rw.Cells(1, 4).Value .Save End With Next Rw Else MsgBox "No birthdays today!", vbExclamation End If ActiveSheet.AutoFilterMode = False Application.ScreenUpdating = True End Sub</pre>
I use Excel 2007. The works fine for dates with current year. And does not pick up older years with the same month and day. I tested with the following data:
<tbody>
</tbody>
Excel picked only the row with today's row: 04-03-2013
and left out 1999 and 2001.
Please help
Greetings to you all!
I used Mr.Domenic code for this:
Option ExplicitSub test() Dim OutlookApp As Outlook.Application Dim MItem As Outlook.MailItem Dim Rng As Range Dim Rw As Range Application.ScreenUpdating = False ActiveSheet.AutoFilterMode = False With ActiveSheet.UsedRange .AutoFilter field:=5, Criteria1:=">=" & Date, Operator:=xlAnd, Criteria2:="<=" & Date On Error Resume Next Set Rng = .Resize(.Rows.Count - 1).Offset(1, 0).SpecialCells(xlCellTypeVisible) On Error GoTo 0 End With If Not Rng Is Nothing Then Set OutlookApp = CreateObject("Outlook.Application") For Each Rw In Rng.Rows Set MItem = OutlookApp.CreateItem(olMailItem) With MItem .To = Rw.Cells(1, 1).Value .CC = "" .BCC = "" .Subject = Rw.Cells(1, 3).Value .Body = Rw.Cells(1, 4).Value .Save End With Next Rw Else MsgBox "No birthdays today!", vbExclamation End If ActiveSheet.AutoFilterMode = False Application.ScreenUpdating = True End Sub</pre>
I use Excel 2007. The works fine for dates with current year. And does not pick up older years with the same month and day. I tested with the following data:
Name | From | Subject | Birthday |
mkp@123.com | 234@123.com | Hello | 04-03-2013 |
mkp@123.com | 635@1213.com | HI | 04-03-1999 |
123@123.com | 635@1213.com | hello | 04-03-2001 |
<tbody>
</tbody>
Excel picked only the row with today's row: 04-03-2013
and left out 1999 and 2001.
Please help