Writing Excel Value to email (Outlook 2003)

Topic

New Reply

Hi
I want to write an Excel row value to the body of my new mail.

I have the followng but doesn’t seem to write the value to the body of the mail, all its did was open the workbook

Sub SendMailMessage()
Dim objOLapp As Object, obXLapp As Object
Dim obXLWB As Object
Dim objMailItem As Object
Dim strBody As String

Set obXLapp = CreateObject(“Excel.Application”)
Set objOLapp = CreateObject(“Outlook.Application”)
Set objMailItem = objOLapp.CreateItem(0) ‘0=mailitem

With obXLapp
.Visible = True
Set obXLWB = .Workbooks.Open(“C:Documents and Settingsfrancis yeoDesktopMyFile.xls”)
End With

strBody = obXLWB.ActiveSheet.Range(“A1”).Value

With objMailItem
.Body = strBody
End With

End Sub

Thanks In advance

cheers, francis

Reply | Quote

Replies

From where do you want to run this code? From within Excel, from within Outlook, or otherwise?

Reply | Quote

Hi Hans,

Happy New Year to you.

I wanna run this from Outlook.

Thanks in advance

cheers,

Reply | Quote

If you run this code from within Outlook, you shouldn’t create an Outlook.Application object, since Outlook is already running. Also, you can declare objMailItem as MailItem, and you can use the constant olMailItem.

The following works for me (after substituting the path and filename of a workbook on my computer):

Sub SendMailMessage()
Dim obXLapp As Object
Dim obXLWB As Object
Dim objMailItem As MailItem
Dim strBody As String

On Error GoTo ErrHandler

Set obXLapp = CreateObject(“Excel.Application”)
Set obXLWB = obXLapp.Workbooks.Open _
(“C:Documents and Settingsfrancis yeoDesktopMyFile.xls”)
strBody = obXLWB.ActiveSheet.Range(“A1”).Value

Set objMailItem = CreateItem(olMailItem)
With objMailItem
.Body = strBody
.Display
End With

ExitHandler:
On Error Resume Next
obXLWB.Close SaveChanges:=False
Set obXLWB = Nothing
obXLapp.Quit
Set obXLapp = Nothing
Set objMailItem = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

I added error handling, you should always have that when using Automation. Otherwise, you may end up with extra instances of the application you started. Note that it isn’t necessary to make the workbook visible if you only want to retrieve a value from it.

Reply | Quote

Hi Hans,

Thanks, it works.

There appear an error “Type Mismatch” if I change to the following to work for a few row and columns of data. Can’t figure where
did I went wrong.

strBody = obXLWB.ActiveSheet.Range(“A1:F5”).Value

Thanks,

Reply | Quote

Value is a property of each individual cell. A range consisting of multiple cells does not have a single value you can refer to. You could loop through the cells of the range, but the result probably wouldn’t please you. For example:

Dim obXLCell As Object
For Each obXLCell In obXLWB.ActiveSheet.Range(“A1:F5”).Cells
strBody = strBody & obXLCell.Value & vbCrLf
Next obXLCell

Reply | Quote

Hans,

You are right! I don’t like the result. Is there any way to have it shows like a table with rows and column or populate into a table format?

Thanks

Reply | Quote

If you want to mail an Excel table complete with formatting, it is much easier to use File | Send To | Mail Recipient from within Excel. Or to copy and paste the table from Excel into a mail message.

If you want a simple table, you can use the code from the attached text file (zipped to avoid problems with HTML tags in the code).

Reply | Quote

Hi Hans,

Great ! It works accordingly. Thanks for the tip on Excel.

Reply | Quote