如何通过VBA将Excel数据表粘贴到Outlook中 [英] How to paste excel data table into outlook through vba

问题描述

  Private Sub CommandButton23_Click()昏暗的objOutlook作为对象昏暗的objMail作为对象调到范围昏暗主题作为范围昏暗的身体作为范围昏暗附加范围设置objOutlook = CreateObject("Outlook.Application")设置objMail = objOutlook.CreateItem(0)使用ActiveSheet设置rngTo = Sheets(帮助台数据").Range("D4")设置rngSubject = Sheets(帮助台数据").Range("I5")'设置rngBody = Sheets(帮助台数据").Range("D4")'设置rngAttach = .Range("B4")结束于表格(帮助台数据").选择Sheets(帮助中心数据").Range("B12:Z12").选择Sheets(帮助中心数据").Range(Selection，Selection.End(xlDown)).SelectSelection.SpecialCells(xlCellTypeVisible).选择选择复制 

现在，我想将上面复制的数据从帮助台数据"粘贴到Outlook Body中，但不知道如何操作..我尝试对Outlook对象使用Specialpaste，但它也消除了错误..

 使用objMail'.To = rngTo.Value.Subject =站点上的所有者问题"&rngSubject.Value&-("& rngTo.Value&圈)".Body =先生，"&_请在下面找到今天报道的站点问题."'.Attachments.Add rngAttach.Value.展示结束于设置objOutlook = Nothing设置objMail = Nothing设置rngTo = Nothing设置rngSubject = Nothing设置rngBody = Nothing设置rngAttach = Nothing结束子 

因此任何人都可以告诉我如何将B12到Z12的数据从帮助台数据"表粘贴到Outlook正文.

解决方案

一种方法是使用 .HTMLBody 属性并将所需范围转换为HTML格式.

在电子邮件子目录中，使用 .objMail ，包含 .HTMLBody 属性，并将范围传递到 rngHTML 函数中./p>

.HTMLBody =下表."&vbNewLine&rngHTML(Range("A1:B10"))

包括将在您的代码中生成HTML范围的函数.

 函数rngHTML(Rng作为范围)Dim fso作为对象，ts作为对象，TempWB作为工作簿昏暗的TempFile作为字符串TempFile = Environ $("temp")&"\"&格式(现在为"dd-mm-yy h-mm-ss")&".htm"''复制范围并创建一个新的工作簿以将数据粘贴到副本设置TempWB = Workbooks.Add(1)带TempWB.Sheets(1).Cells(1).粘贴特殊粘贴:= 8.Cells(1).PasteSpecial xlPasteValues，，False，False.Cells(1).PasteSpecial xlPasteFormats 、、 False，False.Cells(1).选择Application.CutCopyMode =假关于错误继续.DrawingObjects.Visible =真.DrawingObjects.Delete出错时转到0结束于''将工作表发布到htm文件使用TempWB.PublishObjects.Add(_SourceType:= xlSourceRange，_档名:= TempFile，_工作表:= TempWB.Sheets(1).name，_来源:= TempWB.Sheets(1).UsedRange.Address，_HtmlType:= xlHtmlStatic).发布(正确)结束于''将htm文件中的所有数据读入rngHTML设置fso = CreateObject("Scripting.FileSystemObject")设置ts = fso.GetFile(TempFile).OpenAsTextStream(1，-2)rngHTML = ts.readallts.CloserngHTML = Replace(rngHTML，"align = center x:publishsource ="，_"align = left x:publishsource =")TempWB.Close savechanges:= False''删除我们在此功能中使用的htm文件杀死TempFile设置ts =否设置fso = Nothing设置TempWB =无结束功能 

请访问罗恩·布鲁因(Ron de Bruin)的网站，碰到了这个功能；他还解释了将范围纳入电子邮件正文的另一种方法.

希望这会有所帮助.

Private Sub CommandButton23_Click()

Dim objOutlook As Object
Dim objMail As Object
Dim rngTo As Range
Dim rngSubject As Range
Dim rngBody As Range
Dim rngAttach As Range

Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)

With ActiveSheet
    Set rngTo = Sheets("Helpdesk Data").Range("D4")
    Set rngSubject = Sheets("Helpdesk Data").Range("I5")
    'Set rngBody = Sheets("Helpdesk Data").Range("D4")
    'Set rngAttach = .Range("B4")

     End With

Sheets("Helpdesk Data").Select
Sheets("Helpdesk Data").Range("B12:Z12").Select
Sheets("Helpdesk Data").Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy

Now I want to paste above copied data from "Helpdesk data" into Outlook Body, but don't know how to do it.. I tried Specialpaste with Outlook object but it also dispays errors..

With objMail
    '.To = rngTo.Value
    .Subject = "Owner Issue at Site " & rngSubject.Value & " - (" & rngTo.Value & " Circle)"
    .Body = "Sir, " & _
    "Please find below site issue reported Today."

    '.Attachments.Add rngAttach.Value
    .Display

End With

Set objOutlook = Nothing
Set objMail = Nothing
Set rngTo = Nothing
Set rngSubject = Nothing
Set rngBody = Nothing
Set rngAttach = Nothing

End Sub

So anyone could tell me just how could i paste my B12 to Z12 data from "Helpdesk data" sheet to outlook body..

解决方案

One method is to use the .HTMLBody property and to turn the required range into HTML formatting.

In your e-mail sub, with your objMail, include the .HTMLBody property and pass a range into the rngHTML function.

.HTMLBody = "Table below." & vbNewLine & rngHTML(Range("A1:B10"))

Include the function which will generate the HTML range in your code.

Function rngHTML(Rng As Range)
    Dim fso As Object, ts As Object, TempWB As Workbook
    Dim TempFile As String

    TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
    '' copy the range and create a new workbook to paste the data into
    Rng.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial xlPasteValues, , False, False
        .Cells(1).PasteSpecial xlPasteFormats, , False, False
        .Cells(1).Select
        Application.CutCopyMode = False
        On Error Resume Next
        .DrawingObjects.Visible = True
        .DrawingObjects.Delete
        On Error GoTo 0
    End With

    '' publish the sheet to a htm file
    With TempWB.PublishObjects.Add( _
         SourceType:=xlSourceRange, _
         Filename:=TempFile, _
         Sheet:=TempWB.Sheets(1).name, _
         Source:=TempWB.Sheets(1).UsedRange.Address, _
         HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With

    '' read all data from the htm file into rngHTML
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    rngHTML = ts.readall
    ts.Close
    rngHTML = Replace(rngHTML, "align=center x:publishsource=", _
                          "align=left x:publishsource=")

    TempWB.Close savechanges:=False
    '' delete the htm file we used in this function
    Kill TempFile

    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
End Function

Please see Ron de Bruin's website, this is where I originally came across this function; he also explains another method of getting a range into the body of an e-mail.

Hope this helps.

这篇关于如何通过VBA将Excel数据表粘贴到Outlook中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！