Making an automatic mailing & mail tracking system

[Saschah]

Hey Guys,

I'm pretty new to Acces, I know the basics and have done some VBA stuff but I'm not getting pretty far with this...

So, what am I trying to do:

I want to have the possibility to send different pre-made mails to one of my clients just by pushing a button.
This should take the mail out of a form (or table) which is kind of the clients information form.
I want to be able to edit the mail to make the standard mail more personal.

Also, and I think this is the tricky part...
I want to be able to see in a form or table, which client has had which mail...
So we can track whether the client is being informed enough...

We need to send standard mails to every client almost every week but they need to be personal and they will all be send on a different moment during the week. And possibly even by different people.

Can anyone help me?

Thanks alot!!!

[CJ_London]

there are plenty of threads on this and other forums about sending emails - so I'll concentrate on the personalisation.

There are two basic methods 'build' and 'template'

Presupposing you have in your client table a field called 'salutation' (might be populated with Joe or Mr Smith)

In your email table, lets suppose you have a message

"We are pleased to report that nothing has happened in the last week"


To build you would fetch both records and concatenate a string, something like

msg="Dear " & Salutation & "," & vbcrlf & vbcrlf & emailmessage

or for a template, your email table would contain

"Dear [Salutation],

We are pleased to report that nothing has happened in the last week"

and as before, fetch both records and replace [Salutation] in the emailmessage

msg=replace(emailmessage,"[Salutation]",salutation)

with regards track who has had what email, you need a tracking table with something like

ClientID
MessageID
DateSent

and as part of the code sending the email, include code to insert a record into this table.

If you are going to do this - 'I want to be able to edit the mail to make the standard mail more personal.' - you may also want to store the full email text as well.

[Minty]

In addition to the information above have read here - http://www.granite.ab.ca/access/email.htm

[Saschah]

Thanks for your response!!

I'm going to try these things out during the weekend.
But, the tracking table your are suggesting, is it automated or is this impossible?
Maybe I can make a tracking table with checkboxes?
And when I check a box it sends the email?

What do you think?

Thanks!

there are plenty of threads on this and other forums about sending emails - so I'll concentrate on the personalisation.

There are two basic methods 'build' and 'template'

Presupposing you have in your client table a field called 'salutation' (might be populated with Joe or Mr Smith)

In your email table, lets suppose you have a message

"We are pleased to report that nothing has happened in the last week"


To build you would fetch both records and concatenate a string, something like

msg="Dear " & Salutation & "," & vbcrlf & vbcrlf & emailmessage

or for a template, your email table would contain

"Dear [Salutation],

We are pleased to report that nothing has happened in the last week"

and as before, fetch both records and replace [Salutation] in the emailmessage

msg=replace(emailmessage,"[Salutation]",salutation)

with regards track who has had what email, you need a tracking table with something like

ClientID
MessageID
DateSent

and as part of the code sending the email, include code to insert a record into this table.

If you are going to do this - 'I want to be able to edit the mail to make the standard mail more personal.' - you may also want to store the full email text as well.

[CJ_London]

tracking table would be automated - it is tracking what you have sent, not what you need to send