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

[AshleyStraw]

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.

[gmayor]

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

[AshleyStraw]

Thank you very much, I see what was missing now!