Create Group Email in Access with automatic link to Outlook

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

Can anyone help

I would like to create three e-mail distribution lists using data stored i
the following fields

Table: Client
Field: 5500 Contact - Data Type: Tex
Field: Info Contact - Data Type: Tex
Field: Other Contact - Data Type: Tex

Distribution List #1 will take all email addresses from field 5500 Contac
Distribution List #2 will take all email addresses from field Info Contac
Distribution List #3 will take all email addresses from field Other Contac

I have already created very simple three queries for the above Fields

5500 Contact Group Lis
Info Contact Group Lis
Other Contact Group Lis

What I have only is a form built based on the above queries with a text box
The box is "onclick" activated to open Outlook, but it only opens curren
single email and not group of emails selected through the query...I hop
this is clear enough
I need the "onclick" option that opens the entire group of email addresse
in a single email in Outlook

I am not really familiar with VB. Can anyone guide me step by step

BIG thanks!!
 
Hello Natalia,

With one of your queries forming the record source of your form, you can use
the following VBA code in the Click Event of a Command Button. The code
will loop through the recordset and add each email address to the Recipients
collection of your email item.

Dim oApp As Outlook.Application
Dim objNewMail As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone
Set oApp = New Outlook.Application

Set objNewMail = oApp.CreateItem(olMailItem)
With objNewMail
rs.MoveFirst
Do While Not rs.EOF
If Len(Trim(rs!Email)) > 0 Then
Set objOutlookRecip = .Recipients.Add(rs!Email)
objOutlookRecip.Type = olTo
End If
rs.MoveNext
Loop
.Subject = "Test subject"
.Body = "Your text message here."
.Save
.Send
End With

Do be sure that you have a Reference set to the Microsoft DAO Object Library
that is appropriate to the version of Access that you are using.

hth,
 
Cheryl,

Thank you very much for your fast response!
However I could not make it to work.
First I entered your codes into the Form that I created based on my query: I chose the Code Builder from the "OnClick" of the Event section and paste into the VB....which may not be correct at all..as I have not much idea of what I am doing....
Now if I click on the text box I am getting an compile error message and it points to the line
Dim oApp As Outlook.Application, the first line of coding. I am not sure what is wrong.

By the way I am using Microsoft Outlook 2000 and Access 2000 Version.

I have also changed the settings in the reference in the VB, by unclicking the ActiveX Data Object and checked on DAO 3.6 Object Libary.

Can you help me? Any help is gratefully appreciated.

Thank you very much.

Natalia
 
Natalia,

We should be able to get this working just fine for you. However, let me
ask a question or two of you:

1. In your initial post, you say you would "like to create three email
distribution lists using data stored in the following fields":

Table: Clients
Field: 5500 Contact - Data Type: Text
Field: Info Contact - Data Type: Text
Field: Other Contact - Data Type: Text

I interpret this to mean that you have three different fields in a Client
table record, each of which is named for a specific distribution list. Is
this correct?

In each Client record, are you then entering a single email address in just
one of these fields?

With regard to your form: Are you using the Clients table as the record
source for the form, or is the form simply used to select a specific query?

Let me know and we will proceed from there.



--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Natalia said:
Cheryl,

Thank you very much for your fast response!
However I could not make it to work.
First I entered your codes into the Form that I created based on my query:
I chose the Code Builder from the "OnClick" of the Event section and paste
into the VB....which may not be correct at all..as I have not much idea of
what I am doing....
Now if I click on the text box I am getting an compile error message and it points to the line
Dim oApp As Outlook.Application, the first line of coding. I am not sure what is wrong.

By the way I am using Microsoft Outlook 2000 and Access 2000 Version.

I have also changed the settings in the reference in the VB, by unclicking
the ActiveX Data Object and checked on DAO 3.6 Object Libary.
 
Cheryl

Thank you for your help.

