Email

[Hayley Baxter]

Hello Dave

Haven't I just so what's the answer to this monster then?? Oh yeah meant to tell you I did have a look at your database (at long last) very impressive.

Hope you had a good xmas and new year
Hay

 

[BukHix]

Ok I am not sure I understand this so please clarify I few things for me.

I would like to have is a wider choice for the users 1)Send emails to everyone on database - this part is ok now 2) to send to a selection of people ie the no replies.

How do you plan on selecting the people. Checkboxes, maybe?

send emails on a monthly basis to all contacts on database with the exception of contacts in Location A (People in Location A should not be emailed)

This could be handled with a simple If Then Statement (I think). Is the contacts data relatively static?

I would then like to track the "no replies" people who have not given any feedback should be emailed a reminder so next time round how would I only send to these people? Is there anyway of automatically tracking when an email is received? or would I need to set up a multiselect list and select the people who have not responded?

Yes there probably is a way but I have never used OutLook and Access together in that way. I think I have read about others who have so maybe one of them will see this thread and elaborate. Refresh my memory, are you using this database on the web? If so you could achive the results you are looking for with a little ASP.

If I need to use a multiselect list I'm sure I can work my way round this but if there is a more clever way to know via outlook that someone has replied and for this to update the database that would be super.

Anyone?

 

[Hayley Baxter]

I was hoping that I would be able to do the following:

Emails sent to : Contact1, contact2, contact3 etc (All on database) Ideally it is not essential to email people in LocationA but if they are included it doesn't really matter.

Outlook: contact1 replies
Access: Replied Field updates from false to True (To somehow set a field in access to true when the email comes in) if it doesn't come through the field would stay at false but I am not sure if this is even possible.

I was then hoping to write something in VBA to say

EmailReminder (deadline date is 25th of every month) so when the 25th arrives

if field = false then loop through the database and send a reminder email to all of the contacts who have not responded so then no checkboxes would be required.

I am eventually going to make one of the current forms in the database a web page for suppliers to access but they will not have access to the form I am currently working on with the email issue so that would be a no.

I will do some reading and see what I can find and If I do get anything to work I will post up an example. In anyone does know anymore about whether this is possible or not or how I may achieve what I am after I would love to hear from you.

Thanks
Hayley

 

[Hayley Baxter]

I'm back!!

Yes I'm back again. I've just been trying out different things with this and I notice that when I put people in the cc section of the email then every contact is going to get this person cc'd on their copy of the email. I cannot allow that to happen basically if Contact1 has requested that a colleague must be ccd then I only want to cc this person for contact1 not for all of them - This just gets more and more complicated by the minute.

Don't suppose anyone knows how I can do this?

Just an update as well I have found some interesting articles on this topic - understanding them will be the next thing!! This has been fun and games I can tell you.

 

[joebater]

Not wishing to sound too stupid but...

What happens when the legitimate recipient of your mail simply forwards it on (aren't the eventual recipients going to eventually be able to follow the path? or is it that once it's left your domain...). Ultimately would the stringency of your security be in vain?

I don't know about automatically logging responses - one method I've done is to create a record in a tblResponse table for each email as it is sent. Then prompts can be sent to people whose record is found to be empty after the appropriate time...

 

[Hayley Baxter]

No they will not be able to follow the path. Just incase you did download the sample db I posted. The coding in here is slightly different to the coding I am actually using. In the posted example the email message takes the form of mailto: contact1. contact2, contact3 all on the same email message meaning yes they could see everyone who is being sent the email so apologies if this has been misleading. In reality the email I am sending takes the form of mailto: contact1 - new email for contact2 so they only see their own email address on the email and not the others who have been sent the same message. I end up with however many contacts are on the database say 200 = 200 seperate emails in my sent items rather than 1 email with 200 names on it.

Yes I could look at that method of setting up a table for responses as you have done - that will be my second choice. I am going to have a go at making it work via outlook. It is just very time consuming. What I may do is set up a temp fix and work with the test db. If I can come up with anything I can just transfer it over.

Thanks for the reply
Hayley

 

[joebater]

Hi Hayley

I did indeed download your sample, like Sambo I am extremely interested to learn about the user limitations that you've put into your design.

Can I try your patience by asking about "the tools menu"? I don't see what you're referring to and am hoping that in asking I'll only be a fool for five minutes.

Many thanks for the interesting ideas - oh and happy new year.

blue skies
j.

 

[Hayley Baxter]

Hi Joe

You will not see the tools menu as it has been restricted. I didn't disable the shift key on this sample so hold down the shift key upon opening the database and you should then see the tools menu.

Hope this helps
Happy new year to you too
Hay

 

[sambo]

Just a note,
The shift key should actually be held down as the application is loading. I was originally under the impression that it should be hold down only as you click on the Icon. This is not completely true.
Try holding down the shift key just after you double click on the icon, and then continue to hold as the Database Window Loads.

 

