Record Selection, email and then table updating

[ballj_35]

I would like some guidance and help and how to put this together. My situation is as follows:

1. I have some customers in an Access 2010 db/tables. Here is what I would like to do in this order, but do not know where and how to do it.

2. Select records from the table based on data range and other criteria. I know how to build the select and subselect to filter the information I want.

3. Then for each record that the select statement has pulled, then I wand to send an email to the person. Will get some information from another table. I.e. subject and body of the message is in a table.



4. Once messages have been sent, then come back and update the data fields on each record to record the last sent date of a message, which would be the current date.

I do not know where to put these steps in Access. I think it would be a module, but very unsure at this time and then how to do these steps.
Any guidance or examples would be greatly greatly appreciated. Also, you can send info to ballj_35@yahoo.com

Thanx

Jerry

[ajetrumpet]

ball,

you will probably not get examples of this because it's too complicated. Some people may give some links though that can give you some tips on it. For example, see here: http://support.microsoft.com/kb/161088

and here: http://support.microsoft.com/kb/318881

and lastly, here: http://www.access-programmers.co.uk/...d.php?t=158170

that'll show you how to use object creation within VBA to send outlook emails. the problem with doing this though is:

*you need to have outlook on your machine.
*the code is error prone because it involves many different libraries and objects.

that is the easiest way to do it though...the toughest part is probably creating the outlook object and learning the methods and properties to get this done with. Once you're over that hill though, everything is else is cakewalk. good luck!

[ballj_35]

They want to use Gmail as the email client and not Outlook. This link helps the best: http://support.microsoft.com/kb/318881, but I cannot find the following:

1. In the Available References list, click Microsoft Outlook 8.0 Object Library, and then click OK.

Therefore, what would I use for Gmail? Thanx in advance.

Jerry

[ballj_35]

The final challenge is getting the value from the Combo Box and getting the data from the table based on the key.
When I add in the line for the AdKey, I am getting the message
"Too Few Parameters" on the db.execute statement. Again, if I could
look at this in debug, I might be able to get there, but I can't.
The code is as follows, the issue is in bold, italic and underlined:
=========================
Private Sub cmdCreateFile_Click()
Dim End_Date As Date
End_Date = Forms![frmMain]![Edate_Sel]
Dim Start_Date As Date

Start_Date = Forms![frmMain]![Sdate_Sel]

Dim db As Database
Dim TblIns As String
Dim test As String
Set db = CurrentDb()
Dim EndDate As Date

Dim AdKey As String
AdKey = Me.Email_Ad_Key.Column(1)

TblIns = "INSERT INTO tblMailList ( Phone_Number, Email_To, Email_Body, Date_Paid_Bill )"
TblIns = TblIns & " SELECT tblMain.Phone_Number, tblMain.Phone_Number & tblMain.Email_Provider, "
TblIns = TblIns & " tblAds.Ad_Body, tblTrans.Date_Paid_Bill "
TblIns = TblIns & " FROM tblAds, tblTrans, tblMain "
TblIns = TblIns & " WHERE (tblMain.Send_Email=True) And (tblMain.Email_provider Is Not Null) "
TblIns = TblIns & " And (tblMain.Phone_Number = tblTrans.Phone_Number) "
TblIns = TblIns & " And (tblTrans.Date_Paid_Bill >= " & Format(Me.Sdate_Sel, "0")
TblIns = TblIns & " And tblTrans.Date_Paid_Bill <= " & Format(Me.Edate_Sel, "0") & ")"
TblIns = TblIns & " And (tblAds.Ad_Name = " & Me.Email_Ad_Key.Column(1) & ");"

db.Execute TblIns

MsgBox "Update Complete"

End Sub

Also, does anyone know what I need to do to get to the debugger. When I try I am getting the message "Object Libraries Not Registered". I have performed many searches and there is so much out there, I am getting confused on exactly what to do. I am runnings Access 2007 on Windows 7.
Thanx in advance.

Jerry

[ballj_35]

Problem has been resolved.