Note: The other languages of the website are Google-translated. Back to English
登陆  \/ 
x
or
x
点此注册  \/ 
x

or

如何在复制和粘贴指定范围到Excel中的电子邮件正文中发送电子邮件?

在许多情况下,Excel工作表中指定范围的内容可能对您的电子邮件通信很有用。 在本文中,我们将介绍一种直接在Excel中发送将具有指定范围的电子邮件粘贴到电子邮件正文中的方法。

发送具有指定范围的电子邮件,粘贴到Excel中的电子邮件正文中
使用出色的工具发送指定范围的电子邮件,并将其粘贴到电子邮件正文中

有关在Excel中发送邮件的更多教程...


发送具有指定范围的电子邮件,粘贴到Excel中的电子邮件正文中

以下VBA代码可以帮助您直接在Excel中复制范围并将其粘贴到Outlook电子邮件正文中。 请执行以下操作。

1.在工作表中包含您需要复制的范围,按 其他 + F11 同时打开 Microsoft Visual Basic应用程序 窗口。

2.在开幕 Microsoft Visual Basic应用程序 窗口,请点击 工具 > 參考資料 如下图所示。

3。 在里面 参考– VBAProject 对话框,请查找并检查 Microsoft Outlook对象库 选项,然后单击 OK 按钮。

4。 点击 插页 > 模块,然后将下面的VBA代码复制并粘贴到“模块”窗口中。

VBA代码:发送具有指定范围的电子邮件,粘贴到Excel中的电子邮件正文中

Sub Send_Email()
'Updated by Extendoffice 20200119
    Dim xRg As Range
    Dim I, J As Long
    Dim xAddress As String
    Dim xEmailBody As String
    Dim xMailOut As Outlook.MailItem
    Dim xOutApp As Outlook.Application    
    On Error Resume Next
    xAddress = ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Please select range you need to paste into email body", "KuTools For Excel", xAddress, , , , , 8)
If xRg Is Nothing Then Exit Sub
Application.ScreenUpdating = False
    Set xOutApp = CreateObject("Outlook.Application")
    Set xMailOut = xOutApp.CreateItem(olMailItem)
    For I = 1 To xRg.Rows.Count
        For J = 1 To xRg.Columns.Count
            xEmailBody = xEmailBody & "  " & xRg.Cells(I, J).value
        Next
        xEmailBody = xEmailBody & vbNewLine
    Next
    xEmailBody = "Hi" & vbLf & vbLf & " body of message you want to add" & vbLf & vbLf & xEmailBody & vbNewLine
    With xMailOut
        .Subject = "Test"
        .To = "happy.xuebi@163.com"
        .Body = xEmailBody
        .Display
        '.Send
    End With
    Set xMailOut = Nothing
    Set xOutApp = Nothing
    Application.ScreenUpdating = True
End Sub

说明:

  • 1)。 请更改电子邮件正文 xEmailBody =“嗨”&vbLf&vbLf&“要添加的邮件正文”&vbLf&vbLf&xEmailBody&vbNewLine 如你所需。
  • 2)。 请指定您的电子邮件收件人和主题(至= happy.xuebi@163.com.Subject =“测试”) 代码中的行。

5。 按 F5 键来运行代码。 在弹出 Kutools for Excel 对话框,请选择您需要粘贴到电子邮件正文中的范围,然后单击 OK 按钮。 看截图:

6.现在,将创建一封包含指定收件人,主题,正文和所选Excel范围的电子邮件,请单击 发送 按钮发送此电子邮件。 请参阅显示的屏幕截图。

备注:仅当您将Outlook用作电子邮件程序时,VBA代码才起作用。


使用出色的工具发送指定范围的电子邮件,并将其粘贴到电子邮件正文中

如果您不使用Outlook,但仍想直接在Excel中直接发送电子邮件,并在其中粘贴指定的范围数据,则强烈建议您使用 发电子邮件 实用程序 Kutools for Excel 为了你。 使用此功能,您只需要配置电子邮件地址的传出服务器,然后以后再通过此电子邮件地址直接在Excel中发送电子邮件。

申请前 Kutools for Excel首先下载并安装.

1.首先,您需要准备一个带有必填字段的邮件列表。

  • 窍门:邮件列表必须至少包含2行,并且第一行必须为标头(假设您要将电子邮件发送到Excel中的两个电子邮件地址,请输入这两个电子邮件地址,标头为“ Email”,如下所示) )。
  • 或者,您可以使用以下命令轻松创建邮件列表 创建邮件列表 功能。

