Mail Merge - Albert D. Kallal

[Question Boy]

Hello,

I am using Albert D. Kallal Mail Merge and need to then send the merged
document and the body of an e-mail while preserving the formatting of the
text.

Is there a way to modify the RidesMergeWord() to then use Word's Send To
....Mail Recipient function and insert an e-mail address in the TO fields and
a subject in the subject field?

Here is the function in question
Function RidesMergeWord(strDocName As String, _
strDataDir As String, _
Optional strOutDocName As String, _
Optional bolPrint As Boolean = False, _
Optional StrPrinter As String)

' This code takes a word document that has been setup as a MERGE document.
' This merge document is opened, then mailmerge is executed. The original
' document is then closed. The result is a raw word document with no
connectons
' to the merge.txt (a csv source data file).

'Parms:
' strDocName - full path name of word doc (.doc)
' strDataDir - dir (full path) where docuemnts and the
merge.888 file is placed
' strOutDocName - full path name of merged document (saved).
' bolPrint - if true, then output docuemnt is printed - if
strOutDocName is suppled then we close the docuemnt
' strPrinter - sends output to the printer name
'
'
' The above parms are suppled by other routines. You likey should not
need to call this
' routine directly. See the sub called MergeNoPrompts.

' Albert D. Kallal (c) 2001
' (e-mail address removed)
'
Dim wordApp As Object ' running instance of word
Dim WordDoc As Object ' one instance of a word doc
Dim WordDocM As Object ' one instance of a word doc
Dim strActiveDoc As String ' doc name (no path)
Dim lngWordDest As Long ' const for dest, 0 = new doc, 1 =
printer
' Dim MyPbar As New clsRidesPBar ' create a instance of our
Progress bar.


' MyPbar.ShowProgress
' MyPbar.TextMsg = "Launching Word...please wait..."
' MyPbar.Pmax = 4 ' 4 steps to inc
' MyPbar.IncOne ' step 1....start!

On Error GoTo CreateWordApp
Set wordApp = GetObject(, "Word.Application")
On Error Resume Next

' MyPbar.IncOne ' step 2, word is loaded.

Set WordDoc = wordApp.Documents.Open(strDocName)

' MyPbar.IncOne ' step 3, doc is loaded

strActiveDoc = wordApp.ActiveDocument.Name
'wordApp.Activate

If bolPrint = False Then
wordApp.Visible = True
wordApp.Activate
wordApp.WindowState = 0 'wdWindowStateRestore
End If

WordDoc.MailMerge.OpenDataSource _
Name:=strDataDir & TextMerge, _
ConfirmConversions:=False, _
ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="",
WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=0, _
Connection:="", SQLStatement:="", SQLStatement1:=""


With WordDoc.MailMerge
.Destination = 0 ' 0 = new doc
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .datasource
.FirstRecord = 1
' .LastRecord = 1
End With
.Execute Pause:=False
End With
Set WordDocM = wordApp.ActiveDocument

' MyPbar.IncOne ' step 4, doc is merged
WordDoc.Close (False)

wordApp.Visible = True

If strOutDocName <> "" Then
'wordApp.ActiveDocument.SaveAs strOutDocName
WordDocM.SaveAs strOutDocName
End If

If bolPrint = False Then

WordDocM.Activate

Else

' print this document
If StrPrinter <> "" Then
With wordApp.Dialogs(97) ' 97 - wdDialogFilePrintSetup
.Printer = StrPrinter
.DoNotSetAsSysDefault = True
.Execute
End With
End If


WordDocM.PrintOut
'If strOutDocName <> "" Then
'wordApp.ActiveDocument.Close (False)
' when we print...we *always* close the docuemnt..

WordDocM.Close (False)

'End If

wordApp.Visible = True

End If


' MyPbar.HideProgress

Set wordApp = Nothing
Set WordDoc = Nothing
Set WordDocM = Nothing
' Set MyPbar = Nothing

DoEvents

' If bolShowMerge = True Then
' WordApp.Dialogs(676).Show 'wdDialogMailMerge
' End If

Exit Function

CreateWordApp:
' this code is here to use the EXISTING copy of
' ms-access running. If getobject fails, then
' ms-word was NOT running. The below will then
' launch word
Set wordApp = CreateObject("Word.Application")
Resume Next

End Function



After the WordDocM.Activate can this be done?

Thank you,

QB

 

[Albert D. Kallal]

I don't think the email fields/options can be automated inside of word. (at
least, I not done it before).

hence, the code you posted is not going to be of much use here. Furthermore,
your email format
it not likely to be the same as the word format used.

You can most certainly use my example code to produce a word document, and
then ATTACHED that document to a outlook email via automaton, but the sample
code is not going to be much use if you actually are trying to *create* the
email body.
(unless someone jumps in, and shows me how to use the word as email feature
via automaton).

So, you could use my code, and the MergeNoPrmopts feature outlined here to
create a output document:
http://www.members.shaw.ca/AlbertKallal/wordmerge/page2.html

The command is:

MergeNoPrompts
strFromDocTemplate,[strDir],[bolFullPath],[strOutPutDoc],[strSql],[bolPrint],[strPrinter]

please see the above link for a explain of the options for the above.

Once you make the output document, then you can use outlook automation to
attach that document and send it.

eg:

dim strDocName as string
' send to user via email
'Dim ol As Outlook.Application
Dim ol As Object ' Late binding
'Dim ns As NameSpace
Dim ns As Object ' Late bind
'Dim newmessage As MailItem
Dim newmessage As Object ' Late bind

Dim mymessage As String
'Dim objOutlookAttach As Outlook.Attachment
'Dim objOutlookAttach As Object

strDocname = "c:\mydata\Report.doc"

Set ol = GetObject(, "Outlook.Application")
Set ns = ol.GetNamespace("MAPI")
ns.Logon
Set newmessage = ol.CreateItem(0) ' 0 = olMainItem
With newmessage
.Recipients.Add strEmailTo
.Subject = strSubject
.Body = strMsgText
'Set objOutlookAttach = .Attachments.Add(stDocName)
.Attachments.Add (strDocName)
.Display
' .Send
End With

 

[Question Boy]

I'm working on another approach and will post back once working.

Save merge doc as HTML doc the use it as .HTMLBody in outlook automation.

If that doesn't work out, then I will use the attachment approach.

Thank you for the advice and thank you for the sample code and great mail
merge sample db to learn from!

QB

 

[aimai]

I have used Albert Kallal's code to merge to email rather than merge to word documents. Here is how I've modified the code in RidesMergeWord

   With WordDoc.MailMerge
      .Destination = 2        ' 0 = new doc
                            '2 = merge to email
      .MailAsAttachment = False
      .MailAddressFieldName = "Email" ' "Email" is the field on the data source, original was ""
      .MailSubject = "Test email"   'I am still looking for a way to tie the subject to the word template? AM ""
      .SuppressBlankLines = True
      With .datasource
         .FirstRecord = 1
'            .LastRecord = 1
      End With
      .Execute Pause:=False
   End With