Sending mail merge emails vba

[Peter Kinross]

Does anyone know of any code to send Word mail merge emails using vba from within Access. Maybe being too optimistic, but saving each doc to it's file location as we go.
Given time, maybe I could work it out, but would prefer not to re-invent the wheel, especially at this stage of senility.

[HansV]

Here is an example based on documentation from Microsoft. It uses late binding, so you don't have to set a reference to the Microsoft Word Object Library. I have left the early binding parts as comments.

Warning: I haven't tested the code in action.

Code: Select all

Private Sub Command1_Click()
    Dim oApp As Object ' Word.Application
    Dim oMainDoc As Object ' Word.Document
    Dim oSel As Object ' Word.Selection
    Dim sDBPath As String

    Set oApp = CreateObject("Word.Application")

    'Start a new main document for the mail merge.
    Set oMainDoc = oApp.Documents.Add

    With oMainDoc.MailMerge
        .MainDocumentType = 4 ' wdEMail

        'Set up the mail merge data source
        sDBPath = "C:\Users\Peter\Documents\MyDatabase.accdb"
        .OpenDataSource Name:=sDBPath, SQLStatement:="SELECT * FROM [Customers]"

       'Add the mailmerge field codes to the document.
        With .Fields
            Set oSel = oApp.Selection
            oSel.TypeText "Dear "
            ' Here, we add a mailmerge field named ContactName.
            .Add oSel.Range, "ContactName"
            oSel.TypeText ","
            oSel.TypeParagraph
            oSel.TypeParagraph
            oSel.TypeText " This message is to inform you that ..."
            oSel.TypeParagraph
            oSel.TypeParagraph
            oSel.TypeText "Sincerely, Peter Kinross"
        End With

        ' Specify the email field
        .MailAddressFieldName = "Email"
        ' False to send as the body of the message, True to send as attachment.
        .MailAsAttachment = False
        ' Send as HTML
        .MailFormat = 1 ' wdMailFormatHTML
        ' Subject.
        .MailSubject = "News from Peter Kinross"

        'Perform the mail merge to a new document.
        .Destination = 2 ' wdSendToEmail
        .Execute Pause:=True
    End With

    oApp.Quit SaveChanges:=False
    Set oApp = Nothing
End Sub

[Peter Kinross]

Having a go.
No way that the required mailmerge doc can be built from code, so Set oMainDoc = oApp.Documents.Add is not appropriate. Therefor I am using a mailmerge doc that I have created that uses a Db table as data source for the only field in the doc - Firstname (the email addresses are also in the Data source. I just need to open that doc and send as mailmerged emails
However, I can't get this to work:
Set oMainDoc =oApp.Documents.Open FileName:=sDocLoc
The word 'Filename' is highlighted with the error Compile error: Expected end of statement.

[Peter Kinross]

Got it working.
Set oMainDoc = oApp.Documents.Open(sDocLoc)
Thanks Hans E¹⁰