2.选择范围,将数据添加到电子邮件正文,然后按 按Ctrl + C 键复制它。

3.选择整个邮件列表(包括标题),单击 Kutools Plus > 发电子邮件。 看截图:

4.然后 发电子邮件 弹出对话框。

  • 4.1)所选邮件列表的项目填充在相应的字段中(您可以根据需要在邮件列表中添加更多字段);
  • 4.2)点击电子邮件正文框,然后按 按Ctrl + V 键将选定的范围数据粘贴到其中。 之后,根据需要添加其他内容;
  • 4.3取消选中 通过Outlook发送电子邮件 框;
  • 2.4)点击 发送服务器设置 按钮。 看截图:

5.然后 传出服务器(SMTP)设置–新方案 对话框弹出。 请在电子邮件地址中填入其服务器设置,并在选中以下内容后指定一个文件夹来保存所有已发送的邮件 将发送的电子邮件保存到 框,然后单击 OK 按钮保存设置。

6.返回到 发电子邮件 对话框中,单击 发送 按钮发送电子邮件。

从现在开始,您可以直接在Excel中发送具有此功能的电子邮件。

  如果您想免费试用该工具(30天), 请点击下载,然后按照上述步骤进行操作。


相关文章:

将电子邮件发送到Excel单元格中指定的电子邮件地址
假设您有一个电子邮件地址列表,并且您想直接在Excel中批量向这些电子邮件地址发送电子邮件。 如何实现呢? 本文将向您展示将电子邮件发送到Excel单元格中指定的多个电子邮件地址的方法。

在Excel中发送电子邮件时插入Outlook签名
假设您想直接在Excel中发送电子邮件,如何在电子邮件中添加默认的Outlook签名? 本文提供了两种方法来帮助您在Excel中发送电子邮件时添加Outlook签名。

发送带有Excel中附加的多个附件的电子邮件
本文讨论的是通过带有Excel中附加的多个附件的Outlook发送电子邮件。

如果在Excel中已达到到期日期,则发送电子邮件
例如,如果C列中的截止日期小于或等于7天(当前日期为2017/9/13),则向A列中的指定收件人发送电子邮件提醒,并在B列中指定内容。实现这一目标? 本文将提供一种VBA方法来详细处理它。

根据Excel中的单元格值自动发送电子邮件
假设您要基于Excel中指定的单元格值通过Outlook向特定收件人发送电子邮件。 例如,当工作表中单元格D7的值大于200时,将自动创建一封电子邮件。 本文介绍了一种VBA方法,可帮助您快速解决此问题。

有关在Excel中发送邮件的更多教程...


最佳办公效率工具

