Hello all,
I have code below doing so that if the due date is date - 1 then it will turn that column cells red. it also sends out email with relevant information in that row. the code is below:
Private Sub Workbook_WindowActivate(ByVal Wn As Window)

Dim cl As Range
Dim ws As Worksheet
Set ws = Sheets("A-D")

For Each cl In ws.Range("G2:G30")
If cl.Value = Date - 1 Then
cl.Interior.Color = vbRed
cl.Font.Color = vbWhite
   
        Dim olapp As Object
        Dim olns As Object
        Dim olfolder As Object
        Dim olitem As Object
        Dim olattach As Object
        
        Set olapp = CreateObject("Outlook.Application")
        Set olns = olapp.GetNamespace("MAPI")
        Set olfolder = olns.getdefaultfolder(6)
        Set olitem = olapp.createitem(0)
        'Set olattach = olitem.attachments
       
        olitem.To = "abc@xyz.om"
     
        olitem.Subject = "Due Dates"
       
        olitem.body = "Email Automatically Generated By Microsoft Excel on " & Now() _
                      & Chr(13) _
                      & Chr(13) _
                      & "To Management" _
                      & Chr(13) _
                      & Chr(13) _
                      & "Rate review due date for " & Date + 1 _
                      & Chr(13) _
                      & Chr(13) _
                      & "|Company|           |Due Date|             |Person Responsible|" _
                      & Chr(13) _
                      & cl.Offset(, -6) & "                     " & cl.Offset(, -1) & "                 " & cl.Offset(, 1) _
                      & Chr(13) _
                      & Chr(13) _
                      & "Regards" _
                      & Chr(13) _
                      & "Forename Surname" _
                     & Chr(13) _
                      & "xyz Manager" _
                      & Chr(13) _
                      & "Company Ltd" _
                     & Chr(13) _
                      & "Phone: 00000" _
                      & Chr(13) _
                     & "Fax:     00000"         
        
       
        olitem.display
        
        'olitem.send
        
        Set olitem = Nothing
        Set rs = Nothing
        Set Db = Nothing
        Set olfolder = Nothing
        Set olns = Nothing
        Set olapp = Nothing

Set ws = Nothing

Else
cl.Interior.ColorIndex = xlNone
cl.Font.ColorIndex = vbBlack

End If
Next

End Sub
above code creates email body as:
Email Automatically Generated By Microsoft Excel on 12/03/2007 13:14:19
To Management
Rate review due date for 13/03/2007
|Company| |Due Date| |Person Responsible|
ABC 11/03/2007 John smith

Regards
Forename Surname
xyz Manager
Company Ltd
Phone: 00000
Fax: 00000
********
2nd email:

Email Automatically Generated By Microsoft Excel on 12/03/2007 13:14:19
To Management
Rate review due date for 13/03/2007
|Company| |Due Date| |Person Responsible|
XYZ 11/03/2007 Peter Dudd

Regards
Forename Surname
xyz Manager
Company Ltd
Phone: 00000
Fax: 00000
*********
which is fine. but when it has several rows with due dates it will create separate email for each. i want to combile all emails so that it looks like below:

Email Automatically Generated By Microsoft Excel on 12/03/2007 13:14:19
To Management
Rate review due date for 13/03/2007
|Company| |Due Date| |Person Responsible|
ABC 11/03/2007 John smith
XYZ 11/03/2007 Peter Dudd

Regards
Forename Surname
xyz Manager
Company Ltd
Phone: 00000
Fax: 00000
****************

Any help will be greatly appreciated. Thank you