如何用VBA实现与Ctrl+V一致的无格式粘贴（Excel导入邮件文本）

I totally get your frustration—manual paste works perfectly, but getting VBA to match that behavior can be tricky when standard PasteSpecial options mess up your layout. Let's fix this with two reliable approaches:

Approach 1: Use xlPasteUnicodeText to Mimic Manual Paste #

This option replicates the clean, single-column result you get with Ctrl+V, without bringing over extra formatting that might break your sheet:

' Make sure you've already copied the email text to the clipboard first
Range("BC1").Select
ActiveCell.PasteSpecial Paste:=xlPasteUnicodeText

Why this works: When you manually paste, Excel interprets line breaks in the email text to split content into separate rows in a single column. xlPasteUnicodeText preserves this line-break structure while stripping unnecessary formatting, matching exactly what Ctrl+V does.

Approach 2: Directly Read Clipboard Content and Split into Column #

If the first method still gives you issues, you can take full control by reading the clipboard text and splitting it into cells manually. This requires referencing the Microsoft Forms 2.0 Object Library:

1. Open the VBA editor (Alt+F11)
2. Go to Tools > References, check "Microsoft Forms 2.0 Object Library" and click OK

Then use this code:

Dim clipboard As DataObject
Dim textLines As Variant

Set clipboard = New DataObject
clipboard.GetFromClipboard

' Split the clipboard text by line breaks (adjust vbCrLf to vbLf if needed for your email format)
textLines = Split(clipboard.GetText, vbCrLf)

' Write the split lines into column BC starting at row 1
Range("BC1").Resize(UBound(textLines) + 1, 1).Value = Application.Transpose(textLines)

This method ensures every line from the email ends up in its own cell in column BC, no layout surprises.

Either of these should give you the same result as your manual Ctrl+V paste, so you can run your follow-up macro on the clean single-column data.

内容的提问来源于stack exchange，提问作者Robin Jensen