We help IT Professionals succeed at work.

Mail Merge

Medium Priority
574 Views
Last Modified: 2012-06-22
I currently use Access 2010 to perform a mail merge of thousands of customers. During this mail merge a word document is placed into the email that they all receive. The word document is the same for all customers so nothing fancy for this such as customer name added to each document. My issue is with wanting to move this procedure to Excel 2010 and remove the Access 2010 from the equation. This way we still use Outlook 2010 and pull from the Excel 2010 database and include the Word 2010 document. How can I accomplish this or is it even possible. Or does a piece of software exist to make this easier.
Comment
Watch Question

Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
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"
CERTIFIED EXPERT
Top Expert 2009

Commented:
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.
CERTIFIED EXPERT
Top Expert 2009

Commented:
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

Open in new window

CERTIFIED EXPERT
Top Expert 2009

Commented:
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.
CERTIFIED EXPERT
Top Expert 2009

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

Author

Commented:
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?
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

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

Jeff

Author

Commented:
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.
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010
Commented:
Hello,

after following Jeff's instructions to export the data to Excel, you can open the Word merge document and on the Mailings ribbon click Select Recipients > Use existing list > navigate to the Excel file and select it as the source file for the merge.

To include attachments into the mail merge, you could use an add-in developed by Graham Mayor

http://www.gmayor.com/MailMergeWithAttachments.htm

cheers, teylyn

Author

Commented:
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.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.