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.
Sending mail merge emails vba
-
- 5StarLounger
- Posts: 962
- Joined: 09 Feb 2010, 00:33
- Location: Patterson Lakes, Victoria, Australia
Sending mail merge emails vba
Avagr8day, regards, Peter
-
- Administrator
- Posts: 78582
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sending mail merge emails vba
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.
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
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 962
- Joined: 09 Feb 2010, 00:33
- Location: Patterson Lakes, Victoria, Australia
Re: Sending mail merge emails vba
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.
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.
Avagr8day, regards, Peter
-
- 5StarLounger
- Posts: 962
- Joined: 09 Feb 2010, 00:33
- Location: Patterson Lakes, Victoria, Australia
Re: Sending mail merge emails vba
Got it working.
Set oMainDoc = oApp.Documents.Open(sDocLoc)
Thanks Hans E10
Set oMainDoc = oApp.Documents.Open(sDocLoc)
Thanks Hans E10
Avagr8day, regards, Peter