使用VBA将所有可见(格式文本)从Excel复制到Outlook吗? [英] Copy All Visible(Formatted Text) from Excel to Outlook using VBA?

问题描述

使用VBA将所有可见(格式文本)从Excel复制到Outlook吗?

Copy All Visible(Formatted Text) from Excel to Outlook using VBA?

请找到以下代码以通过Outlook发送电子邮件.但是，我要发送选择的邮件，该邮件是我在下面使用代码复制的. 我不希望将表格创建为HTML，而是只复制所有可见的表格?

Please find below code to send email through outlook. However, I want to send the email with a selection that I have copied below using code. I do not wish to create a Table as HTML but instead just copy all visible?

Sub EmailRep()


Dim Mailbody As Range

Application.DisplayAlerts = False

Dim Outlook As Outlook.Application
Set Outlook = CreateObject("Outlook.Application")
Dim outmail As MailItem
Set outmail = Outlook.CreateItem(0)

Set Mailbody = ActiveWorkbook.Worksheets("Dashboard").Range("A1:F30")
Mailbody.Copy

With outmail

.To = "[e-mail]"
.Subject = "All Open"
.Body = "This is Test Email"
.Display
.Send
End With


Set Outlook = Nothing
Set outmail = Nothing
Set Mailbody = Nothing


End Sub

推荐答案

如果我理解正确，请更改您的行:

If I understand correct change your line of :

Set Mailbody = ActiveWorkbook.Worksheets("Dashboard").Range("A1:F30")

收件人

Set Mailbody = ActiveWorkbook.Worksheets("Dashboard").Range("A1:F30").SpecialCells(xlCellTypeVisible)

尽管在您的代码中，您并未将范围放入电子邮件正文中.起初以为您是手动粘贴范围的，但后来我发现您的代码中有.Send，可以在您有机会粘贴之前发送电子邮件.

Although in your code you are not putting the range into the body of the email. At first thought you pasted the range by hand but then I noticed you have .Send in code which would send the email before you got a chance to paste.

以上两种方法都只会复制可见范围.

Either way the above will copy only the visible range.

如果您对通过电子邮件快速发送范围而不需要复制以下内容感兴趣，这是很简短的做法:

If you are interested in a quick way to send your range in an email without the need to copy the below is pretty short and sweet:

Sub EmailRep()
With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
End With

Range("A1:F30").SpecialCells(xlCellTypeVisible).Select

ActiveWorkbook.EnvelopeVisible = True

With ActiveSheet.MailEnvelope
    .Introduction = "This is Test Email"
    .Item.To = "[e-mail]"
    .Item.Subject = "All Open"
    .Item.Send
End With

ActiveWorkbook.EnvelopeVisible = False

With Application
    .DisplayAlerts = True
    .ScreenUpdating = True
End With
End Sub

这篇关于使用VBA将所有可见(格式文本)从Excel复制到Outlook吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！