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
Bob Phillips
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....
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.