Kutools for Excel解决了您的大多数问题,并使您的生产率提高了80%

  • 重用: 快速插入 复杂的公式,图表 以及您以前使用过的任何东西; 加密单元 带密码 创建邮件列表 并发送电子邮件...
  • 超级公式栏 (轻松编辑多行文本和公式); 阅读版式 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不会丢失数据; 拆分单元格内容; 合并重复的行/列...防止细胞重复; 比较范围...
  • 选择重复或唯一 行; 选择空白行 (所有单元格都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择...
  • 确切的副本 多个单元格,无需更改公式参考; 自动创建参考 到多张纸; 插入项目符号,复选框等...
  • 提取文字,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级滤镜 (将过滤方案保存并应用于其他工作表); 高级排序 按月/周/日,频率及更多; 特殊过滤器 用粗体,斜体...
  • 结合工作簿和工作表; 根据关键列合并表; 将数据分割成多个工作表; 批量转换xls,xlsx和PDF...
  • 超过300种强大功能。 支持Office / Excel 2007-2019和365。支持所有语言。 在您的企业或组织中轻松部署。 完整功能30天免费试用。 60天退款保证。
kte选项卡201905

Office选项卡为Office带来了选项卡式界面,使您的工作更加轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!
officetab底部
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Yogesh Girase · 6 months ago
    hello,

    can you help me on below

    I have create excel sheet & updated 10 supplier mail detail
    I have send mail through excel to all 10 supplier with individual sheet attachment with individual mail.

    I want to paste excel data in outlook body instead of attachment in mail

    can any one help me

  • To post as a guest, your comment is unpublished.
    Arul · 1 years ago
    Hi,
    Instead of selecting the range, I want to select multiple pivots in the excel.
    can you please help me.
  • To post as a guest, your comment is unpublished.
    Raman · 1 years ago
    Hi,
    Instead of selecting the range manually, I want to select the range automatically.
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Raman,
      In the below code, please replace the range "A1:C5" in line Set xRg = Range("A1:C5") with your own range.

      Sub Send_Email()
      Dim xRg As Range
      Dim I, J As Long
      Dim xAddress As String
      Dim xEmailBody As String
      Dim xMailOut As Outlook.MailItem
      Dim xOutApp As Outlook.Application
      On Error Resume Next
      xAddress = ActiveWindow.RangeSelection.Address
      Set xRg = Range("A1:C5")
      If xRg Is Nothing Then Exit Sub
      Application.ScreenUpdating = False
      Set xOutApp = CreateObject("Outlook.Application")
      Set xMailOut = xOutApp.CreateItem(olMailItem)
      For I = 1 To xRg.Rows.Count
      For J = 1 To xRg.Columns.Count
      xEmailBody = xEmailBody & " " & xRg.Cells(I, J).Value
      Next
      xEmailBody = xEmailBody & vbNewLine
      Next
      xEmailBody = "Hi" & vbLf & vbLf & " body of message you want to add" & vbLf & vbLf & xEmailBody & vbNewLine
      With xMailOut
      .Subject = "Test"
      .To = "happy.xuebi@163.com"
      .Body = xEmailBody
      .Display
      '.Send
      End With
      Set xMailOut = Nothing
      Set xOutApp = Nothing
      Application.ScreenUpdating = True
      End Sub
  • To post as a guest, your comment is unpublished.
    Ther · 1 years ago
    Hi experts, Do we have updates on how the format maintained?
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Ther,
      Can't figure it out. Sorry for that.
  • To post as a guest, your comment is unpublished.
    Gowtham · 1 years ago
    i am seeing a compile error (User-defined type not defined". Please help me out to overcome this.
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi,
      Please get into the Reference window by clicking Tools > references. Scroll down to find and check the Microsoft Outlook Object Library box and click the OK button to finish the setting.
  • To post as a guest, your comment is unpublished.
    sachin kumr · 2 years ago
    this is pasting as a text. Kindly suggest how to send the table or the same format which is copied from the excel.
  • To post as a guest, your comment is unpublished.
    miguel · 2 years ago
    Code to send automatically after selecting after ok
  • To post as a guest, your comment is unpublished.
    Dhiraj Mahajan · 2 years ago
    Hi
    This code is vary excellent, by using the code i have completed my 90% of my project.
    I have same issue as mentioned by Anirudh that is table formatting. How can i format the table in email.
    Please help me......
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Good Day,
      The problem can't be solved yet. Sorry for the inconvenience and thank you for your comment.
      • To post as a guest, your comment is unpublished.
        mahajand09@gmail.com · 2 years ago
        Hi, Is there any update on below.......
        • To post as a guest, your comment is unpublished.
          mahajand09@gmail.com · 2 years ago
          Hi, Also I Wanted code for "Filter by Date".
          I am working on project, on that project I wanted to filter the data by the date, actually we have filter/hide the and last 05 days to current date data and we have highlight all other data.
          Please help me to complete this project.
          Your help is very great-full for me.
  • To post as a guest, your comment is unpublished.
    ghosh · 2 years ago
    This is great. It is working as expected. The only issue is that the format of the table is not maintained in the mail. Can you let me know how do we preserve the format of the table in the mail
  • To post as a guest, your comment is unpublished.
    mfergus · 2 years ago
    This code is great, but I need to execute with a command button rather than pressing F5 in code view. I'd like my employees to be able to fill out some info, then hit the button and have it copy the range of cells they completed and paste into email. I can get code working for the email button and separately for the copy/paste of the range of cells, but can't seem to combine both functions. Please help!!! Thanks!
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Hi Michael,
      You just need to create a button (such as a Button (Form Control)) in your worksheet, then assign the macro to the button.
      • To post as a guest, your comment is unpublished.
        prasana05@gmail.com · 1 years ago
        Hi Crystal, Thank you!
        I had a code created and was wondering how to assign the Command button to the code. Your post helped! and my report works like a charm.

        Thanks again.