Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Writing Excel Value to email (Outlook 2003)

    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("Cocuments 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
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Writing Excel Value to email (Outlook 2003)

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

  3. #3
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing Excel Value to email (Outlook 2003)

    Hi Hans,

    Happy New Year to you.

    I wanna run this from Outlook.

    Thanks in advance

    cheers,
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Writing Excel Value to email (Outlook 2003)

    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 _
    ("Cocuments 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.

  5. #5
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing Excel Value to email (Outlook 2003)

    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,
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Writing Excel Value to email (Outlook 2003)

    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

  7. #7
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing Excel Value to email (Outlook 2003)

    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
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Writing Excel Value to email (Outlook 2003)

    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).

  9. #9
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing Excel Value to email (Outlook 2003)

    Hi Hans,

    Great ! It works accordingly. Thanks for the tip on Excel.
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •