E-mail/ mail merge/ automation. Almost there...?!

[mattkorguk]

Hi,

I'm trying to e-mail a document to a list of people. This document is a mailmerge document so it includes some of their details as well as their name etc. I also have another "template" document (also a merge document to the same list, and this is my .body text of the e-mail.

I seem to be missing a way of moving through the merge documents. The code below will create the 3 required e-mails (currently set to display for testing) but the .body of each e-mail relates to the first record only.

Also the attachment is still a merge document, but I'd like this to some how be specific to each record also.

It's so close I can almost smell it!!! I must be missing a simple .movenext or similar somewhere...

Private Sub cmdTest_Click()
Dim rsEmail As DAO.Recordset
Dim strEmail As String
Dim oItem As Outlook.MailItem
Dim Datarange As Recipients
Dim oOutlookApp As Outlook.Application
Dim oDoc As Word.Application
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDM-Export-Cases-Email", acViewNormal
Set oOutlookApp = GetObject(, "Outlook.Application")
Set rsEmail = CurrentDb.OpenRecordset("ll-mergeEmail")
    strEmail = rsEmail.Fields("[Email Addr]").Value
    message = "Enter the subject to be used for each e-mail message."
    Title = "Email Subject"
    mysubject = InputBox(message, Title)
Do While Not rsEmail.EOF
strEmail = strEmail
Set oDoc = CreateObject("Word.Application")
    oDoc.Documents.Open ("C:\ll-Export\" & Me.selLetter.Value & ".doc")
    strMessage = oDoc.ActiveDocument.Content
    
Set oItem = oOutlookApp.CreateItem(olMailItem)
    
    With oItem
        .Subject = mysubject
        .Body = strMessage
        .To = rsEmail.Fields("[Email Addr]").Value
        .Attachments.Add Me.fPath.Value, olByValue, 1, "Important Information"
        .Display
    End With
rsEmail.MoveNext
 
Loop
oDoc.Application.Quit
Set rsEmail = Nothing
Set oLetter = Nothing
End Sub

Thanks

 

[mearle]

A few comments:
strEmail = strEmail - this does nothing
Set oDoc = CreateObject("Word.Application") - this opens a fresh copy of word each time,which is never closed. I would put this outside of the loop, and use the document Open and Close methods in the loop.
DoCmd.OpenQuery "qryDM-Export-Cases-Email", acViewNormal - what is the intention here ?
Me.selLetter.Value/Me.fPath.Value - this doesn't change with rsEmail.MoveNext - so you are opening the same document again and again.

 

[mattkorguk]

OK mearle, comments taken on board:
1. strEmail - Not sure where I was going with that, now removed.
2. A valid comment, I'll move that out of the loop.
3. qryDM-Export-Cases-Email provides the list which both merge documents refer to.
4. That's ok because selLetter references a drop down list on screen providing the merge letter to use as the body of the e-mail and fPath is the location, also from a drop down list of the attachment merge document.

This kind of operation must have been done before?!
Personalise an e-mail to a list of customers and attach another 'personalised' word document. One e-mail per customer each with an attachment.

 

[mearle]

Ah, I see, so you haven't actually done the merge yet ?
Opening the query on it's own won't do it for you.
I guess what you need to do is to record a macro in word of you manually doing the merge, and then look at the code produced to get a clue as to how to do it.
Use the key lines of code from the macro, and then figure out how to refer to the newly created merged documents - ie change oDoc.ActiveDocument to something like oDoc.Documents(i) (a guess).
Having a look at the word object model in help will be useful.

 

[mattkorguk]

mearle - Thanks again - The merge documents have already been created, the drop downs just allow the user to select which merge document for a specific mailing list. The query just builds the list which the letters merge to.
It's the moving through each record I'm having issues with.

 

[mearle]

The merge documents might have been created, but as far as I can tell, I don't think you are doing a merge in Word, which is needed to create the separate documents that you need to attach - and this is your problem. You are only selecting the merge template, not the new merged documents. Is this right ?

DoCmd.OpenQuery "qryDM-Export-Cases-Email", acViewNormal

The above is not sufficient to do the merge. All this does is open the query in it's own window, and Word knows nothing about this - the query needs to be run via Word.

If you record a macro in word, you will get something like the following:
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With

You need something similar to this to create the documents (in fact seperate sections in one document, which you need to select in code).
Interestingly, one of the options for .Destination is to send via email, which could save you the bother of selecting the text into Outlook.

 

[mattkorguk]

mearle - you are correct, that makes a lot of sense. I shall look at that first thing tomorrow. Thanks again for your help.

 

[mearle]

On the other hand, I'd forgotton you can step through the merge records one by one, so the following might work for the way you intended:
ActiveDocument.MailMerge.ViewMailMergeFieldCodes = False
ActiveDocument.MailMerge.DataSource.ActiveRecord = wdNextRecord

 

[mattkorguk]

mearle - That is fantastic, works great for the body of the mail, just working on the attachment now...

 

[mattkorguk]

Solution...

Hi,
After discussions with the boss, we've decided to go through with just the body merge taking place and a generic attachment. I might return to the attachment at some point!

Private Sub cmdEmail_Click()
Dim rsEmail As DAO.Recordset
Dim oItem As Outlook.MailItem
Dim Datarange As Recipients
Dim oOutlookApp As Outlook.Application
Dim oDoc As Word.Application
Dim objInsp As Outlook.Inspector
Dim strMessage
Dim WinWnd As Long, Ret As String, RetVal As Long, lpClassName As String
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDM-Export-Cases-Email", acViewNormal
Set oOutlookApp = GetObject(, "Outlook.Application")
    message = "Enter the subject to be used for each e-mail message."
    Title = "Email Subject"
    mysubject = InputBox(message, Title)
DoCmd.Hourglass True
Set rsEmail = CurrentDb.OpenRecordset("ll-mergeEmail")
Set oDoc = CreateObject("Word.Application")
Do While Not rsEmail.EOF
    oDoc.Documents.Open ("C:\ll-Export\" & Me.selLetter.Value & ".doc")
Set oItem = oOutlookApp.CreateItem(olMailItem)
    strMessage = oDoc.ActiveDocument.Content
    
    With oItem
        .Subject = mysubject
        .Body = strMessage
        .To = rsEmail.Fields("[Email Addr]").Value
        .Attachments.Add Me.fPath.Value, olByValue, 1, mysubject & "Attachment"
        .Display
    End With
rsEmail.MoveNext
oDoc.ActiveDocument.MailMerge.ViewMailMergeFieldCodes = False
oDoc.ActiveDocument.MailMerge.DataSource.ActiveRecord = wdNextRecord
Loop
oDoc.Documents.Close (False)
oDoc.Application.Quit (False)
Set rsEmail = Nothing
Set oLetter = Nothing
Set oDoc = Nothing
Set oItem = Nothing
DoCmd.Hourglass False
End Sub

Thanks mearle for all your help with this one.