Excel as a data source for Outlook to email Word docs

  • Thread starter Thread starter Monomeeth
  • Start date Start date
M

Monomeeth

Hello

Each fortnight I am given a report (in Excel) containing the details of all
NEW team leaders and managers for our organisation. This report contains,
amongst other things, the User ID and First Name of each person listed. I
also have a standard letter (in Word) which needs to be emailed (via Outlook)
to each of these people.

WHAT I NEED
In summary, I need a macro which will do the following:

1. Grab the first name of the person from the Excel spreadsheet and place it
in the salutation of the letter in Word (i.e. Dear "first name")
2. Send this letter as an attachment in an email via Outlook, which in turn
uses the User ID from the Excel spreadsheet as the email address.

My preference would be to have a separate email for each person listed in
the spreadsheet, and for the macro to process each name one by one until it
hits a blank cell.

If at all possible, I would also like to have the person's first name used
in the actual email text.

As such, I imagine the macro (or macros) would have to save the Word
document with some sort of unique name (perhaps using the User ID in the
filename) in a particular location before then using Outlook to open an
existing email template, inserting the first name in the email text,
attaching the appropriate letter and then sending it off to the correct
recipient.

Basically, we are trying to create a personalised experience for these people.

I have cross-posted this to a group in each of the three applications
(Excel, Word and Outlook) as I wasn't sure where to start?

Any help would be greatly appreciated. I really hope this is possible...

Joe.
 
Hi Doug,

Thanks very much for your help - I really do appreciate it.

I have followed your link and have read the instructions a number of times.
I'm not sure I have correctly created the Directory type mail merge main
document, as I haven't done this before, so I'm a little uncertain.

Nevertheless, I have created the macro in Word using your code. I've
attached the macro to the actual mailmerge document. However, when I try to
run it I get a Microsoft Visual Basic Compile error: "User-defined type not
defined".

When I click OK it highlights the following code:

oOutlookApp As Outlook.Application

This appears in row 6 of the macro code, after "Dim ".

Are you able to suggest where I may be going wrong?

I've definitely checked the Microsoft Outlook 11.0 Object Library checkbox
under the TOOLS > REFERENCES menu option in the Visual Basic Editor.

Hope this makes sense.

Thanks for your help!

Joe.
 
The problem is arising because the reference to the Outlook Object Library
is not being retained. I would suggest that you place the macro in the
Normal.Dot template or in another template that you save in the Word\Startup
folder so that it is available to any document that you create.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP
 
Back
Top