Email

[Hayley Baxter]

Hi All

I'm wondering if someone can help me with an email problem. I have managed to get my code to work basically it takes a standard piece of text in a word document and attaches this to the body of the email and send to the relevant people in the database, however now that this is all ok I just thought to myself that I do not want all the recipients to see who else I have sent the message to Is there any way I can get round this? Do I need to put them in the bcc will that work? Also this works on click of a cmdbutton how can I make it send on my selected date each month? I have not mastered this part yet.

This was my original post but I did not use the code in here I thought the sendobject worked best.

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=30353&highlight=monthly+email

Can anyone help?
Thanks
Hay

 

[rich.barry]

Use Blind Carbon Copy (Bcc) as you suggest to avoid people knowing the other recipients.

Use the section of code that Nero posted you to check for the mailing date

MyDate = format(Now,"dd/mm/yy")
MailDate = Left(MyDate,2)
If MailDate = "The date you want to send your email" Then YourMailCommandButton_Click

 

[Hayley Baxter]

My company did not want me to use bcc as the data is too sensitive. I would like to send out multiple emails in a way that it will run through all the contacts on the database then attach the standard word document to the email and send to everyone as an individual email by the click of a button.

I am half way there attching the word doc is not a problem, the problem is running it through all contacts and sending them an email each automatically.

It has been quite a while since I posted this now but I have been busy on other projects

Hope someone can help

Thanks
Hay

 

[Hayley Baxter]

Incase anyone is interested

On Local Error GoTo Some_Err

Dim MyDB As Database, RS As Recordset
Dim strBody As String, lngCount As Long, lngRSCount As Long

DoCmd.RunCommand acCmdSaveRecord
Set MyDB = DBEngine.Workspaces(0).Databases(0)

Me!txtprogress = Null

Set RS = MyDB.OpenRecordset _
("Email - Outstanding Promo")
lngRSCount = RS.RecordCount
If lngRSCount = 0 Then
MsgBox "No promo email messages to send.", vbInformation
Else
RS.MoveLast
RS.MoveFirst
Do Until RS.EOF
lngCount = lngCount + 1
lblstatus.Caption = "Writing Message " & CStr(lngCount) _
& " of " & CStr(lngRSCount) & "..."
strTo = RS!cEmailAddress
intMessageID = Year(Now) & Month(Now) & Day(Now) & Fix(Timer) & "_MabryMail"
' Send the email using some technique or other
RS.Edit
RS("cpeDateTimeEmailed") = Now()
RS.Update
RS.MoveNext
Loop

End If
RS.Close
MyDB.Close
Set RS = Nothing
Set MyDB = Nothing
Close

Me!txtprogress = "Sent " & CStr(lngRSCount) & " emails."
lblstatus.Caption = "Email disconnected"
MsgBox "Done sending Promo email. ", vbInformation, "Done"
lblstatus.Caption = "Idle..."
Exit Sub

Some_Err:
'MousePointer = 0
MsgBox "Error (" & CStr(Err.Number) & ") " & Err.Description, _
vbExclamation, "Error!"
lblstatus.Caption = "Email disconnected"


End Sub

Hay

 

[BukHix]

Sorry I missed this post the first time around but I thought I would post a simplified version of some email looping code just in case others may read this and want a simpler solution then the one you posted.

Dim rsEmail As DAO.Recordset
Dim strEmail As String
Set rsEmail = CurrentDb.OpenRecordset("YourQuery")

Do While Not rsEmail.EOF
strEmail = rsEmail.Fields("EmailAddress").Value
DoCmd.SendObject , , , strEmail, , , "Subject", "Message Text"

rsEmail.MoveNext

Loop
Set rsEmail = Nothing

Then make sure you change these to match the names in your table/query.

"YourQuery" = Table or Query Name.
"EmailAddress" = Field in Table or Query holding the email address.

This will loop through an entire table or query sending an email to everyone in it one at a time without stacking up a bunch of addys in the To: column.

 

[Hayley Baxter]

Still a slight problem

Whichever way I do this ie using method 1 above or Bukhix method it works fine up to a point. The only thing is that when the email actually sends it comes back undelivered because it is recognised as a weblink and not an email link ie http instead of mailto: I have come accross this before in this post

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=33648&highlight=email

The problem is how to incorporate both of these pieces of coding into one to make this work for me.

Can anyone help with this last problem?

Thanks
Hayley

 

[BukHix]

Hayley, what does your email address in the field look like? If you are using the name@domain.com format it should come up correctly.

 

[Hayley Baxter]

Hi Bukhix

It is set up on my form as email@emailaddress.com but when I choose to run the code via the cmd button it appears as email@emailaddress.com#mailto:email@emailaddress

Any ideas what I am doing wrong?

Thanks
Hayley

 

[BukHix]

Are you getting the addresses from a table or the form? Is it possible to strip the database of all important info and then zip it and send it to me.

 

[Hayley Baxter]

Thank You

Thanks alot...here it is

Ta
Hayley

 

[Hayley Baxter]

Sorry about that here it is now

 

[BukHix]

Change the Email Data Type in the tblCompanies to a text filed. If you need it to appear as a hyperlink in the form just fake it with formatting.

That should fix your problem completely. By the way once you do that you will see why your links appear they way they do. You may have to write a routine to strip the extra stuff that Access put in your fields.

 

[Hayley Baxter]

Thanks!!

Thanks alot it now works perfectly. I would not have believed that something as simple as that would have stopped it from working but there you go.

Have a great xmas
Hayley

 

[BukHix]

The reason it happens is because Access assumes all hyperlinks should be webpages.

 

[sambo]

Hayley..
I downloaded your db and I am curious as to how you made it so that the user is limited in design functionality.
For example..
The right click doesn't work in form view.
There are no Toolbars Available for the Application.
It is quite a bit harder to make changes.

I am very interested to find out how you set all of these options. I have just been rolling the dice with these options. If I user is smart enough, I suppose he/she would not have too hard of a time in completely mucking up my designs. I would like to limit the users' ability much like you have in your database.

Could you please elaborate?

 

[Hayley Baxter]

Hi Sambo

If you look at the tools menu then choose startup you will see that there are a number of tick boxes that are checked - uncheck them. The advanced feature on here will also remove the F11 key, leaving this checked allows the user to press F11 and still gain access to your database window. At the top form page you can choose your switchboard form to open automatically upon startup meaning the users will not be able to access the design at all (unless they know about the shift bypass key) but there are ways to disable this too to lock them out altogether.

You can create your own custom menus only to give the users permission to what you want them to have - tools, customise.

The scrollbars are disabled through the form properties and choosing None.

Hope this helps
Hay

 

[sambo]

That sounds great.
How do I assure that I (admin) have access to all options that I need? Is there a "hot key" that will turn everything back on as it should be?

 

[Hayley Baxter]

Try this sample

Try this sample that I posted. It will allow you to disable the shift key then re enable it. All you do is type in the path name of the database.

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=32506&highlight=shift+key

HTH
Hayley

 

[Hayley Baxter]

More Questions....

Firstly thank you bukhix for getting me this far. The email is working great. However I would like some more options if possible. Ideally what 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.

Here is what I am looking for - 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)

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?

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.

Appreciate any thoughts or suggestions.

Thanks and happy new year everyone!!
Hay

 

[Oldsoftboss]

Jes Haley, 260 odd views, and how many replies, you've created a monster
Dave