First I wanted to tell you that I made your codes to work. But after I did some redesign on my form, I was getting a message regarding “Module not Found, which corrupted my data somehow that even when I was trying to create a new Form I could not save it

So I think I have to start all over again. First, let me answer your questions

I have three different fields for specific distribution list: 5500 Contact, Info Contact and Other Contac. Your understanding is correct

You can enter email addresses in all fields for one client. The names can repeat. The same person can be a 5500 Contact and Info Contact, etc

The way I built my Form was based on the query. So I think that the record source is the query, but the query was built on the Client Table where I have those fields. I used the Wizard when creating my Form and selected for example for the 5500 Contact Form, the 5500 Contact Group List query.
However, I am not sure even if I need a query, as my query really duplicates the specific Field in the Client Table as it brings all the addresses stored and not the particular one.
So to answer your question, I use the form to select a specific query

I hope that I did not discourage you by the above

Any help is a GREAT help.

Again, BIG thanks

Natali
 
Cheryl

I would like to add also that in your formula, when it worked I added rem before .Save, so email went to the Draft Folder in the Outlook so I could edit the subject and enter the specific message...and this is what I needed

So the question is whether I can create a command button (and not use the text box), which by click will execute the following: selects the email addresses I need (from query or from the specific field in the Client Table) and, 2. will open Outlook and places an email in the Draft Folder with all targeted email addresses in the row To:

Thank you.

Natalia
 
Cheryl.
...another error messge I am getting with the "Module not Found" is "You canceled the previous operation"....not sure how to cure this..as I was trying to use your codes in the new Form that I built based on the Client Table, 5500 Contact field

Natalia
 
In order to save the email to the Drafts folder and open it so that you can
do further editing, put a comment on the .Send line but un-comment the .Save
line.

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Natalia said:
Cheryl,

I would like to add also that in your formula, when it worked I added rem
before .Save, so email went to the Draft Folder in the Outlook so I could
edit the subject and enter the specific message...and this is what I needed.
So the question is whether I can create a command button (and not use the
text box), which by click will execute the following: selects the email
addresses I need (from query or from the specific field in the Client Table)
and, 2. will open Outlook and places an email in the Draft Folder with all
targeted email addresses in the row To: ?
 
Please describe the form you are now using (either a new form or the one
which you redesigned).

1. Are you using one of your queries as the record source for the form?

2. Or, are you using a form which does not have a record source bound to
one of your queries but *is* used simply to select one of your queries?

3. Please post the code you used when you received the "Module not found"
error and advise on which line of code the error occurred.



--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Natalia said:
Cheryl,

Thank you for your help.

First I wanted to tell you that I made your codes to work. But after I did
some redesign on my form, I was getting a message regarding "Module not
Found, which corrupted my data somehow that even when I was trying to create
a new Form I could not save it.
So I think I have to start all over again. First, let me answer your questions.

I have three different fields for specific distribution list: 5500
Contact, Info Contact and Other Contac. Your understanding is correct.
You can enter email addresses in all fields for one client. The names can
repeat. The same person can be a 5500 Contact and Info Contact, etc.
The way I built my Form was based on the query. So I think that the record
source is the query, but the query was built on the Client Table where I
have those fields. I used the Wizard when creating my Form and selected for
example for the 5500 Contact Form, the 5500 Contact Group List query.
However, I am not sure even if I need a query, as my query really
duplicates the specific Field in the Client Table as it brings all the
addresses stored and not the particular one.
 
See my response to your first posting dated 2/6/2004

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Natalia said:
Cheryl..
..another error messge I am getting with the "Module not Found" is "You
canceled the previous operation"....not sure how to cure this..as I was
trying to use your codes in the new Form that I built based on the Client
Table, 5500 Contact field.
 
Cheryl

I would like to thank you again for your help. You are unbelivable!!! The issue you have advised me on is fixed and I am moving forward with my database project.

Regards

Natalia
 
Back
Top