[DataSpeak Support]

I'm sending/emailing a doc attached with data from access (works fine) but i
need to have a cover letter also in the body of the email or as a second
attachment.

i would prefer cover in the body of the email and doc attachment

when i send from/mailmerge inside of the doc it attachs but the body is blank.
- no signature
- no stationary
- no way to to format anything in the body

Thanks
Sandy

[Peter Jamieson]

If you are merging to Outlook (not Outlook Express) you can do something
about this using a VBA macro to do "one merge per data source record.

Personally I would suggest that you stick to using a plain text body, for
which you can try something based on the following VBA:

Sub EmailOneDocPerSourceRecWithBody()
Dim bOutlookStarted As Boolean
Dim bTerminateMerge As Boolean
Dim intSourceRecord As Integer
Dim objMailItem As Outlook.MailItem
Dim objMerge As Word.MailMerge
Dim objOutlook As Outlook.Application
Dim strMailSubject As String
Dim strMailTo As String
Dim strMailBody As String
Dim strOutputDocumentName As String


bOutlookStarted = False
bTerminateMerge = False


' Set up a reference to the
' Activedocument, partly because
' the ActiveDocument changes as you
' merge each record


Set objMerge = ActiveDocument.MailMerge


' Start Outlook as necessary


On Error Resume Next
Set objOutlook = GetObject(, "Outlook.Application")
If Err 0 Then
Set objOutlook = CreateObject("Outlook.Application")
bOutlookStarted = True
End If


With objMerge


' If no data source has been defined,
' do it here using OpenDataSource.
' But if it is already defined in the
' document, you should not need to
' define it here.


' .OpenDataSource _
' Name:="whatever"


intSourceRecord = 1


Do Until bTerminateMerge
.DataSource.ActiveRecord = intSourceRecord


' if we have gone past the end
' (and possibly, if there are no records)
' then the Activerecord will not be what
' we have just tried to set it to


If .DataSource.ActiveRecord intSourceRecord Then
bTerminateMerge = True
' the record exists
Else


' while we are looking at the
' correct activerecord,
' create the mail subject, body and "to"
' Just some sample code here - replace it with
' whatever you need


strMailSubject = _
"Results for " & _
objMerge.DataSource.DataFields("Firstname") & _
" " & objMerge.DataSource.DataFields("Lastname")


strMailBody = _
"Dear " & objMerge.DataSource.DataFields("Firstname") & _
vbCrLf & _
"Please find attached a Word document containing" & vbCrLf & _
"your results for..." & vbCrLf & _
vbCrLf & _
"Yours" & vbCrLf & _
"Your name"
strMailTo = objMerge.DataSource.DataFields("Emailaddress")


' create the document path name
' In this case it can be te same for every recipient,
' but if you want to retain copies of the
' document, you can use info. in the data source


' this is an example - insert your
' own pathname here


strOutputDocumentName = "c:\a\results.doc"


' strOutputDocumentName = _
' "c:\mymergeletters\_" & _
' .DataSource.DataFields("Lastname").Value & _
' " letter.doc"
.DataSource.FirstRecord = intSourceRecord
.DataSource.LastRecord = intSourceRecord
.Destination = wdSendToNewDocument
.Execute


' The Activedocument is always the
' output document


' Add any parameters you need to these calls
ActiveDocument.SaveAs strOutputDocumentName
ActiveDocument.Close


' Now create a mail item


Set objMailItem = objOutlook.CreateItem(olMailItem)
With objMailItem
.Subject = strMailSubject
.Body = strMailBody
.To = strMailTo
.Attachments.Add strOutputDocumentName, olByValue, 1
'.Save
.Send
End With
Set objMailItem = Nothing


intSourceRecord = intSourceRecord + 1
End If
Loop
End With


' Close Outlook if appropriate


If bOutlookStarted Then
objOutlook.Quit
End If


Set objOutlook = Nothing
Set objMerge = Nothing


End Sub


However, if you need an HTML body, try starting with (the rather similar)

Sub EmailOneHTMLPagePerSourceRecWithBody()
' By Peter Jamieson, 2006
Dim bOutlookStarted As Boolean
Dim bTerminateMerge As Boolean
Dim intSourceRecord As Integer
Dim objMailItem As Outlook.MailItem
Dim objMerge As Word.MailMerge
Dim objOutlook As Outlook.Application
Dim strMailSubject As String
Dim strMailTo As String
Dim strMailBody As String
Dim strOutputDocumentName As String

bOutlookStarted = False
bTerminateMerge = False

' Set up a reference to the
' Activedocument, partly because
' the ActiveDocument changes as you
' merge each record

Set objMerge = ActiveDocument.MailMerge

' Start Outlook as necessary

On Error Resume Next
Set objOutlook = GetObject(, "Outlook.Application")
If Err 0 Then
Set objOutlook = CreateObject("Outlook.Application")
bOutlookStarted = True
End If

With objMerge

' If no data source has been defined,
' do it here using OpenDataSource.
' But if it is already defined in the
' document, you should not need to
' define it here.

' .OpenDataSource _
' Name:="whatever"

