从Excel内容格式化电子邮件正文 [英] Formatting email body from Excel contents

问题描述

我有一个具有给定数据的工作表，

I have a worksheet with given data,

我需要使用Microsoft Outlook在特定日期以必需的格式通过电子邮件发送数据.

I need to email the data using Microsoft Outlook in the required format for a specific date.

说日期是2015年1月5日.

Say the date is 05 Jan 2015.

这是电子邮件的外观，

This is how the email should look,

代码写在Excel 2007工作簿的模块中，

The code is written in the modules of the Excel 2007 workbook,

Public Function FormatEmail(Sourceworksheet As Worksheet, Recipients As Range, CoBDate As Date)

    Dim OutApp As Object
    Dim OutMail As Object
    Dim rows As Range

    On Error GoTo FormatEmail_Error

    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")

    For Each rows In Recipients.Cells.SpecialCells(xlCellTypeConstants)

        If rows.value Like "?*@?*.?*" Then

            Set OutMail = OutApp.CreateItem(0)

            On Error Resume Next
            With OutMail
                .To = rows.value
                .Subject = "Reminder"
                .Body = "Hi All, " & vbNewLine & _
                         vbNewLine
                .display
            End With
            On Error GoTo 0

            Set OutMail = Nothing

        End If

    Next rows

    On Error GoTo 0
    Exit Function

FormatEmail_Error:

    Set OutApp = Nothing
    Application.ScreenUpdating = True
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure FormatEmail of Module modOutlook"

End Function

推荐答案

如果要创建格式正确的Outlook电子邮件，则需要生成具有格式的电子邮件.单纯的基于文本的电子邮件显然是不够的，因此您必须寻找HTML格式的电子邮件.如果是这种情况，您可能打算使用VBA动态创建HTML代码，以模仿Excel的美观外观.

If you want to create nicely formatted Outlook emails then you need to generate emails with formatting. Pure text-based-emails are evidently not sufficient and hence you must be looking for HTML formatted emails. If that's the case you probably aim to dynamically create HTML code with your VBA to mimic the nice visual representation of Excel.

在以下链接下 http://www.quackit.com/html/online-html-editor/，您将找到一个在线HTML编辑器，该编辑器可让您准备格式正确的电子邮件，然后向您显示获取此格式所必需的HTML代码.然后，您只需要在VBA中使用以下电子邮件地址将电子邮件正文设置为此HTML代码即可

Under the following link http://www.quackit.com/html/online-html-editor/ you'll find an online HTML editor which allows you to prepare a nicely formatted email and then shows you the HTML code which is necessary to get this formatting. Afterwards you just need to set in VBA the email body to this HTML code using

.HTMLBody = "your HTML code here"

代替

.Body = "pure text email without formatting"

如果这还不够，并且您要将Excel的一部分复制/粘贴到该电子邮件中，则必须复制Excel的一部分，将它们另存为图片，然后将图片添加到您的电子邮件中(一次再次使用HTML).如果这是您想要的，那么您将在这里找到解决方案: 使用VBA代码如何导出excel工作表作为Excel 2003中的图像?

If that is not sufficient and you want to copy / paste parts of your Excel into that email then you'll have to copy parts of your Excel, save them as a picture, and then add the picture to your email (once again using HTML). If this is what you want then you'll find the solution here: Using VBA Code how to export excel worksheets as image in Excel 2003?

这篇关于从Excel内容格式化电子邮件正文的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！