Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel and OUtlook (XP)

    I use the following code to distribute an Email to various recipients. I have noticed that the row spacing on the Email message is not consistent.

    Sub NewEmailCode()
    Dim objOLook As New Outlook.Application
    Dim objOMail As MailItem
    Dim i As Long

    Set objOLook = New Outlook.Application
    Set objOMail = objOLook.CreateItem(olMailItem)
    oP01 = Range("P_01")
    oP02 = Range("P_02")
    oP03 = Range("P_03")
    oP04 = Range("P_04")
    oP05 = Range("P_05")
    oVar1 = Range("R_SupervisorEmail")

    With objOMail
    .To = "" & oVar1 & ""
    .Subject = "Your Subject Matter Here"
    .Body = .Body & oP01 & vbCrLf & oP02 & vbCrLf & oP03 & vbCrLf & oP04 & vbCrLf & oP05
    ' .Display
    .Send
    End With
    Set objOMail = Nothing
    Set objOLook = Nothing
    End Sub


    Each rangename in the workbook ie Range("P_XX") contains text which I would like to place in the Email message as a new paragraph. I would expect to see a blank row between paragraphs but sometimes a row is missing ie combining the paragraphs.

    Example:
    This is paragraph one.

    This is paragraph two.

    This is paragraph three.

    This is paragraph four.

    This is what I sometimes see (not always)

    This is paragraph one.

    This is paragraph two
    This is paragraph three

    This is paragraph four



    Thanks,
    John

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

    Re: Excel and OUtlook (XP)

    This seems to be more a problem with Outlook than with Excel, unless you have entered line feeds in the Excel cells with Alt+Enter.

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Excel and OUtlook (XP)

    Which version of Outlook, and what is the default message format setting, HTM, RTF or plain text? One thought that comes to mind is that since you are setting the ranges to an undeclared and therefore variant variable, you can't be sure what type they are being set to; so you might want to get your range content in this form:

    oP01 = Trim(CStr(Range("P_01").Value)) & vbLf & vbLf
    <etc.>

    then your concatenation is simpler:

    .Body = oP01 & oP02 & oP03 & oP04 & oP05
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel and OUtlook (XP)

    John,

    I am using Outlook 2002 and the default message format setting is HTM.

    I tried your suggestion and it worked.

    Thanks,
    John

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Excel and OUtlook (XP)

    For HTML, using the line break tag might be cleaner than using vbLf:

    oP01 = Trim(CStr(Range("P_01").Value)) & ""

    You might also consider builing the .Body this way:

    .Body = Trim(CStr(Range("P_01").Value)) & ""
    .Body = .Body & Trim(CStr(Range("P_02").Value)) & ""
    .Body = .Body & Trim(CStr(Range("P_03").Value)) & ""
    .Body = .Body & Trim(CStr(Range("P_04").Value)) & ""
    .Body = .Body & Trim(CStr(Range("P_05").Value)) & ""
    .Body = .Body & Trim(CStr(Range("R_SupervisorEmail").Value)) & ""

    & skip the concatenation.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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