intSourceRecord = 1

Do Until bTerminateMerge
.DataSource.ActiveRecord = intSourceRecord

' if we have gone past the end
' (and possibly, if there are no records)
' then the Activerecord will not be what
' we have just tried to set it to

If .DataSource.ActiveRecord intSourceRecord Then
bTerminateMerge = True
' the record exists
Else

' while we are looking at the
' correct activerecord,
' create the mail subject, body and "to"
' Just some sample code here - replace it with
' whatever you need

strMailSubject = _
"Results for " & _
objMerge.DataSource.DataFields("Firstname") & _
" " & objMerge.DataSource.DataFields("Lastname")

' Use a simple sample
strMailBody = "HTMLBODYTABLE
BORDER=5TRTDk/TDTDt/TD/TR/TABLE/BODY/HTML"
strMailTo = objMerge.DataSource.DataFields("Emailaddress")

' create the document path name
' In this case it can be the same for every recipient,
' but if you want to retain copies of the
' document, you can use info. in the data source

' this is an example - insert your
' own pathname here

strOutputDocumentName = "c:\a\results.htm"

' strOutputDocumentName = _
' "c:\mymergeletters\_" & _
' .DataSource.DataFields("Lastname").Value & _
' " letter.doc"
.DataSource.FirstRecord = intSourceRecord
.DataSource.LastRecord = intSourceRecord
.Destination = wdSendToNewDocument
.Execute

' The Activedocument is always the
' output document

' Add any parameters you need to these calls
ActiveDocument.SaveAs strOutputDocumentName, wdFormatFilteredHTML
ActiveDocument.Close

' Now create a

Set objMailItem = objOutlook.CreateItem(olMailItem)
With objMailItem
.BodyFormat = olFormatHTML
.Subject = strMailSubject
.HTMLBody = strMailBody
.To = strMailTo
.Attachments.Add strOutputDocumentName, olByValue, 1
.Save
.Send
End With
Set objMailItem = Nothing

intSourceRecord = intSourceRecord + 1
End If
Loop
End With

' Close Outlook if appropriate

If bOutlookStarted Then
objOutlook.Quit
End If

Set objOutlook = Nothing
Set objMerge = Nothing

End Sub

Peter Jamieson
"DataSpeak Support" wrote in
message ...

I'm sending/emailing a doc attached with data from access (works fine) but
i
need to have a cover letter also in the body of the email or as a second
attachment.

i would prefer cover in the body of the email and doc attachment

when i send from/mailmerge inside of the doc it attachs but the body is
blank.
- no signature
- no stationary
- no way to to format anything in the body

Thanks
Sandy

[Peter Jamieson]

Also, with the macro open in the VBA editor, you need to use Tools|Reference
to add a reference to the appropriate Microsoft Outlook library (e.g.
Microsoft Outlook 11.0 Object Library)

Peter Jamieson
"Peter Jamieson" wrote in message
...
If you are merging to Outlook (not Outlook Express) you can do something
about this using a VBA macro to do "one merge per data source record.

Personally I would suggest that you stick to using a plain text body, for
which you can try something based on the following VBA:

Sub EmailOneDocPerSourceRecWithBody()
Dim bOutlookStarted As Boolean
Dim bTerminateMerge As Boolean
Dim intSourceRecord As Integer
Dim objMailItem As Outlook.MailItem
Dim objMerge As Word.MailMerge
Dim objOutlook As Outlook.Application
Dim strMailSubject As String
Dim strMailTo As String
Dim strMailBody As String
Dim strOutputDocumentName As String


bOutlookStarted = False
bTerminateMerge = False


' Set up a reference to the
' Activedocument, partly because
' the ActiveDocument changes as you
' merge each record


Set objMerge = ActiveDocument.MailMerge


' Start Outlook as necessary


On Error Resume Next
Set objOutlook = GetObject(, "Outlook.Application")
If Err 0 Then
Set objOutlook = CreateObject("Outlook.Application")
bOutlookStarted = True
End If


With objMerge


' If no data source has been defined,
' do it here using OpenDataSource.
' But if it is already defined in the
' document, you should not need to
' define it here.


' .OpenDataSource _
' Name:="whatever"


intSourceRecord = 1


Do Until bTerminateMerge
.DataSource.ActiveRecord = intSourceRecord


' if we have gone past the end
' (and possibly, if there are no records)
' then the Activerecord will not be what
' we have just tried to set it to


If .DataSource.ActiveRecord intSourceRecord Then
bTerminateMerge = True
' the record exists
Else


' while we are looking at the
' correct activerecord,
' create the mail subject, body and "to"
' Just some sample code here - replace it with
' whatever you need


strMailSubject = _
"Results for " & _
objMerge.DataSource.DataFields("Firstname") & _
" " & objMerge.DataSource.DataFields("Lastname")


strMailBody = _
"Dear " & objMerge.DataSource.DataFields("Firstname") & _
vbCrLf & _
"Please find attached a Word document containing" & vbCrLf & _
"your results for..." & vbCrLf & _
vbCrLf & _
"Yours" & vbCrLf & _
"Your name"
strMailTo = objMerge.DataSource.DataFields("Emailaddress")


