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