Help sending a worksheet content in email body [RESOLVED]

[cliffw]

Hi All,

Basically I have a workbook which keeps track of how often a list of documents are reviewed. If a document's "Last reviewed date" falls beyond 180 days, conditional formatting turns the cell red to warn the owner.

What I now want to be able to do is to automatically email the owner of those documents to tell them to take action. I've written the below but this will email the entire workbook to the user rather than just the contents (or a range thereof) of a worksheet.

Can this be achieved? If so how?

Many Thanks in advance

Cliff

Private Sub MailDocument()
'This routene mails the customer
Dim stDocName, User As String
Dim MyMessage As Object
User = "cliff@clifforddashwrightdotcodotuk"
ColUpdateoverdue = 9
For a = 2 To Sheets.Count
        Sheets(a).Activate
        For x = 3 To ActiveSheet.UsedRange.Rows.Count
            If Sheets(a).Cells(x, ColUpdateoverdue) = "Yes" Then
            With ThisWorkbook
               '.HasRoutingSlip = False
                With .RoutingSlip
                    .Delivery = xlAllAtOnce
                    .Recipients = User
                    .Subject = "These OSM Documents are due for review"
                    .Message = Sheets(a).Cells(x, 8) & " Document " & Sheets(a).Cells(x, 1) & " is due for review"
                    .ReturnWhenDone = False
                    
                End With
            .Route
            End With
          
           'just for testing only     
            MsgBox Sheets(a).Cells(x, 8) & " Document " & Sheets(a).Cells(x, 1) & " is due for review"
            Else
            End If
        Next x
Next a
 
 
    
End Sub