提示:其他語言是Google翻譯的。 你可以訪問 English 版本。
登入
x
or
x
x
註冊
x

or

如何發送電子郵件複製和粘貼指定的範圍到電子郵件正文在Excel中?

在許多情況下,Excel工作表中指定的內容範圍可能對您的電子郵件通信非常有用。 在這篇文章中,我們將介紹一種直接在Excel中發送具有指定區域粘貼到電子郵件正文中的電子郵件的方法。

在Excel中發送粘貼到電子郵件正文中的指定範圍的電子郵件


在Excel中發送粘貼到電子郵件正文中的指定範圍的電子郵件


以下VBA代碼可以幫助您複製範圍並將其直接粘貼到Excel中的電子郵件正文中。 請做如下。

1。 在工作表中包含您需要復制的範圍,請按 其他 + F11 鍵同時打開 Microsoft Visual Basic for Applications 窗口。

2。 在開幕式上 Microsoft Visual Basic for Applications 窗口,請點擊 工具 > 參考 如下圖所示。

3。 在裡面 參考 - VBAProject 對話框,請查找並檢查 Microsoft Outlook對像庫 選項,然後單擊 OK 按鈕。

4。 點擊 插入 > 模塊,然後將下面的VBA代碼複製並粘貼到模塊窗口中。

VBA代碼:在Excel中發送粘貼到電子郵件正文中的指定範圍的電子郵件

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 = 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 =“Hi”&vbLf&vbLf&“您要添加的郵件正文”&vbLf&vbLf&xEmailBody&vbNewLine 如你所需。

2)。 請指定您的電子郵件收件人和主題 .To = happy.xuebi@163.com - .Subject =“測試” 代碼中的行。

5。 按 F5 鍵來運行代碼。 在彈出 Kutools for Excel 對話框中,請選擇您需要粘貼到電子郵件正文中的範圍,然後單擊 OK 按鈕。 看截圖:

6。 現在用指定的收件人,主題,正文和選定的Excel範圍創建電子郵件,請點擊 發送 按鈕發送此電子郵件。 見所示的截圖。

注意:當您使用Outlook作為您的電子郵件程序時,VBA代碼才起作用。


相關文章:



推薦的Excel生產力工具

Kutools for Excel幫助您提前完成工作,並從人群中脫穎而出

  • 超過300強大的高級功能,專為1500工作場景設計,通過70%提高生產力,讓您有更多時間照顧家庭和享受生活。
  • 不再需要記憶公式和VBA代碼,從現在起讓你的大腦休息一下。
  • 成為3分鐘的Excel專家,複雜和重複的操作可以在幾秒鐘內完成,
  • 每天減少成千上萬的鍵盤和鼠標操作,現在告別職業病。
  • 110,000高效人才和300 +世界知名公司的選擇。
  • 60-day full功能免費試用。 60天退款保證。 2多年的免費升級和支持。

將選項卡式瀏覽和編輯帶到Microsoft Office,遠比瀏覽器的選項卡強大

  • Office選項卡專為Word,Excel,PowerPoint和其他Office應用程序設計:Publisher,Access,Visio和Project。
  • 在同一窗口的新選項卡中打開並創建多個文檔,而不是在新窗口中。
  • 通過50%提高您的工作效率,每天為您減少數百次鼠標點擊!
Say something here...
symbols left.
You are guest ( Sign Up? )
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.
    sachin kumr · 2 months 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 · 3 months ago
    Code to send automatically after selecting after ok
  • To post as a guest, your comment is unpublished.
    Dhiraj Mahajan · 5 months 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 · 5 months 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.
        Dhiraj Mahajan · 2 months ago
        Hi, Is there any update on below.......
        • To post as a guest, your comment is unpublished.
          Dhiraj Mahajan · 2 months 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.
    Anirudh · 9 months 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.
    Michael Ferguson · 10 months 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 · 10 months 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.