|
|
Add-in for Word 2007 & 2010 to merge an Excel data file to
e-mail with attachments |
|
|
This add-in
was inspired by a macro produced by fellow MVP and frequent collaborator
Doug Robbins to mail merge a document to e-mail either as an attachment
or with attachments (or both). The add-in requires that the data source
is an Excel worksheet and that Outlook is available to send the
messages. If your version of Outlook is not from the same version of
Office as the Word version, then you may experience errors when
attempting to merge to the body of an e-mail message.
Version
2.0 brought a complete redesign to the original add-in which has been
available for some time, with additional error checking.
Following a
suggestion from a user, from version 2.9, I have added the ability to
include the contents of a field from the data source in the subject
line. This field can be used alone or together with the contents of the
'Subject for the e-mail' text field, and can be placed before or after
the content of the text field. When this option is used the text and
field are separated by a single space.
The add-in
is provided in the form of a DOTM format template. With Word closed
extract the template to the Word startup folder. Then restart Word to
load it.
Unless you
have changed the location it will be
Windows
Vista/Windows 7 - C:\Users\[YOUR USER NAME]\AppData\Roaming\Microsoft\Word\STARTUP
Windows XP
- C:\Documents and Settings\[YOUR USER NAME]\Application
Data\Microsoft\Word\Startup
Alternatively the download includes a self-extracting
zip file which will install in the current Word startup folder (with the
option to install it elsewhere) and remove any earlier version that
happens to be present.
When installed
correctly it adds a button to the Mailings tab of the ribbon: |
|
|
|
|
Setup the
merge document, attach an Excel data source and click the button.
Provided the document is correctly configured you will see the following dialog
(first illustration) or one of a variety of error messages (listed at the end of this
page) which explain where you
have gone wrong. This opening dialog will be presented each time you use
the function, unless you check the box 'Do not show this dialog on
startup'. Thereupon the application will open with the page shown in the
second illustration below.
There are five pages to the userform:- |
|
 
  |
| |
The page
missing from the above illustrations is the Message page. The contents
of which will vary according to selected options. This will be discussed
in greater depth later.
When run, the macro checks the registry to
ascertain whether Outlook is configured to spell check messages before
sending. If this option is selected, the user is presented with a
warning message, and the option to merge to e-mail message is disabled.
Otherwise this would result in the user having to click a message box
for each merged message, which would soon become very tedious. Where the
macro cannot identify whether the spell check option is selected or not
(which one user reported as a problem) the message below right is
produced each time the add-in is run, whatever the setting in Outlook. |
|
|
| Note: |
The
option to check whether the spell check option is available uses a call
to the Shell32.dll object library. If you get an error message when you
begin, especially on Windows 64 bit operating systems, it is an
indication that Shell32.dll is not present or, more likely, not
registered. If that occurs, download the instructions for registering the
file. |
|
|
You can
disable the spell check in Outlook 2010 from Tools > Options >
Spelling, and in Outlook 2010 from File > Options > Mail |
|
|
| |
The first
dialog (after the disclaimer when presented) is to choose the type of merge document. You may select
common attachments to add to all messages, or you can add the attachment name(s) to a field in the data file and send individually targeted
attachments to the recipients. For creating pre-prepared individual personalised
attachments, you may also find the
Individual Merge Letters add-in useful.
If you choose the options to merge to PDF or Word
document format. The active
document itself becomes the attachment. You can add further attachments
if you require. |
| Note: |
Although the add-in is
intended to be used to merge as (or with) attachments, the attachments
themselves are not an essential part of the process. You can, if you
wish, merge a Word document to an e-mail message body using this add-in
without any additional attachments. Do however note that Word document
format and html-e-mail message formats are entirely different from one
another and merging a document to the body of an e-mail message may not
produce the results you expect. You can check the layout in Word's Web
view.
Note also that when merging to the body of an e-mail message, the
signature block is not automatically added to the messages. If you want
a signature block, add it to the merge document before running the
merge. |
|
|
|
|
The Message tab dialog is context sensitive. Further fields are
displayed dependent on the selection of Merge Destination.: |
|
 |
|
|
Merge to
Word Document and to PDF produce a similar range additional fields,
however the Word attachment has the additional option to save as word
97-2003 DOC format or Word 2007-2010 docx format. Some of the fields are
mandatory. When all the required fields are completed, the 'Continue'
button becomes available. |
|
Note: |
Where
records share the same data in the field used for naming the document
attachment, the first matching record is named using the field value.
With subsequent matching records, the record number is appended to the
name field value in brackets e.g. Filename(93).docx.
Where
the field used to name the document attachments is empty the document is
named 'No Name' with the record number in brackets e.g. No Name(27).pdf. |
|
|
From version 3.4 you may also include multiple e-mail addresses in the
e-mail field, provided the addresses in the data source are separated by semi-colons as
shown below. The BCC check box directs the e-mail addresses to the BCC
field, thereby hiding the other recipients from one another. The BCC
check box value is retained between merges until changed.
 |
