将Excel中的范围复制并粘贴到Outlook中

huangapple go评论72阅读模式
英文:

Copying and Pasting a Range into Outlook from Excel

问题

我正在尝试完成以下操作:

将Excel中的范围复制并粘贴到Outlook中

我正在尽量避免使用RangeToHtml。它似乎可以复制范围,但无法在Outlook中粘贴。以下是我迄今为止的代码。

Sub Send_Email_Condition_Cell_Value_Change()

    Dim pApp As Object
    Dim pMail As Object
    Dim pBody As String
    Dim rng As Range
    Set rng = Range("B6:C16")
    Set pApp = CreateObject("Outlook.Application")
    Set pMail = pApp.CreateItem(0)
    On Error Resume Next
    With pMail
        .To = "@gmail.com"
        .CC = ""
        .BCC = ""
        .Subject = "BLANK Account Action Price Notification"
        .Body = "Hello, our recommended action price for BLANK has been hit." & vbNewLine & vbNewLine & _
            "Thank you."
        .Display
        Dim wdDoc As Object     ' Word.Document
        Dim wdRange As Object   ' Word.Range
        Set wdDoc = OutMail.GetInspector.WordEditor
        Set wdRange = wdDoc.Range(0, 0)
        wdRange.InsertAfter vbCrLf & vbCrLf
        ' 复制范围到文档
        rng.Copy
        wdRange.Paste
        ' 移除下面的注释将自动发送电子邮件
        ' .Send
    End With
    On Error GoTo 0
    Set pMail = Nothing
    Set pApp = Nothing
End Sub

我尝试过使用RangeToHtml,但对于我的能力来说有点复杂。我找到了这个解决方案,但无法使其正常工作。

英文:

I am trying to accomplish this:

将Excel中的范围复制并粘贴到Outlook中

I am trying to avoid using RangeToHtml if possible. It seems to copy the range but does not paste it in Outlook. Here is what I have thus far.

Sub Send_Email_Condition_Cell_Value_Change()

    Dim pApp As Object
    Dim pMail As Object
    Dim pBody As String
    Dim rng As Range
    Set rng = Range("B6:C16")
    Set pApp = CreateObject("Outlook.Application")
    Set pMail = pApp.CreateItem(0)
    On Error Resume Next
    With pMail
        .To = "@gmail.com"
        .CC = ""
        .BCC = ""
        .Subject = "BLANK Account Action Price Notification"
        .Body = "Hello, our recommended action price for BLANK has been hit." & vbNewLine & vbNewLine & _
            "Thank you."
        .Display
         Dim wdDoc As Object     '## Word.Document
         Dim wdRange As Object   '## Word.Range
        Set wdDoc = OutMail.GetInspector.WordEditor
        Set wdRange = wdDoc.Range(0, 0)
        wdRange.InsertAfter vbCrLf & vbCrLf
        'Copy the range in-place
        rng.Copy
        wdRange.Paste
        'Below will auto send the email when apostrophe is removed
        '.Send
    End With
    On Error GoTo 0
    Set pMail = Nothing
    Set pApp = Nothing
End Sub

I have tried utilizing RangeToHtml, but that is a bit complex for my abilities. I have found this solution however I am unable to make it work.

答案1

得分: 0

更改为:

你正在使用错误的对象名称

将

Set wdDoc = OutMail.GetInspector.WordEditor


改为

Set wdDoc = pMail.GetInspector.WordEditor


<details>
<summary>英文:</summary>

You are using the wrong object name

Change 

Set wdDoc = OutMail.GetInspector.WordEditor


with

Set wdDoc = pMail.GetInspector.WordEditor


</details>



huangapple
  • 本文由 发表于 2023年2月14日 06:27:55
  • 转载请务必保留本文链接:https://go.coder-hub.com/75441788.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定