PDA

View Full Version : Solved: Email an excel sheet range using outlook.



DerbyNeal
12-09-2008, 05:05 AM
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

xld
12-09-2008, 05:26 AM
have you been to Ron de Bruin's site, http://www.rondebruin.nl, I know that he covers that topic.

DerbyNeal
12-09-2008, 05:44 AM
Excellent reference.

Many Thanks!

JP2112
12-10-2008, 05:13 AM
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
12-10-2008, 11:51 AM
JP's works for me too. Be sure to set the reference to the msOutlook object library....

JP2112
12-10-2008, 01:44 PM
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....