Consulting

Results 1 to 3 of 3

Thread: Outlook Body Text - Content out of order

  1. #1
    VBAX Regular
    Joined
    Apr 2020
    Location
    Perth, Western Australia
    Posts
    6
    Location

    Outlook Body Text - Content out of order

    I am in the final stages of building a system that analyses student scores to determine which cell in a Word Document table is selected to provide a standardised report. Because this needed hyperlinks included in the message, the text was copied from the pertinent cell and pasted into Outlook through the Word Editor. Above this copied text I need to insert an introduction line (e.g. Dear xxx with the name drawn from the spreadsheet). Below this, I need to insert a signature block (e.g. Yours sincerely, etc.), which is also drawn from a cell in the spreadsheet. When the text builds in the Outlook message body it orders the content as:
    Introduction (e.g. Dear)
    Signature Block
    Body Text copied from Table Cell

    I have enclosed a code block snippet that produces this outcome below. Having instantiated the Inspector, Word Editor and Range Objects. I have then tried to insert the material into the body range using a variety of strategies, which include Collapse and InsertBefore and insert after. Interestingly, if I just insert the Introduction and Body (as pasted from the table cell) this works fine. It is the signature block that is the problem. If someone could please give me some advice, this would be very much appreciated.

    --------------------------------------
    Set OLMail = appOL.CreateItem(olMailItem) 'This creates a mail object
    Set WDInspector = OLMail.GetInspector 'This starts the inspector object
    Set OLEditor = WDInspector.WordEditor 'This makes the cell content available to the Word Editor in Outlook
    Set OLRange = OLEditor.Range 'This sets the Editor range object that will be inserted into the email
    ' Now add the various elements into the email
    With OLMail
    .Display
    .To = rngStudentEmail.Offset(0, 6) 'This is the email address for the student
    .CC = rngCCEmails.Value 'This draws from the Source Sheet to include CC addressees if required
    .Subject = rngTitle.Value 'This draws from the Title that is provided in the Source Sheet
    .BodyFormat = olFormatHTML 'This sets the body format as HTML so that the links will work
    OLRange.Collapse Direction:=wdCollapseStart 'This moves the range to the start of the email body
    OLRange.Text = "Dear " & rngStudentEmail.Offset(0, 9) & "," & vbCrLf & vbCrLf
    'OLRange.InsertBefore ("Dear " & rngStudentEmail.Offset(0, 9) & "," & vbCrLf & vbCrLf) 'This inserts the introduction line, which uses the full name drawn from the Student Worksheet
    OLRange.Collapse Direction:=wdCollapseEnd
    OLRange.PasteAndFormat (wdFormatSurroundingFormattingWithEmphasis) 'This pastes the copied reference (including Dear... and the Signature block into the cell) _
    Please note that this ensures that what is copied in uses the default formatting used for the emails
    OLRange.Collapse Direction:=wdCollapseEnd
    OLRange.text =
    vbCrLf & "Yours sincerely," & vbCrLf & vbCrLf & vbCrLf & rngSigBlock.Value & vbCrLf
    'OLRange.InsertAfter (vbCrLf & "Yours sincerely," & vbCrLf & vbCrLf & vbCrLf & rngSigBlock.Value & vbCrLf) 'This adds the signature block onto the end of the email. _
    '.Save
    .Send
    End With

  2. #2
    I posted how to do this in response to your earlier question - http://www.vbaexpress.com/forum/show...o-Outlook-Body

    When using that method, the default signature associated with the account is retained. Given that we have no idea what most of the variables in your current question relate to it is impossible to determine what the issue you are having is. Note that if you are using later binding to Outlook as I suggested, you cannot use Outlook specific commands such as olFormatHTML. You must use the numeric equivalents e.g. in this case 2.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    VBAX Regular
    Joined
    Apr 2020
    Location
    Perth, Western Australia
    Posts
    6
    Location
    Dear Graham, Firstly, many thanks for your guidance. I went back and did some more experimenting based on your advice. The problem seems to stem from the application of PasteAndFormat WordRecoveryType. In essence the problem is resolved if I just use Range.Paste, but it appears when I use Range.PasteAndFormat. I had been aiming to use this and the WordRecoveryType 20 (wdFormatSurroundingFormattingWithEmphasis) as different lecturers will be using this tool and I wanted it to match their common formatting standards when it brought in the copied text. Do you have any suggestions on how to resolve this? Thank you again for your help. If I can repay the kindness please let me know.

Posting Permissions

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