将Excel范围粘贴到Outlook中

问题描述

我正在升级Excel宏.我想在每天更改范围的表中生成电子邮件副本.

I'm upgrading an Excel macro. I want to generate an email copying in a table that changes range daily.

Strbody会填充电子邮件，但时间表未附加.

Strbody populates the email but the timetable isn't attaching.

Sub Ops_button()
'Working in Office 2000-2010
Dim Outapp As Object
Dim Outmail As Object
Dim Strbody As String
Dim Timetable As String

'Auto Email Attachment Variables
Set Outapp = CreateObject("Outlook.Application")
Set Outmail = Outapp.createitem(0)
Timetable = Sheets("sheet1").Range("C2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
Strbody = "body text."

On Error Resume Next
With Outmail

    'Send email
    .To = ""
    .bcc = ""
    .Subject = "Report" & " " & Format$(Date, "dd-mm-yyyy")
    .body = Strbody & Timetable

    On Error Resume Next
    .Display
End With

On Error GoTo 0
Set Outmail = Nothing
Set Outapp = Nothing

End Sub

推荐答案

您不能以尝试的方式执行此操作...让我们看看为什么不行:)

You can't do this the way you're trying to do it... Let's see why not :)

您已将Timetable声明为String类型的变量.在此语句中，您将其值分配为.Select方法的返回值(如果没有错误，该方法将返回True的值).

You've declared Timetable as a String type variable. In this statement, you're assigning its value as the return from the .Select method (which will return a value of True if there is no error).

Timetable = Sheets("sheet1").Range("C2").Select

因此，在以下语句中，您绝不会将表的Range对象附加到字符串中

So, you're in no way appending the Table's Range object to the string, in this statement:

.body = Strbody & Timetable

实际上，您确实需要将表转换为HTML或直接将范围从Excel复制并粘贴到Word.

Instead, you really need to either convert the table to HTML or copy and paste the range directly from Excel to Word.

1. 使用Ron de Bruin的函数将表转换为HTML PublishObject并将其插入到电子邮件中，或者
2. .Display MailItem，然后获取MailItem的.Inspector对象(实际上只是Word文档)的句柄

1. Use Ron de Bruin's function to convert the table to an HTML PublishObject and insert that to the email, or
2. .Display the MailItem and then get a handle on the MailItem's .Inspector object (which is really just a Word document)

对于解决方案1，请在此处调整已经给出的答案:

For the solution 1, adapt the answer already given, here:

在Outlook中粘贴特定Excel范围

对于解决方案2，您需要使用此处概述的方法来获取检查器(代表电子邮件项目的Word文档):

For the solution 2, you'll need to use the method outlined here to get the Inspector (Word Document representing the Email item):

https://msdn.microsoft.com/en-us /library/office/ff868098.aspx

然后，Dim TimeTable as Range，然后将代码更改为:

Then, Dim TimeTable as Range, and change code to:

Set Timetable = Sheets("sheet1").Range("C2").End(xlToRight).End(xlDown)

然后，复制表格:

Timetable.Copy

然后在检查器上找到句柄后，通过上面的MSDN链接，在Outlook(Word)中获取目标范围，然后可以使用Word.Range对象的PasteAndFormat方法:

And then following the MSDN link above once you have a handle on the Inspector, get the destination range in Outlook (Word) and you can use the PasteAndFormat method of a Word.Range object:

Dim wdRange as Object 'Word.Range
OutMail.Display

Set wdRange = OutMail.getInspector().WordEditor.Range
wdRange.Text = strBody
wdRange.Expand (1)
wdRange.Characters.Last.PasteAndFormat 16  'wdFormatOriginalFormatting

选项2是我的首选方法.我使用的是没有Outlook的计算机，因此我希望从内存中获取一点信息，目前无法测试，但是如果您有任何问题，请发表评论，我将尝试早上再帮忙.

Option 2 would be my preferred method. I'm on a computer that doesn't have outlook, so I'm winging this a little bit from memory and I can't test right now, but if you have any issues with it just leave a comment and I'll try to help out some more in the morning.

这篇关于将Excel范围粘贴到Outlook中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！