How to copy the format in word document to Outlook Mail

  • Thread starter Sangeeta Michael
  • Start date
S

Sangeeta Michael

Hi,
I am trying to develop an application that creates a word object and then
generates a letter based on the data pulled out from the database. I need to
then send this letter from Outlook.
My code executes what i want it to but my problem is
1) i am creating a new table into an existing form (This does not keep the
font of the existing form)
2) When i pull the created document into outlook, it does not keep the
formatting, neither does it display the table.
I will give the code i have used for this process
Could some one help me with this?

Public Function EmailLetterTest()
On Error GoTo Failed
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset

Set con = Application.CurrentProject.Connection
Set rs = New ADODB.Recordset
'Get the data to be printed
rs.Open "select * from tTrainingSchedule where id=1", con

If rs.RecordCount = 0 Then
MsgBox "No data found"
Exit Function
End If

rs.MoveFirst
'********************************************************
'GENERATE LETTER
'Create Object for the new Word Application
Dim myWordObj As Object
Dim doc1 As Document
Set myWordObj = CreateObject("WORD.APPLICATION")
myWordObj.Visible = True



myWordObj.Documents.Add "F:\APPS\Colossus
II\Documents\TrainingConfirmationLetter.doc"
With myWordObj.Selection
.EndKey 6, 0
'.typetext Text:="Sangeeta Noby"
'.typeparagraph
myWordObj.ActiveDocument.Tables.Add
Range:=myWordObj.Selection.Range, NumRows:=1, NumColumns:=6,
DefaultTableBehavior:=2, AutoFitBehavior:=0
'.MoveRight unit:=12
.typetext Text:="Date"
.MoveRight unit:=12
.typetext Text:="Number of Participants"
.MoveRight unit:=12
.typetext Text:="Start Time"
.MoveRight unit:=12
.typetext Text:="End Time"
.MoveRight unit:=12
.typetext Text:="Estimated Amount"
.MoveRight unit:=12
.typetext Text:="Training Location"

