Excel, Loop through Column and send emails

[sgtSortor]

I have an excel document that when it's opened it needs to loop through each record and if the date in column (G) is < 90 days ahead, an email needs to be sent to someone.

Here is the code for the loop. All of a sudden it's telling me that an Run-time error '424': "Object Required" and goes to:
For Each cell In rng

Private Sub Workbook_Open()
Dim rng As Range, cell As Range
'Dim FinalRow As Integer
For Each cell In rng

    If cell.Value < Date + 90 Then
        MsgBox cell.Value
        Call EmailComment
    End If
'Next i
Next cell
End Sub

Loop was working not sure where that came from.

In the email I need to reference column (A) (Employer Name) and Column (B) (Inspection ID) in the email and not sure how to do that.

Sub EmailComment()
'For Tips see: [URL="http://www.rondebruin.nl/win/winmail/Outlook/tips.htm"][COLOR=#0066cc]http://www.rondebruin.nl/win/winmail/Outlook/tips.htm[/COLOR][/URL]
'Working in Office 2000-2013
Dim xDate As Date, NotifDate As Date, InspEmployer As String, InspID
Dim i As Long
xDate = cell.Value
NotifDate = xDate - 90
InspEmployer = testInspEmployer
InspID = testInspID
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    strbody = "Inspection Report Needed for " & InspEmployer & " - " & InspID & " - " & xDate & vbNewLine & vbNewLine
    On Error Resume Next
    With OutMail
        .To = "MyEmail"
        .CC = ""
        .BCC = ""
        .Subject = "Report Needed!"
        .Body = strbody
        'You can add a file like this
        '.Attachments.Add ("C:\test.txt")
        .Display   'or use .Display
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Help please,

 

[Rx_]

The CurrentRegion is the rectangular shape of the data. Start the loop at the top left of the currentregion. Best practice to use the .Cells notation.

The current region is a bonus as you know what the top left starting point for data is, Current Region ends at the last based on lower left.
If the data doesn't start at column A, the column number is relative to the CurrentRegion.

[FONT=Courier New]Sub LoopThroughCells()[/FONT]
[COLOR=#000000][FONT=Courier New]Set MyRng = Range("A1").CurrentRegion ' assume A1 is currentregion top left[/FONT][/COLOR]
[COLOR=#000000][FONT=Courier New]For Each MyCell In MyRng.Columns(1).Cells ' assume Column 1 in CurrentRegion is the one you are qualifying with a condition.[/FONT][/COLOR]
[COLOR=#000000][FONT=Courier New]MyCell.Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Courier New]' Now do something with this cell - the criteria and action[/FONT][/COLOR]
[COLOR=#000000][FONT=Courier New]Next MyCell[/FONT][/COLOR]
[COLOR=#000000][FONT=Courier New]End Sub[/FONT][/COLOR]

or

[FONT=Courier New]Sub LoopThroughCells()[/FONT]
[COLOR=#000000][FONT=Courier New]Set MyRng = Range("A1").CurrentRegion[/FONT][/COLOR]
[COLOR=#000000][FONT=Courier New]Set MyRng = MyRng.Columns(1)[/FONT][/COLOR]
[COLOR=#000000][FONT=Courier New]For Each MyCell In MyRng.Cells[/FONT][/COLOR]
[COLOR=#000000][FONT=Courier New]MyCell.Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Courier New]Next MyCell[/FONT][/COLOR]
[COLOR=#000000][FONT=Courier New]End Sub[/FONT][/COLOR]

 

[Rx_]

Example attached - Excel loops through cells
This is a macro enabled Excel workbook, the macro must be enabled

Many of the Excel demo's online leave out the Option Explicit.
This seems to cause a lot of confusion about the data types.
This is a very simple example of assigning a macro to a button.

It actually loops through a cell in a column, then while the cell is selected, allows it to be evaluated for some additional action.

The data moved into an Excel worksheet doesn't always start at A1.
In this example, the data starts somewhere else. When moving the data into a worksheet, make it a Named Range.
This code sets the selection in the Named Range. Then, it can select a different column based on the position of the Named Range.

While this is a loop that allows for a nice single-step code interaction, for larger datasets, the offset method is typically faster.

If you send an email, my suggestion would be to either bold the ones that had an email sent, or to add a Comment with the detials of the email (time stamp) to the cell.

See my recent post about Box.com
I had to stop my regular SQL Server programming activities to create some custome legal reports based on hundreds of thousands of filtered, commented emails. Emails are becomming the new legal certified letter.