2023年3月20日 23:22:52go评论56阅读模式

英文:

Why does this VBA Macro that copies and paste range into email in the wrong order?

问题

我正在尝试编写一个宏，它会将一个区域复制并粘贴到Outlook邮件中。我希望在粘贴的区域下面包含文本和格式。

当宏编译后，显示的电子邮件如下所示：

然而，我期望的结果是这样的：

有人能帮助我找出我做错了什么吗？

英文:

I'm trying to write a macro that will copy and paste a range into an email in outlook. I have text and formatting that I would like to include below the range that is pasted in the email.

Sub sendEmailwithPic()

Dim OutApp As Object
Dim OutMail As Object
Dim table As Range
Dim pic As Picture
Dim ws As Worksheet
Dim wordDoc


Set OutApp = CreateObject(&quot;Outlook.Application&quot;)
Set OutMail = OutApp.CreateItem(0)

&#39;grab table, convert to image, and cut
Set ws = ThisWorkbook.Sheets(&quot;Sheet1&quot;)
Set table = ws.Range(&quot;A1:E11&quot;)
ws.Activate
table.Copy
Set pic = ws.Pictures.Paste
pic.Cut

&#39;create email message
On Error Resume Next
    With OutMail
        .to = &quot;someone@gmail.com&quot;
        .Subject = &quot;Country Population Data &quot; &amp; Format(Date, &quot;mm-dd-yy&quot;)
        .Display
        
        Set wordDoc = OutMail.GetInspector.WordEditor
        wordDoc.Range.PasteandFormat wdChartPicture
        .HTMLBody = &quot;&lt;body style=font-size:11pt;font-family:Calibri&gt;&quot; &amp; _
                &quot;&lt;p&gt;&lt;font style=font-size:11pt;font-family:Calibri&gt;&lt;b&gt;Lorem ipsum dolor sit amet, consectetur adipiscing elit,&quot; &amp; _
                &quot;sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Vitae ultricies leo integer malesuada nunc.&lt;/b&gt;&lt;/p&gt;&quot; &amp; _
                &quot;&lt;p&gt;&lt;font style=font-size:9pt;font-family:Calibri&gt;Lorem ipsum dolor sit amet, consectetur adipiscing elit,&quot; &amp; _
                &quot;sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.&lt;/p&gt;&quot; &amp; _
                &quot;&lt;p&gt;&lt;font style=font-size:9pt;font-family:Arial;color:#595959&gt;&lt;i&gt;Lorem ipsum dolor sit amet, consectetur adipiscing elit,&quot; &amp; _
                &quot;sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Amet consectetur adipiscing elit ut. Mattis aliquam faucibus purus in massa tempor.&quot; &amp; _
                &quot;Hendrerit dolor magna eget est.&lt;/i&gt;&lt;/font&gt;&lt;/p&gt;&quot; &amp; .HTMLBody
    End With
    On Error GoTo 0
    
Set OutApp = Nothing
Set OutMail = Nothing

End Sub

When the macro compiles the email that is displayed looks like this:

However, my desired outcome is this:

Can someone help me figure out what I'm doing wrong??

答案1

得分: 1

我已翻译以下代码部分：

With OutMail
        .To = "someone@gmail.com"
        .Subject = "Country Population Data " & Format(Date, "mm-dd-yy")
        .Display
        
        lastBodyStr = .HTMLBody
        
        Set wordDoc = OutMail.GetInspector.WordEditor
        wordDoc.Range.PasteandFormat wdChartPicture
        textStr = "<body style=font-size:11pt;font-family:Calibri>" & _
                "<p><font style=font-size:11pt;font-family:Calibri><b>Lorem ipsum dolor sit amet, consectetur adipiscing elit," & _
                "sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Vitae ultricies leo integer malesuada nunc.</b></p>" & _
                "<p><font style=font-size:9pt;font-family:Calibri>Lorem ipsum dolor sit amet, consectetur adipiscing elit," & _
                "sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.</p>" & _
                "<p><font style=font-size:9pt;font-family:Arial;color:#595959><i>Lorem ipsum dolor sit amet, consectetur adipiscing elit," & _
                "sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Amet consectetur adipiscing elit ut. Mattis aliquam faucibus purus in massa tempor." & _
                "Hendrerit dolor magna eget est.</i></font></p>" 
        
        .HTMLBody = .HTMLBody & textStr & lastBodyStr
                
    End With

英文:

Try this:

With OutMail
        .To = &quot;someone@gmail.com&quot;
        .Subject = &quot;Country Population Data &quot; &amp; Format(Date, &quot;mm-dd-yy&quot;)
        .Display
        
        lastBodyStr = .HTMLBody
        
        Set wordDoc = OutMail.GetInspector.WordEditor
        wordDoc.Range.PasteandFormat wdChartPicture
        textStr = &quot;&lt;body style=font-size:11pt;font-family:Calibri&gt;&quot; &amp; _
                &quot;&lt;p&gt;&lt;font style=font-size:11pt;font-family:Calibri&gt;&lt;b&gt;Lorem ipsum dolor sit amet, consectetur adipiscing elit,&quot; &amp; _
                &quot;sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Vitae ultricies leo integer malesuada nunc.&lt;/b&gt;&lt;/p&gt;&quot; &amp; _
                &quot;&lt;p&gt;&lt;font style=font-size:9pt;font-family:Calibri&gt;Lorem ipsum dolor sit amet, consectetur adipiscing elit,&quot; &amp; _
                &quot;sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.&lt;/p&gt;&quot; &amp; _
                &quot;&lt;p&gt;&lt;font style=font-size:9pt;font-family:Arial;color:#595959&gt;&lt;i&gt;Lorem ipsum dolor sit amet, consectetur adipiscing elit,&quot; &amp; _
                &quot;sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Amet consectetur adipiscing elit ut. Mattis aliquam faucibus purus in massa tempor.&quot; &amp; _
                &quot;Hendrerit dolor magna eget est.&lt;/i&gt;&lt;/font&gt;&lt;/p&gt;&quot; 
        
        .HTMLBody = .HTMLBody &amp; textStr &amp; lastBodyStr
                
                
    End With

