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

[RobDog888]

Yes, you can copy the data that you want to email into a new workbook and then email that using the .SendMail method instead.

[cliffw]

True - but I really wanted the content of the email to be say for example range A1:J10, appearing directly as if I'd opened an email and pasted this into the body iteself so that there are no attachments to the email ...

That possible?

Cheers

Cliff

[RobDog888]

Yes, do you have Outlook? If not we can still do it.

[cliffw]

Yes I'm using outlook

[RobDog888]

Then you can automate Outlook from within Excel by adding a reference to Outlook and writting code to create a new email.

Or if you want to add basic text to the email body you can use the ShellExecute API and pass teh &body= argument with the text to add.

So you didnt like the create new workbook with the data and send that object instead?

[cliffw]

So you didnt like the create new workbook with the data and send that object instead?

No not really ... it would serve a purpose but ...

The spreadsheet is a central tracking spreadsheet for a department .. the purpose of the email is to warn individual owners of documents that they should review their document which is 'logged' on this spreadsheet.

If I were to send this entire spreadsheet as an attachment, users might be tempted to update the attached version rather than the centrally stored version and hence cause me issues ...

By purely having the body of the email showing a range of cells, this would allow me to get the message across that document X needs reviewing, whilst avoiding the temptation for users to update anything other than the centrally stored tracking spreadsheet .. make sense?

Then you can automate Outlook from within Excel by adding a reference to Outlook and writting code to create a new email.

Can you give me a hint at the code?

Thanks

Cliff

[RobDog888]

Sure, but I have a massive amount of Outlook code on the forums but...

Option Explicit
'Add a reference to ms outlook xx.0 object library
Private Sub Command1_click()
 
    Dim oApp As outlook.application
    dim oEmail as outlook.mailitem
 
    set oapp = new outlook.application
    set oemail =- oapp.createitem(olOutlookEMailitem)
    oemail.body = "Your excel range or cell(s) data"
    oemail.to = "[email protected]"
    oemail.send
    set oemail = nothing
    set oapp = nothing
end sub

[cliffw]

Thanks for this .. my knowledge of non-excell VBA is pretty shoddy ...

Using this code I get a "Compile Error; User-defined type not defined" ...

What's that about then?

[RobDog888]

'Add a reference to ms outlook xx.0 object library

[cliffw]

Sheesh ... thanks for the speedy response ... have added the reference that I managed to miss ... doh!

But ... now I get an error saying that "olOutlookEMailitem" isn't defined ... Am I being blonde still ?

[RobDog888]

Oops, thats what I get for freehand typing it into the reply box. It should be olMailItem.

[cliffw]

Hmmm ... I did try this but thought it wasn't right because I get a 'Type mismatch' error (olMailItem seems to have a default value of '0' if this helps)

Any ideas ?

Much appreciate your help here

[RobDog888]

Another type-o. this line should be like this.

set oemail = oapp.createitem(olMailitem)

[cliffw]

Brilliant!

I had wondered what the - was for ... should've known to try it ...

Works like a charm now ... Thanks again for your help

Cliff