Send a Word Doc as E-Mail Body

[goshute]

I am trying to email a word doucument from Excel. I have some data in
Excel that I am inserting into a new Word document that I create from a
template. Then I want to send that Word document as the BODY of an
email which I can do manually by selecting File > Send To > Mail
Recipient. In VBA, I can open the "EnvelopeWindow" but can not enter
any info into the TO or Subject lines. I receive the following error.

Run-time error '-2147417851 (80010105) Automation error The server
threw an exception


I also get this error when I try to save the Word document with SaveAs.
I am not stuck on any one process but I would like to be able to send
these messages automatically.

As there are over 300 of these messages that need to be sent each
month, I would like to eliminate this manual step.

My code snippet:

WDApp.ActiveWindow.EnvelopeVisible = True
With WDApp.ActiveDocument.MailEnvelope
.Item.To = "(e-mail address removed)"
.Item.Subject = "SOX Review"
End With

 

[Sue Mosher [MVP-Outlook]]

You didn't say what version of Office you're using. I've used the "Office envelope" feature a lot and have never run into that problem.

A potentially more difficult issue is that you will not be able to send those messages automatically without raising security prompts. Therefore, instead of using the "envelope" feature, I'd recommend that you use Word's mail merge feature. Performing a merge with the outgoing messages in HTML format (same as the "envelope" feature produces) will not result in security prompts.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

 

[goshute]

I do not know how to do the Word Mail Merge but see there is a section
in your "Outlook Programming" book on page 485.

Every message will contain:
Boilerplate text from the Word document
Appropriate cells from an Excel Worksheet
Recipient from Excel worksheet

The process is that all of the data is contained in an Excel
spreadsheet, with the recipient as well. Each message will have the
recipient and different cells from the Excel worksheet, a seperate VBA
Sub will get that data. The Word template is used only for message
boilerplate and has a bookmark were the appropriate cells are inserted
from the spreadsheet.

We did have a custom message but abondoned that because we could not
find a way to insert the data from Excel in the proper place. With
this current process, we can not address the message or save the
document.

 

[Sue Mosher [MVP-Outlook]]

Have you even tried mail merge in Word or reading the Help topic on it? It can take an Excel document as the data source. Try it by hand, starting within Word, and see if it produces good results for your data and message. If it does, then you can use Word objects to automate it from the code behind your spreadsheet.

Using a custom HTML-format message is relatively easy if you put placeholders (e.g. %cell1%, %cell2%) in the message where you want the data to appear. You code then simply uses the Replace function to substitute the actual cell data for the placeholder in the HTMLBody. But note that this will trigger security prompts. Mail merge is still the only way to do what you want with Office objects only and no prompts.
--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers