Consulting

Results 1 to 3 of 3

Thread: Paste content of Excel sheet as Unformatted Unicode Text in email body

  1. #1

    Paste content of Excel sheet as Unformatted Unicode Text in email body

    Hello,


    The Outlook VBA code below paste into the body of an email the content of some cells in the excel sheet "example.xlsx".
    I would like the content of the excel sheet to be pasted as paste special "Unformatted Unicode Text" in the outlook email.
    I have found on google that the command wdFormatPlainText should be used and I have tried to implement it in the code below but it doesn't work, the content of the excel cells still gets copied with formatting.


    Sub CopyFromExcel()
        Dim myXL As Excel.Application
        Dim wb As Excel.Workbook
        Dim myMail As MailItem
        
        Set myXL = GetObject(, "Excel.Application")
        Set wb = myXL.Workbooks("example.xls")
        
        wb.RefreshAll
        wb.Worksheets(1).Range("A1:A102").Copy
        Set myMail = ThisOutlookSession.CreateItem(olMailItem)
        With myMail
            .Display
            '.GetInspector.WordEditor.Range.Paste 'Original code pasting with formatting
            .GetInspector.WordEditor.Range.PasteAndFormat wdFormatPlainText 'code supposed to paste without formatting - not working as formatting still there
            .To = "emailAddress"
            .Display
            '.Send
        End With
    
    
    End Sub
    Could someone explain how I can paste the content of the excel cells with the paste special "unformatted unicode text" option?


    Thank you very much.

  2. #2
    This is Outlook, not Word so you are going to have to tell the macro what wdFormatPlanText means (i.e it's a value of 22). All wdCommands have numeric equivalents that are required instead of the text versions of the commands.

    Use of the Range object will allow you to retain your default signature associated with the account, but putting the list at the start of the message.

    Sub CopyFromExcel()
        Dim myXL As Object
        Dim wb As Object
        Dim myMail As MailItem
        Dim oRng As Object
        Const wdFormatPlainText As Long = 22
        
        Set myXL = GetObject(, "Excel.Application")
        Set wb = myXL.Workbooks("Example.xls")
        
        wb.RefreshAll
        wb.Worksheets(1).Range("A1:A102").Copy
        Set myMail = ThisOutlookSession.CreateItem(olMailItem)
        With myMail
            .Display
            Set oRng = .GetInspector.WordEditor.Range
            oRng.collapse 1
            oRng.PasteAndFormat wdFormatPlainText 'code supposed to paste without formatting - not working as formatting still there
            .To = "emailAddress"
            .Display
            '.Send
        End With
    End Sub
    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
    Thank you very much, I see what was missing now!

Tags for this Thread

Posting Permissions

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