How to automate sending email messages via Outlook 2003 1

[phoenix26]

What I need to do is send about 10-15 emails a day to a list of customers and because of company policy, I am restricted to only using Outlook 2003. They refuse to give me any access to a webserver, because everything at work has to go through their proxy and so that is why I can only use Outlook 2003.

The message must be a customized message with customer information and serial numbers..etc. I have the data in an Excel spreadsheet and also in an Access database table.

What I would like to know is how can I "automate" the message sending, taking data from the Excel or Access, populating Outlook, and then sendint it out.

Is it possible to do this with VBA? Does Excel/Access have some VBA connection that will allow for the information transfer?

Would someone please provide me with some help/suggestions on what I can do to accomplish my task.

Thank you so much.

 

[PHV]

In Access VBA you may consider the DoCmd.SendObject method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xlbo]

You may also consider looking at the extensive FAQs section in which there are several examples of this

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[firefytr]

You can use SendMail in Excel as well.

I generally tend to go as far as to create my own Outlook object as I do not like the SendMail feature or addin. If you do this multiple times per day, depending on what your criteria is, you could set this up to a toolbar/menu so you can perform this at the click of a button.

Maybe you can answer a few questions first:
What exactly are you wanting to send from Excel?
A workbook? Worksheet? Specific range of a worksheet?
Same file everytime?
Always located in the same place?
Do you need to "shop" (look) for the file through an Open-type dialog box? Do you always send to the same people?
Is the message body always the same? Subject?

-----------
Regards,
Zack Barresse

 

[phoenix26]

Thank you firefytr.

To answer your questions:
What exactly are you wanting to send from Excel?
> I want to send a dynamic letter composed of fields in Excel. For example, one of the column will be email address, another column will be a BODY, where the BODY will be a series of concatenation from other columns in the same row.

A workbook? Worksheet? Specific range of a worksheet?
> From the 3, I would say the closest would be a specific range on the worksheet. The worksheet has about 10 rows.

Same file everytime?
> No file.

Always located in the same place?
> Yes. Always in the same place.

Do you need to "shop" (look) for the file through an Open-type dialog box? Do you always send to the same people?
> No. All the data is on the worksheet.

Is the message body always the same? Subject?
> No. The message body will vary depending on the columns of data.

Here is an example:

COL_1 | COL_2 | COL3 | COL4...

COL_1 is the email address
COL_2 is the customer name
COL_3 is the date
COL_4 is the BODY which will be created by other columns in the row....(Dear XYZ How are you doing? Have you received serial number XYZ yet?)

I was playing around with a sample code I found from another thread:
thread707-1124624

But when I tried to compile the code in Excel 2003, I get an error saying

"Compile error:
Invalid outside procedure"

========

Does what I said make any sense?? I'm pretty new to VBA and Excel so any help you can offer me will greatly be appreciated. Thank you so much.

 

[PHV]

Invalid outside procedure
Seems you've code outside a Sub ... End Sub block.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[phoenix26]

Cool. I added the code to a sub/end sub section and now can call it sucessfully through a command button click procedure.

Can you provide me a sample code for sending the Excel field information to an Outlook email message?

 

[phoenix26]

Nevermind. I found it!!

 

[firefytr]

Wow, miss a day, miss the world. LOL! Let us know if you need anymore help with this phoenix26.

-----------
Regards,
Zack Barresse