|
 
|
|
|
| Additional
Attachments |
| |
In addition
to the option to send the merge document itself as an attachment, the
add-in also provides for the inclusion of additional attachments. These
can be attachments selected from field(s) in the data source
containing the filename(s) and path(s) of the
Complete
the Additional Attachments fields if required then click Continue |
|
|
| Invalid e-mail
addresses |
|
|
It is essential that the e-mail addresses in the data
source are both present and valid. The add-in will examine all the
e-mail addresses in the record set and if any addresses do not meet the
required standards for e-mail address formats, the process will be
stopped and you will see a warning message and the erroneous record
numbers and associated e-mail addresses are listed in a new document to
help you locate and correct them. Note that it is the record number
shown and not the row number in the Excel worksheet.
This
function caters for the vast majority of possible e-mail address
configurations, however should you discover a genuine e-mail address
reported as erroneous, or an erroneous address that is not flagged as
incorrect then please e-mail me with the details so that I may
modify the error trapping. |
|
  
|
|
|
When
'Continue' is clicked, the messages are sent to a temporary sub folder
of the Outlook drafts folder (to ensure a unique location). You will
have the choice of removing the temporary folder later when you send the
messages. The function looks for the presence of the folder and if it is
not present will create it. There is an additional button on the ribbon
that provides a function to remove the temporary folder should you
change your mind about retaining it. When removed the temporary folder
is removed completely. It is not merely moved to the Deleted items
folder.
The temporary folder allows the safe examination of the
messages and attached documents produced, before committing to sending
them. They will only be sent when the 'Send Messages' button is clicked.
If you have
Outlook configured to send messages immediately when connected (see
below), clicking the 'send messages' button, irretrievably sends the
messages to their destination e-mail addresses.
If you have the option
set not to send messages immediately when connected, then you have a
further opportunity to change your mind. However if you choose not to
send at the prompt, then you must use the Outlook Send and Receive
function to send on the messages. The button on the Word Mailings ribbon
only sends messages that are in the temporary folder. |
|
|
| |
At various
times you may see one of the following error and information messages: |
 
  
 
 
 
|
|
Note: |
1. The merge to e-mail
message option is dependent upon the option to send the current document
to an e-mail recipient being available in Word. If you find that this
option does not produce any messages in the Outlook outbox, then the
most probable reason is that this option is not available.
Its omission is caused
by a MAPI error. Repairing Office from the Windows Control Panel add or
remove programs applet should fix it. Ensuring Outlook is the default
e-mail application in Windows should also fix it, but doesn't always. This is
not the fault of the add-in, but of the host application. As a quick
check, add the Send To Mail Recipient command from the All Commands
section to the Quick Access Toolbar. If there is a problem, the command
will be grayed out. This issue does not affect the alternative merge
options provided.
2. When using the add-in
it is possible that you will encounter the error message shown in the
next illustration in
your document attachments.
This is another good
reason to heed the note in large red type at the end of this page.
The error occurs
because the merge fields in the document are replaced with docvariable
fields. However, because of the way mail merge ignores certain
characters that may be used in the data source field names, the values
that end up in the document variables may not match the variable names
in the docvariable fields.
There is no
documentation, that I have been able to find, that discusses how Word
mail merge handles field names, and, worse still, when there are several
similar field names, the mail merge renames the fields to avoid
conflicts. I have trapped all those unwanted characters that may
reasonably found in a field name, but I cannot do anything about your
field naming with similar field names.
If you see such an
error in any of your merge documents, check out the field names, before
reporting the issue to me. Toggle the display of fields to display the
errant field construction, so that you may cross reference it with the
corresponding field in the data source. The character that is different
in the Excel data source from the Word field is the one that needs to be
fixed to make it work correctly. Ensure especially that there are no
leading spaces in your column headings
If you are unable to
edit data source for whatever reason, and this issue is thus
insurmountable, let me know the character in question and I will add it
to the list of corrections. I do not anticipate that this will occur
very often. |
|
|
|
|
3. You may encounter
the following error message when you select Start Merge from the ribbon.
This message suggests a problem with the Excel data source. In
particular look out for empty and especially hidden columns. The add-in
addresses the Excel data directly. It is not a conventional mail merge.
Consequently if the macro cannot access the data it requires, the error
message is created. |
|
|
|
|
A typical
data file could be something like: |
|
 |
| Microsoft
Exchange e-mail accounts |
|
|
It has been brought to my
attention that with Microsoft Exchange e-mail accounts (see first
illustration below) the macro will produce an error when you attempt to
send the messages using the add-in. You should be able to overcome this
by setting the e-mail account name to your e-mail user name - see second
illustration below. |
|
 |
|
WARNING! |
If you click the Send button on the
ribbon, any messages in the application's temporary folder will be sent
to the recipients of those messages, so when testing, ensure that folder
is cleared before proceeding to use the application with live data. |
|
 - Now
download the add-in
|
