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

问题描述

我在 Outlook 中保存了一个电子邮件模板.我每天多次发送这封电子邮件.我发送的每封电子邮件都格式为粗体彩色文本、公司徽标以及该收件人的独特信息.

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

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

我知道有一种通过 Microsoft Word 发送邮件的方法，但我不知道如何让它完全自动化.然后 excel 会做所有事情，但它忽略文本正文中的所有格式，我无法让它为文本正文选择多个单元格.

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

Sub SupplierTestingEmail()Dim olApp 作为 Outlook.ApplicationSet olApp = CreateObject("Outlook.Application")Dim olMail 作为 Outlook.MailItem设置 olMail = olApp.CreateItem(olMailItem)olMail.To = Range("B1")olMail.Subject = Range("B2")olMail.Body = Range("A1:C5")olMail.Display结束子

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

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

解决方案

要在复制范围后保留原始格式，请使用

<小时>

展望

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.

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

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.

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.

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) 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.

解决方案

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)

---

Example

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屋！