Mail Merge

The issue with "removing access from the equation" is how much else of this system is in Access.
In other words, if you now want to use Excel,then why was Access used in the first place?

At the most basic level, You can simply export the Access table/Query to Excel and be done.

    DoCmd.TransferSpreadsheet acExport, , "YourTableOrQueryName", "C:\YourFolder\YourFile.xls"

If the email addresses are in an Excel workbook, you can certainly write code to loop through them and produce an email for each, including a Word doc either as an attachment or in the body of the email, using the Envelope object.  This is not exactly mail merge, since the Word doc  is the same for all records.  I will work up some sample code and post it here shortly.

Here is the code:

Public Sub SendEmails()
'Created by Helen Feddema 2-Nov-2011
'Last modified by Helen Feddema 2-Nov-2011

On Error GoTo ErrorHandler

   Dim appOutlook As New Outlook.Application
   Dim lngLastRow As Long
   Dim lngRow As Long
   Dim msg As Outlook.MailItem
   Dim rng As Excel.Range
   Dim strEmail As String
   Dim strWordDoc As String
   Dim sht As Excel.Worksheet
   
   Set sht = Application.ActiveSheet
   strWordDoc = Application.ActiveWorkbook.Path & "\Word Document.docx"
   Debug.Print "Word doc and path: " & strWordDoc
   lngLastRow = sht.UsedRange.Rows.Count
   Debug.Print "Last used row: " & lngLastRow
   
   For lngRow = 1 To lngLastRow
      Set rng = Application.ActiveSheet.Range("A" & CStr(lngRow))
      strEmail = rng.Text
      Debug.Print "Email: " & strEmail
      Set msg = appOutlook.CreateItem(olMailItem)
      msg.To = strEmail
      msg.Attachments.Add strWordDoc
      'msg.Send
      msg.Display
   Next lngRow
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in SendEmails procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Select allOpen in new window

You need a reference to the Outlook object library, and the code assumes that the Word document is in the same folder as the workbook from which the code is running.  You could hard-code the document path instead.

To send the messages immediately, uncomment the msg.Send line.

Helen,

How do you use this code? I just want to change the .mdb datasource for the mailing to a .xlsx datasource. Can you help with this?

You have obviously ignored my post, so I'll let you continue on with Helen.

Jeff

Boag2000
I did not ignore your post but u gave me little info to question. I did the basic and exported to excel before u even responded with your suggestion. If you have something intelligent to add other than the basic then I am willing to listen. Besides the points have not been awarded so why throw in the towel. This is a community of assistance and when I use the service I listen to all those responding because I know you guys are worth listening to.

tdbrowning,
:-)

I'll clarify.....
...as my post may have sounded a little "grumpier" than I had intended...
;-)

A lot of times a member will look at all the posts, and decide only reply to the one that they think will help.
It's a bit "Brisk", ...but it happens.
;-)
I answer enough questions, so I have a thick skin about this, because I understand that many people just want to get their issue resolved in the shortest amount of time, so sometimes it is best for an expert to step aside.

But in all honesty, my guess was that perhaps I just misunderstood your request.

Helen sometimes has an uncanny ability to see something that the rest of us miss.
And it seemed like she had invested a fair amount of time and energy into her replies, so I thought that I would just step aside.

As far as my post goes...
It was just a quick shot...
If you didn't want Access, then just export the data...
I thought it was that simple...

I'll stay around and see how this progresses.


Finally, Thanks for taking the time to reply in professional manner.
Considering that you posted from a mobile device, I really have to give you credit for that
;-)

Jeff

Teylyn,

This was exactly what I needed to know. Sometimes it is hard to ask the right question to find out a direct answer. I certainly did not want to mess around with any code !!

Thanks Much.