Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Next
Hello. I have a bit of code below fron Ron deBruin that e-mails from excel.
In column A of the excel sheet, I have a timestamp of changes. Each row is for a specific person, with their e-mail address in column E. This code will be scheduled to run at midnight every night. What I would like is for this code to e-mail everyone in the list where the timestamp is today - basically email eveyone whos information has changes today. So I think it sounds like some sort of For Next statement? Thanks! Sub Email_Contractor() Dim iMsg As Object Dim iConf As Object Dim cell As Range Dim var As Range Dim strbody As String Application.ScreenUpdating = False If IsEmpty(Cells(ActiveCell.Row, "M")) Then strbody = "You are not authorized to charge against any PO's" Else Set var = Range(Cells(ActiveCell.Row, "M"), _ Cells(ActiveCell.Row, "BJ").End(xlToLeft)) For Each cell In var desc = Application.VLookup(cell.Value, Range("A1500:B2000"), 2, 0) If IsError(desc) Then sStr1 = "" Else sStr1 = " - " & desc End If strbody = strbody & cell.Value & sStr1 & vbNewLine Next End If Set iConf = CreateObject("CDO.Configuration") iConf.Load -1 Set Flds = iConf.Fields With Flds ..Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 ..Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = ..Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 .Update End With Set iMsg = CreateObject("CDO.Message") With iMsg Set .Configuration = iConf .To = Sheets("HR DB").Cells(ActiveCell.Row, "E").Value .From = """Me""" .Subject = "Test" .textBody = strbody .Send End With Set iMsg = Nothing Set iConf = Nothing Application.ScreenUpdating = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|