' create the document path name
' In this case it can be te same for every recipient,
' but if you want to retain copies of the
' document, you can use info. in the data source


' this is an example - insert your
' own pathname here


strOutputDocumentName = "c:\a\results.doc"


' strOutputDocumentName = _
' "c:\mymergeletters\_" & _
' .DataSource.DataFields("Lastname").Value & _
' " letter.doc"
.DataSource.FirstRecord = intSourceRecord
.DataSource.LastRecord = intSourceRecord
.Destination = wdSendToNewDocument
.Execute


' The Activedocument is always the
' output document


' Add any parameters you need to these calls
ActiveDocument.SaveAs strOutputDocumentName
ActiveDocument.Close


' Now create a mail item


Set objMailItem = objOutlook.CreateItem(olMailItem)
With objMailItem
.Subject = strMailSubject
.Body = strMailBody
.To = strMailTo
.Attachments.Add strOutputDocumentName, olByValue, 1
'.Save
.Send
End With
Set objMailItem = Nothing


intSourceRecord = intSourceRecord + 1
End If
Loop
End With


' Close Outlook if appropriate


If bOutlookStarted Then
objOutlook.Quit
End If


Set objOutlook = Nothing
Set objMerge = Nothing


End Sub


However, if you need an HTML body, try starting with (the rather similar)

Sub EmailOneHTMLPagePerSourceRecWithBody()
' By Peter Jamieson, 2006
Dim bOutlookStarted As Boolean
Dim bTerminateMerge As Boolean
Dim intSourceRecord As Integer
Dim objMailItem As Outlook.MailItem
Dim objMerge As Word.MailMerge
Dim objOutlook As Outlook.Application
Dim strMailSubject As String
Dim strMailTo As String
Dim strMailBody As String
Dim strOutputDocumentName As String

bOutlookStarted = False
bTerminateMerge = False

' Set up a reference to the
' Activedocument, partly because
' the ActiveDocument changes as you
' merge each record

Set objMerge = ActiveDocument.MailMerge

' Start Outlook as necessary

On Error Resume Next
Set objOutlook = GetObject(, "Outlook.Application")
If Err 0 Then
Set objOutlook = CreateObject("Outlook.Application")
bOutlookStarted = True
End If

With objMerge

' If no data source has been defined,
' do it here using OpenDataSource.
' But if it is already defined in the
' document, you should not need to
' define it here.

' .OpenDataSource _
' Name:="whatever"

intSourceRecord = 1

Do Until bTerminateMerge
.DataSource.ActiveRecord = intSourceRecord

' if we have gone past the end
' (and possibly, if there are no records)
' then the Activerecord will not be what
' we have just tried to set it to

If .DataSource.ActiveRecord intSourceRecord Then
bTerminateMerge = True
' the record exists
Else

' while we are looking at the
' correct activerecord,
' create the mail subject, body and "to"
' Just some sample code here - replace it with
' whatever you need

strMailSubject = _
"Results for " & _
objMerge.DataSource.DataFields("Firstname") & _
" " & objMerge.DataSource.DataFields("Lastname")

' Use a simple sample
strMailBody = "HTMLBODYTABLE
BORDER=5TRTDk/TDTDt/TD/TR/TABLE/BODY/HTML"
strMailTo = objMerge.DataSource.DataFields("Emailaddress")

' create the document path name
' In this case it can be the same for every recipient,
' but if you want to retain copies of the
' document, you can use info. in the data source

' this is an example - insert your
' own pathname here

strOutputDocumentName = "c:\a\results.htm"

' strOutputDocumentName = _
' "c:\mymergeletters\_" & _
' .DataSource.DataFields("Lastname").Value & _
' " letter.doc"
.DataSource.FirstRecord = intSourceRecord
.DataSource.LastRecord = intSourceRecord
.Destination = wdSendToNewDocument
.Execute

' The Activedocument is always the
' output document

' Add any parameters you need to these calls
ActiveDocument.SaveAs strOutputDocumentName, wdFormatFilteredHTML
ActiveDocument.Close

' Now create a

Set objMailItem = objOutlook.CreateItem(olMailItem)
With objMailItem
.BodyFormat = olFormatHTML
.Subject = strMailSubject
.HTMLBody = strMailBody
.To = strMailTo
.Attachments.Add strOutputDocumentName, olByValue, 1
.Save
.Send
End With
Set objMailItem = Nothing

intSourceRecord = intSourceRecord + 1
End If
Loop
End With

' Close Outlook if appropriate

If bOutlookStarted Then
objOutlook.Quit
End If

Set objOutlook = Nothing
Set objMerge = Nothing

End Sub

Peter Jamieson
"DataSpeak Support" wrote in
message ...

I'm sending/emailing a doc attached with data from access (works fine)
but i
need to have a cover letter also in the body of the email or as a second
attachment.

i would prefer cover in the body of the email and doc attachment

when i send from/mailmerge inside of the doc it attachs but the body is
blank.
- no signature
- no stationary
- no way to to format anything in the body

Thanks
Sandy