I added a lastBodyStr which saves your signature if you have one, changed up your original .HTMLBody to be saved as textStr and also took out the & .HTMLBody and then set the .HTMLBody to be in the order it needs to be

答案2

得分: -1

设置消息正文时，使用Word对象模型和HTMLBody属性的方法如下：

设置wordDoc = OutMail.GetInspector.WordEditor
        wordDoc.Range.PasteandFormat wdChartPicture
        .HTMLBody = &quot;&lt;body style=font-size:11pt;font-family:Calibri&gt;&quot; &amp; _
                &quot;&lt;p&gt;&lt;font style=font-size:11pt;font-family:Calibri&gt;&lt;b&gt;Lorem ipsum dolor sit amet, consectetur adipiscing elit,&quot; &amp; _
                &quot;sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Vitae ultricies leo integer malesuada nunc.&lt;/b&gt;&lt;/p&gt;&quot; &amp; _
                &quot;&lt;p&gt;&lt;font style=font-size:9pt;font-family:Calibri&gt;Lorem ipsum dolor sit amet, consectetur adipiscing elit,&quot; &amp; _
                &quot;sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.&lt;/p&gt;&quot; &amp; _
                &quot;&lt;p&gt;&lt;font style=font-size:9pt;font-family:Arial;color:#595959&gt;&lt;i&gt;Lorem ipsum dolor sit amet, consectetur adipiscing elit,&quot; &amp; _
                &quot;sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Amet consectetur adipiscing elit ut. Mattis aliquam faucibus purus in massa tempor.&quot; &amp; _
                &quot;Hendrerit dolor magna eget est.&lt;/i&gt;&lt;/font&gt;&lt;/p&gt;&quot; &amp; .HTMLBody

建议确保将内容粘贴到HTML文档中，不依赖Outlook的内置功能。

这意味着HTMLBody属性返回格式良好的HTML文档。如果您需要在其中添加内容，您需要首先找到合适的位置。例如，您可能需要找到开头的<body>标签，如果您需要将内容粘贴到消息正文的开头，或者找到结束的</body>标签，如果您需要在末尾粘贴内容。

英文:

When you set up a message body using the Word object model and the HTMLBody property in the following way:

Set wordDoc = OutMail.GetInspector.WordEditor
        wordDoc.Range.PasteandFormat wdChartPicture
        .HTMLBody = &quot;&lt;body style=font-size:11pt;font-family:Calibri&gt;&quot; &amp; _
                &quot;&lt;p&gt;&lt;font style=font-size:11pt;font-family:Calibri&gt;&lt;b&gt;Lorem ipsum dolor sit amet, consectetur adipiscing elit,&quot; &amp; _
                &quot;sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Vitae ultricies leo integer malesuada nunc.&lt;/b&gt;&lt;/p&gt;&quot; &amp; _
                &quot;&lt;p&gt;&lt;font style=font-size:9pt;font-family:Calibri&gt;Lorem ipsum dolor sit amet, consectetur adipiscing elit,&quot; &amp; _
                &quot;sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.&lt;/p&gt;&quot; &amp; _
                &quot;&lt;p&gt;&lt;font style=font-size:9pt;font-family:Arial;color:#595959&gt;&lt;i&gt;Lorem ipsum dolor sit amet, consectetur adipiscing elit,&quot; &amp; _
                &quot;sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Amet consectetur adipiscing elit ut. Mattis aliquam faucibus purus in massa tempor.&quot; &amp; _
                &quot;Hendrerit dolor magna eget est.&lt;/i&gt;&lt;/font&gt;&lt;/p&gt;&quot; &amp; .HTMLBody

I'd suggest making sure that you paste the content inside the HTML document not relying on built-in capabilities of Outlook.

That means the HTMLBody property returns a well-formed HTML document. And if you need to add something there you need to find the right place for that first. For example, you may need to find the opening <body> tag if you need to paste the content in the beginning of the message body or the closing </body> tag if you need to paste the content after (in the end).

通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。

为什么这个复制并粘贴范围到电子邮件的VBA宏顺序错误？

问题

答案1

答案2

Opening another excel file and copy the “specific” filtered columns to the current active workbook.

从另一个Excel或Access数据库查询

在下一个空单元格粘贴范围会导致运行时错误 424：’需要对象’

在Excel中将对角单元格归零而不改变矩阵。

What's the correct way to type hint an empty list as a literal in python?

如何在Highcharts Gantt中更改本地化的星期名称

如何在同一个流中使用多个过滤器和映射函数？

如何使用Map/Set来将代码优化到O(n)？

.NET MAUI Android在GitHub Actions上构建失败，错误代码为1。

如何在Playwright视觉比较中屏蔽多个定位器？

在C++中，可以使用可变模板参数来检索类型的内部类型。

selenium.common.exceptions.StaleElementReferenceException: Message: stale element reference: stale element not found

Creating and opening a URL to log in to Website via Basic Auth with Robot Framework/Selenium (Python)

AG Grid 在上下文菜单中以大文本形式打开

发表评论