Solved: Email an excel sheet range using outlook.

[DerbyNeal]

Hi,

I need an automated routine that will email a range of cells from an excel spreadsheet (using outlook).

I've investigated various sites, but found nothing helpful as yet.

For Example:


Dim objOL As New Outlook.Application
Dim objMail As MailItem
Set objOL = New Outlook.Application
Set objMail = objOL.CreateItem(olMailItem)
With ActiveSheet
With objMail
.To = "user@domain.com"
.Subject = "These are the excel results."
.Body = "See the table below: " & vbCrLf & _
Range("R33", "AW196").Value
End With
End With
Set objOL = Nothing
Set objMail = Nothing


Note:
I'm using Excel 2003

Any ideas?

Thanks in advance,

Neal

[Bob Phillips]

have you been to Ron de Bruin's site, http://www.rondebruin.nl, I know that he covers that topic.

[DerbyNeal]

Excellent reference.

Many Thanks!

[JP2112]

Did you test that code? It worked for me, except for the Range part. Here's an updated version.



Sub testme()
Dim objOL As Outlook.Application
Dim objMail As Outlook.MailItem

Set objOL = New Outlook.Application
Set objMail = objOL.CreateItem(olMailItem)

With objMail
.To = "user@domain.com"
.Subject = "These are the excel results."
.Body = "See the table below: " & vbCrLf & Range("R33").Value
.Display
End With

Set objOL = Nothing
Set objMail = Nothing
End Sub




Hi,

I need an automated routine that will email a range of cells from an excel spreadsheet (using outlook).

I've investigated various sites, but found nothing helpful as yet.

For Example:


Dim objOL As New Outlook.Application
Dim objMail As MailItem
Set objOL = New Outlook.Application
Set objMail = objOL.CreateItem(olMailItem)
With ActiveSheet
With objMail
.To = "user@domain.com"
.Subject = "These are the excel results."
.Body = "See the table below: " & vbCrLf & _
Range("R33", "AW196").Value
End With
End With
Set objOL = Nothing
Set objMail = Nothing

Note:
I'm using Excel 2003

Any ideas?

Thanks in advance,

Neal

[lucas]

JP's works for me too. Be sure to set the reference to the msOutlook object library....

[JP2112]

Here's a late bound version:



Sub testme()
Dim objOL As Object
Dim objMail As Object

On Error Resume Next
Set objOL = GetObject(,"Outlook.Application")
On Error Goto 0

If objOL Is Nothing Then
Set objOL = CreateObject("Outlook.Application")
End If

If objOL Is Nothing Then Goto ExitProc

Set objMail = objOL.CreateItem(0)

With objMail
.To = "user@domain.com"
.Subject = "These are the excel results."
.Body = "See the table below: " & vbCrLf & Range("R33").Value
.Display
End With

ExitProc:
Set objOL = Nothing
Set objMail = Nothing
End Sub


JP's works for me too. Be sure to set the reference to the msOutlook object library....