CODEWITHSUNDEEP
vbawindowsms-word
Hal Egbert
Hal Egbert

Reputation: 98

Word: VBA recorded macro gives runtime error

I have a daily task two or three very simple word docs, I do a mail merge to send the docs to addresses from Excel. Very simple Mailings, Start mail merge - Email, select data source- xlsm file, clean up act and send. Verry very simple. However, a single button click would be easier!! Recorded a vba macro of the operation. After completion of recording, Clicked the button to start the macro, received this run time error: "by mail or fax without a valid mail address. Choose the Setup button to select a mail address data field." Tried several times, What is causing the error, how to fix? Highlighted step on error: "Execute Pause:=False" Code from recording :

Sub sendMailSheet1()
'
' sendMail Macro
'
'
'MsgBox "In Sheets1"
    ActiveDocument.MailMerge.MainDocumentType = wdEMail
    ActiveDocument.MailMerge.OpenDataSource Name:= _
        "C:\Users\Hal 1GB M2\Documents\MyEmailer.xlsm", ConfirmConversions:=False _
        , ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
        PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
        WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
        Connection:= _
        "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\Hal 1GB M2\Documents\MyEmailer.xlsm;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Lo" _
        , SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="", SubType:= _
        wdMergeSubTypeAccess
    With ActiveDocument.MailMerge
        .Destination = wdSendToEmail
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With
End Sub

Upvotes: 1

Views: 98

Answers (1)

jonsson
jonsson

Reputation: 1301

A merge to email needs to know which field/column in the Data Source contains the email address it should use to send each email.

The macro recorder does not record the line of code needed for that. (The macro recorder can be a useful starting point but it doesn't record everything). After the line .Destination = wdSendToEmail you need something like

    .MailAddressFieldName = "email"

Where email is the exact name of the Excel column (it's case-sensitive) containing the e-address.

You may also need

    .MailFormat = wdMailFormatHTML ' or wdMailFormatPlainText
    .MailSubject = "the text you want as the subject of every email"

Upvotes: 1

Related Questions