从Excel发送带有多个单元格文本主体和其他文本格式的自动填充的Outlook电子邮件模板 [英] Sending an auto-filled Outlook email template from Excel with multiple cell text body and additional text formatting

问题描述

我有一个保存在Outlook中的电子邮件模板.我每天都会多次发送此电子邮件.我发送的每封电子邮件均以粗体，彩色文本，公司徽标和该收件人的唯一信息格式设置.

I have an email template saved in Outlook. I send this email out many times a day. Each email I send is formatted with bold, colored text, the company logo, and unique information for that recipient.

唯一信息是在excel中生成的，因此我一直在手动将excel信息复制并粘贴到Outlook模板中.

The unique information is generated in excel and so what I have been manually copying and pasting the excel information into the Outlook template.

我想找到一种方法，使excel为我自动填充模板，包括主要收件人，抄送收件人，主题和附件，以及粘贴到电子邮件正文中的单元格.

I want to figure out a way to get excel to auto-populate the template for me, including the main recipient, the cc recipient, subject, and attachment, and the cells pasted into the body of the email.

我知道在Microsoft Word中可以通过邮件进行通信，但是我不知道如何使它完全自动化.然后excel会做所有事情，但是忽略文本正文中的所有格式，而我无法为文本正文选择多个单元格.

I know there is a way through mailings in Microsoft Word, but I can't figure out how to get it to automate completely. Then excel will do everything, but it ignore all formatting in the text body and I cant get it to select multiple cells for the text body.

我什至不知道从哪里开始.以下是在Outlook中发送电子邮件的基本VBA.但是，当然，电子邮件正文的范围不起作用

I don't even know where to begin with this one. Below is the basic VBA for sending an email on outlook. But of course, the range for the email body doesnt work

Sub SupplierTestingEmail()

   Dim olApp As Outlook.Application
      Set olApp = CreateObject("Outlook.Application")

        Dim olMail As Outlook.MailItem
        Set olMail = olApp.CreateItem(olMailItem)

        olMail.To = Range("B1")
        olMail.Subject = Range("B2")
        olMail.Body = Range("A1:C5")
        olMail.Display

End Sub

我希望电子邮件中包含以下内容

I want the email to include the following things

1)身体的一系列细胞
2)公司标志
3)彩色文字
4)引用的单元格
5)附件
6)抄本，我需要抄送某人.

1) a range of cells for the body
2) the company logo
3) Colored text
4) referenced cells
5) an attachment
6) a carbon copy, I need to CC someone.

推荐答案

要在复制范围后保留原始格式，请使用 WdRecoveryType枚举(Word)

To Preserves original formatting once you copy the range, work with Word object model with MailItem.GetInspector Property (Outlook) and then paste it as wdFormatOriginalFormatting WdRecoveryType Enumeration (Word)

示例

Option Explicit
Public Sub Example()
' add ref - tool -> references - > Microsoft Outlook XX.X Object Library
    Dim olApp As Outlook.Application
    Set olApp = New Outlook.Application

    Dim Email As Outlook.MailItem
    Set Email = olApp.CreateItem(0)

' add ref - tool -> references - > Microsoft Word XX.X Object Library
    Dim wdDoc As Word.Document '<=========
    Set wdDoc = Email.GetInspector.WordEditor

    Dim Sht As Excel.Worksheet
    Set Sht = ThisWorkbook.Worksheets("Sheet1")

    Dim rng As Range
    Set rng = Sht.Range("A4:H16").SpecialCells(xlCellTypeVisible)
        rng.Copy

    With Email
        .To = Sht.Range("C1")
        .Subject = Sht.Range("B1")
        .Display

         wdDoc.Range.PasteAndFormat Type:=wdFormatOriginalFormatting
    End With

End Sub

---

Excel

Outlook

这篇关于从Excel发送带有多个单元格文本主体和其他文本格式的自动填充的Outlook电子邮件模板的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！