e-mail problem from newby

[fvd]

Hello,
Please excuse me if my question already exists is this forum...
i'm not familiar with VBA (unfortunately) so i've made my entire database with macro's and queries instead of VBA-code.
But now i have a problem that cannot be solved with a macro, so i need some VBA-code that i can use.
I would like to send an e-mail to some persons (these persons' e-mailadresses are in a table that i generate with a query).
So all the persons in this tabel need to get an e-mail, this e-mailmessage needs a standard subject and a message.
And in addition i would like to have a familiar VBA-code (also for persons from a specific table), but now the message has got to be some data that depends from person to person.
For example: a specific result for Susan is 1.25 and for John the result is 2.75, so the e-mail for susan has to got a different message than the one for John...
Although that is would be ok if these different results are in a report that i can send as an attachment.

So, i did not find a code in this forum that i can use since i'm not capable of adjusting the code for my use...
I would be very happy with some code that would solve my problem and also with a little explanation on how to use this in my database! (i thought of using a button that would send the e-mails).

Thank you very much already!!!
Kind regards,
Filip

 

[TomAllins]

possible solutions

There are two possible solutions that I can think of:

1/ send the mail through outlook (I have no experience with that)
2/ use access itself to send the mails (solution below)

This code is actually a modified macro-command 'sendobject'. I saved it to a module and edited the code afterwards.

Public Function SendMails()

'variables
Dim dbs As Database
Dim rst As Recordset
Dim i As Long

'connect to database
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("ENTER SQL CODE HERE")

'loop through list
rst.MoveFirst
For i = 0 To rst.RecordCount - 1
DoCmd.SendObject acTable, "emptytable", "MicrosoftExcelBiff8(*.xls)", rst!Email, , , "enter subject here", "your result is " & rst!result
rst.MoveNext
Next

'end connection to database
Set dbs = Nothing
Set rst = Nothing

End Function

Since you are unfameliar with VBA i'll give you a step by step runthrough.

'connect to database
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("ENTER SQL CODE HERE")

Instead of using your saved query, I suggest you use the sql-code to create a temporary recordset.

rst.MoveFirst
For i = 0 To rst.RecordCount - 1
DoCmd.SendObject acTable, "emptytable", "MicrosoftExcelBiff8(*.xls)", rst!Email, , , "enter subject here", "your result is " & rst!result
rst.MoveNext
Next

Here I run through each record of the recordset and send a mail with an empty table. This table is necessary because access won't send mails without an attachment. I use the fields email from your query/recordset to know the email-address and the field result to display the result.

FINAL REMARK
Microsoft has increased the security settings on outlook. This means that each time you send a mail a popup will appear, forcing you to confirm that the mail send by access is legimate.

This can be annoying, certainly if you need to send a lot of mails. I know their is a way to block this popup, but I don't know how. If you need more info on this subject, check the msdn website or this forum.

PS
This code was not tested!! (sorry but not enough time for that)

PSS
Code is based designed on Access 2003 (although it should work on lesser versions too)