.MoveRight unit:=12
.typetext Text:=CStr(rs.Fields("AllottedDate"))
.MoveRight unit:=12
.typetext Text:="1"
.MoveRight unit:=12
.typetext Text:=CStr(Format(rs.Fields("AllottedStartTime"), "h:mm
AM/PM"))
.MoveRight unit:=12
.typetext Text:=CStr(Format(rs.Fields("AllottedEndTime"), "h:mm
AM/PM"))
.MoveRight unit:=12
.typetext Text:=CStr(rs.Fields("EstimatedAmount"))
.MoveRight unit:=12
.typetext Text:=CStr(rs.Fields("TrainingLocation"))
'.Kind = wdEMail
End With


'************************************************************
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'GENERATE E-MAIL
Dim objOutlook As Outlook.Application
Dim aNamespace As NameSpace
Dim aFolder As MapiFolder
Dim aItems As Items
Dim aNewItem As Outlook.MailItem

Set objOutlook = CreateObject("OUTLOOK.APPLICATION")
Set aNamespace = objOutlook.GetNamespace("MAPI")
Set aFolder = aNamespace.GetDefaultFolder(OLFolderInbox)
Set aItems = aFolder.Items
Set aNewItem = objOutlook.CreateItem(OLMailitem)

With aNewItem
.Subject = "Testing"
.To = "(e-mail address removed)"
.BodyFormat = olFormatRichText 'I tried HTML and the other options does
no luk
.Body = myWordObj.Documents(1).Content
.Display
End With

Set myWordObj = Nothing
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Exit Function
Failed:
HandleError Err.Description, Err.Number, Err.Source, "test:EmailLetterTest"
End Function
 
E

Eric Legault [MVP - Outlook]

Try setting Outlook to use Word as the e-mail editor for new messages and see
if the formatting is better.
 
S

Sangeeta Michael

No luck
The table comes out like this

Date
Number of Participants
Start Time
End Time
Estimated Amount
Training Location

10/1/2006
1
9:00 AM
1:00 PM
1100
3
 
E

Eric Legault [MVP - Outlook]

You're right, I tried - doesn't work.

The easiest way to do this is without code - in Word, choose File -> Send To
-> Mail Recipient. Recipients will get exactly what you see in the Word
document in the e-mail message body.

Otherwise, you'd need to build your Word document in Outlook and use Word as
the e-mail editor. You can use the Inspector.WordEditor property to get a
handle to a Word.Document object and use Word VBA in your Outlook VBA Editor
to build the document contents, and then you'll have full control
programmatically over the sending of the e-mail.

--
Eric Legault (Outlook MVP, MCDBA, MCTS: Messaging & Collaboration)
Try Picture Attachments Wizard for Outlook:
http://www.collaborativeinnovations.ca
Blog: http://blogs.officezealot.com/legault/
 
S

Sangeeta Michael

Eric,
I cannot follow your easiest path becoz all the information in the to,
subject etc fields in the new mail is automatically generated from the
database and i have to fill them in the outlook object.
Could you give me some assistance on how i can do this using the second
method you suggested.
I am not that an expert on VBA. Could you help please?
 
E

Eric Legault [MVP - Outlook]

Your solution is a little complex because it involves both Word and Access,
so you'll need to do your part to migrate your code to Outlook. First of all
you'll need to enable Word as the e-mail editor in Outlook. Then your base
macro for creating the e-mail in Outlook will look like this:

Sub GenerateWordEmail
Dim objMail As Outlook.MailItem
Dim objInsp As Outlook.Inspector
Dim objWordDoc As Word.Document

Set objMail = Application.CreateItem(olMailItem)
Set objInsp = objMail.GetInspector
Set objWordDoc = objInsp.WordEditor

'Now work with the Word Document object mode via objWordDoc to automate
the creation of the content
'within the document, calling Access or whatever else you need

'Display or send when done
'objMail.Display
'objMail.Send
End Sub

--
Eric Legault (Outlook MVP, MCDBA, MCTS: Messaging & Collaboration)
Try Picture Attachments Wizard for Outlook:
http://www.collaborativeinnovations.ca
Blog: http://blogs.officezealot.com/legault/
 
S

Sangeeta Michael

Thank you so much eric,
I need some more assistance here
In a normal word object, i add an existing word document to it in this way
myWordObj.Documents.Add "F:\APPS\Colossus
II\Documents\TrainingConfirmationLetter.dot"
But when i used your code, i couldent get the documents property for the
word object.
could you help me add an existing document to this document object?
This is the code i used
Public Function GenerateWordEmail()
'------------Added my SN
Dim objOutlook As Outlook.Application
Set objOutlook = CreateObject("OUTLOOK.APPLICATION")
Dim myWordObj As Word.Application 'Testing adding a new document to
normal word object
'Dim doc1 As Document
Set myWordObj = CreateObject("WORD.APPLICATION")
'myWordObj.Visible = True
'myWordObj.Documents.Add "F:\APPS\Colossus
II\Documents\TrainingConfirmationLetter.dot"
'-----------------------------------


Dim objMail As Outlook.MailItem
Dim objInsp As Outlook.Inspector
Dim objWordDoc As Word.Document

Set objMail = objOutlook.CreateItem(OLMailitem)
Set objInsp = objMail.GetInspector
Set objWordDoc = objInsp.WordEditor
objWordDoc.Documents.Add "F:\APPS\Colossus
II\Documents\TrainingConfirmationLetter.dot"
objMail.Display
'objMail.Send
End Function
 
E

Eric Legault [MVP - Outlook]

This is where I'm afraid you'll have to do your homework Sangeeta. Try
posting in a Word programming newsgroup. Note that you can get a
Word.Application object from the Document.Application property. You may be
able to use that method to add a document, but I'm not sure how this would
work from Outlook. You may need to create a second Word.Application and use
that to automate opening the document, and copy contents to the Word.Document
object you get from Inspector.WordEditor.

--
Eric Legault (Outlook MVP, MCDBA, MCTS: Messaging & Collaboration)
Try Picture Attachments Wizard for Outlook:
http://www.collaborativeinnovations.ca
Blog: http://blogs.officezealot.com/legault/
 
S

Sangeeta Michael

Thank you so much for your help and assistance
Looks like i myself have to play around with it.
Thanks once again!
 
K

Ken Slovak - [MVP - Outlook]

FWIW, WordEditor.Application.Documents will return the Word.Documents
collection and you can add documents and/or templates to that collection
with no problems.

Of course WordEditor is restricted unless the addin is running in trusted
mode.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top