[Hayley Baxter]

Just wanted to say a big thanks to everyone who helped me out with this post and took an interest especially Bukhix - thanks alot you've all been great.

Here's what I ended up doing:

I created a cmd button to send to all contacts on the database but rather than use the table as the record source I used a query with the criteria set to <> Location A.

As the data type of the email field could not be a hyperlink and had to be text instead this then done away with sending an individual email to any one contact. To overcome this problem I added another command button that opens outlook taking the email address of the current record.

As for the no replies ie to track the people who have not responded I didn't quite figure out how to get outlook to update access for me (I think it may be possible but there are limitations) I think I'll leave this part to a later date anyway just so I had something in place I created a query and set the criteria for the field that I am tracking to ISNull. I then have another cmdbutton to send a reminder to the No Replies that runs this query.

It works very well but I'm still interested in knowing how outlook can update access when the email comes through.

Thanks once again
Hay

 

[Hayley Baxter]

The long winded thread continues...This has worked very well for me over the past few months but I would like to update and make the email more personal. Rather than "Good Morning", "Good Afternoon" which is what I've had to work with so that everyone can be addressed while looping through the email (without them knowing who else has received it) I would like to take the name of the person in the contact list ie I have a field called Contact_Name so the email would look at each name in turn and say Hello Joe then message, move onto second record Hello Lisa.

I would also like the name and position, department of whichever employee sends the email request to appear as a signature at the bottom of the email as apposed to my own if at all possible. I am assuming these details would need to be stored in a table. Wondering if anyone else has done this before and can maybe offer me some advice.

Many thanks for all your help so far
Hay

 

[Fizzio]

Hi Hay!

I theory, you just need to add the relevant fields to your initial query as demonstrated in BukHix's first code. I'm assuming that you already have the data stored somewhere within the Db, just may mean adding tables to your first query. You then only need to create a dynamic message text or subject line using the recordset example BukHix has given.
(I haven't downloaded the sample so forgive me if I'm way off base here!)

 

[Hayley Baxter]

Hi Fiz

I have a query called QrySendEmails and this query takes the company name, contact name and email address.

Here is the code I am using:

Private Sub CmdEmail_Click()
Dim rsEmail As DAO.Recordset
Dim strEmail As String
Set rsEmail = CurrentDb.OpenRecordset("QrySendEmails")

Do While Not rsEmail.EOF
strEmail = rsEmail.Fields("Email").Value
DoCmd.SendObject , , , strEmail, , , _
"Subject", _
"Good Afternoon" & _
vbCrLf & vbCrLf & "Message" & _
vbCrLf & vbCrLf & "EmployeeName" & _
vbCrLf & "Dept" & _
vbCrLf & "Address" & _
vbCrLf & "Tel:" & _
vbCrLf & "Fax:False

rsEmail.MoveNext

Loop
Set rsEmail = Nothing

MsgBox "Emails have been sent"
End Sub

So all I really need to do now is amend the above code to take the contact_name for record1, record2etc rather than every mail saying "Good Afternoon" that's the first problem.

Second one is that I would like the signature to change depending on who's logged in - The person who actually clicks the send button (Put their details at the bottom of the email)

Thanks
Hay

 

[Fizzio]

Just include the contact_name in the qrySendEmails then update the code to

....
strEmail = rsEmail.Fields("Email").Value
DoCmd.SendObject , , , strEmail, , , _
"Subject", _
"Hi " & rsEmail.Fields("contact_name") & _

etc....

To use the sig of person logged on it depends how you log on the person. If, like I, you have a login form that is just hidden after a successful login, then you can use the DLookup function to get the full name. Get back how you store / keep the current login details.

 

[Hayley Baxter]

Thanks once again Fiz, good job you came back. How do you expect to get rid of me when you keep helping?

 

[Fizzio]

bugger

 

[Hayley Baxter]

"We shall not be moved"

 

[Hayley Baxter]

The code has started playing up recently. It runs ok without any error messages etc however it appears to miss out records ie if there are 50 contacts to email. There may only be 40 sent where before all 50 would be sent. No idea why this is happening.

Buck, if you're out there. I'd be grateful if I could trouble you once more - Thanks a lot

 

[BukHix]

Which Version of Access are you using? I believe there is a bug in A2K that caused the email thing to misfire quite often. Believe it or not I am still using Access 97 (although we are almost weaned off Access completely now) and have no problem sending more then 50 emails at a time.

Does it always miss the same emails? Or is there any other recognizable pattern to the problems you are having.

 

[Hayley Baxter]

I am using A2K. The problem has only started occuring and I am not yet aware if it's the same emails that are being missed out. I'll need to keep an eye on that next time they go out. Is there no solution for the problem that you know of then? There are also occasions when it will get half way through the recordset and just stop.

Thanks again for your time
Hay