Email tracking question

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

Guest

I have a Contact database that includes a Contact table and an Email table.

The Email Table includes the email name, date and description, and it is
related to the contacts table via the contact ID field.

I have a contact Form that includes an email subform. The purpose is to
track who has been sent what specific emails. I would like to input multiple
records at one time.

How can I take a selection of records (contacts) and add the appropriate
email name to the Email table.

Because these are two separate tables I am having trouble.

Thanks in advance

Steve
 
Sure, build a recordset and use the add method to append a record to the
second table. Open a code window and look in the help files. There should be
an example.
 
Tom, Thanks for the response

Contact Table
Contact ID (Key, Automumber)
First Name
Last Name
Company Name
Email Address
etc


Email Information Table
Contact ID (long integer Related to the Contact table)
Email ID (key< Autonumber)
Email Name
Email Date
Email Description

Right now the contact table is populated. The email info table is not. I
can update the records using a form and subform and it works fine. I would
like to update multiple records at one time. I tried to do a query and use
both tables in the query. When I try to include the email Information table
in the query I get no records returned.

Thanks
 
Sorry Tom,

Contact Table
Contact ID (Key, Automumber)
First Name- "John"
Last Name- "Smith"
Company Name "ABC Corporation"
Email Address "(e-mail address removed)
etc


Email Information Table
Contact ID (long integer Related to the Contact table)
Email ID (key< Autonumber)
Email Name "BASIC"
Email Date- "8-19-07"
Email Description "First Silestone Email, basic Information"

Hope that helps.

Thanks
 
Hi Steve,

I'm still confused as to what exactly you want to update. Your original post
included the following:
I would like to input multiple records at one time.
Click to expand...
How can I take a selection of records (contacts) and add the appropriate
email name to the Email table.
Click to expand...

In the sample data that you have just provided, the email name, ie. "BASIC",
is not a part of the sample Contact record.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
If I send the basic email to everyone on my list that has as email address I
can manually go thru and update each contact. I would like to update all at
once.

Thru the Contact Form which has a subform linked to the Email Information
table I can update one at a time.

Hope that helps Sorry I'm not too good at describing the situation.
 
Hi Steve,

For robust e-mail tracking, you might be best off using either Total Access
E-mailer from FMS (http://www.fmsinc.com/products/Emailer/index.asp) or
Fairlogic WorldCast (http://www.fairlogic.com/worldcast/). Those are two that
I am aware of--there may be other software products as well. Both of these
products allow one to send HTML formatted messages, and include a transaction
log so that you can see which messages were not delivered correctly. I'm
fairly certain that both products send individual e-mail messages out to each
recipient.

I put together an Access only sample that might serve your needs okay, but
it is certainly not as robust as the above two solutions. You can download a
zipped copy here:

http://home.comcast.net/~tutorme2/samples/ContactEmailDemo.zip

It sends a text only message using the DoCmd.SendObject method, with a MAPI
compliant e-mail program, such as Outlook or Outlook Express. The e-mail
addresses of the people that you are sending to are entered on the Bcc (blind
copy) line. You will need to either hard-code a valid "To:" address, or add a
text box control, so that this can be entered by the user. However, a couple
of caveats:

1.) A new e-mail message opens in edit mode. If the user clicks on the "X"
in the upper right corner, to close the message without sending it, I don't
see any way offhand of stopping the update statement that updates the EMail
Information table, at least when I tested using Outlook Express. (I did not
test this code using Outlook).

2.) If one or more messages failed to deliver, due to bad e-mail addresses,
the code will still add a record for this person to the EMail Information
table.

3.) This should work "okay" (but certainly not great), as long as you do not
exceed whatever number of e-mail addresses that your ISP might flag as spam.
For some ISP's this number of e-mail addresses is in the 40~50 range. An
workaround would be to send in batches of 40 or 50 until the entire recordset
was processed, or you could perhaps fire off individual messages to each
recipient (in which case you would not use the current hard-coded "To" e-mail
address).

4.) Currently, the only criteria is Email address is not null. One could,
with some extra coding, create a multiselect list box on the form, to allow a
user to pick who they wanted to send the message to.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
 
Tom,

Thanks for all the work you put into that. I may be making this more
complicated than it needs to be.

I have about 400 people that I email product updates, announcements etc to
on a regular basis. I have a database of about 1600 prospects/contacts that
contain the 400 email people. I need to keep track of who has received what
emails. I sort the list based on the email I plan to send for instance I am
sending a new product announcement
I choose the list of people to send to, I export that list to outlook (to a
temporary folder) and send the email. (I use outlook because it works
directly with publisher) I would like to somehow add a record to each
contact with the email name, date sent, and description. Currently I am
using a child table to hold the email information but like I have mentioned
I am having difficulty updating multiple records at the same time. Because
the child table is related to the parent table I have only been able to add
records one at a time.

At this point I dont need to send the email thru access just track that it
was sent. Bounce backs are minimal because the people I am sending to are
familiar and the data is pretty clean. I am simply trying to avoid sending
the same email to to the same person twice.

Any advice on a way to accomplish this or a better way of implementing would
be appreciated. Thanks again for all your efforts to this point

Steve
 
Hi Steve,
I need to keep track of who has received what emails.
Click to expand...

I think your best bet will be to go with either Total Access E-mailer or the
Worldcast offering. There's a reason that a market exists for these products.
The "roll-your-own" code method in Access works just "okay". By the way, I
tested the code I posted yesterday at work, where we use Outlook 2003 (not
Outlook Express). It was better behaved for the full Outlook, which produces
a trappable error if one closes the message without sending it. In that case,
the code that updated your EMail Information table was not executed.
I am simply trying to avoid sending the same email to to the same person twice.
Click to expand...

This capability is built into the products I mentioned. In addition, I
believe both of them allow you to incorporate a file attachment (ie. your
Publisher file).

You could go with Outlook code similar to what I show on page 17 of this
document:

http://home.comcast.net/~tutorme2/samples/accesslinks.zip

However, I think you will either need to send each message individually, or
use the BCC line with batches of, say, 40-50 e-mail addresses at a time
(check the limits imposed by your ISP).


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
Back
Top