change email address in automatic email (1 Viewer)

[hardik_088]

Hi guys,
I have created automatic email like when quantity is less than three in my database for some items then it will send automatic email. and there is a fixed email address it will send there only. but i am thinking that suppose administator have change his/her email address and now he/she want that automatic email there on new email address .so how she can change or add more email address in database because code will be locked.

So how can i make that facility.
--------------------------------
here is my code for in timer that will check data after every hour

Private Sub Form_Timer()

CheckOrderLevels "Laptops", "[Part number]", "[quantity ordered]", "Laptops"
Me.TimerInterval = 3600000
End Sub
--------------------------------------------
code for automatic email

Private Sub CheckOrderLevels(ByVal strTable As String, ByVal strNameField As String, ByVal strQuantityField As String, ByVal strProduct As String)
On Error Resume Next
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT " & strNameField & " FROM " & strTable _
& " WHERE " & strQuantityField & " < 3", dbOpenSnapshot)
With rs
If .RecordCount > 0 Then
Dim sProducts As String
Dim bFirst As Boolean
sProducts = ""
bFirst = True
.MoveFirst
Do While Not .EOF
If Not bFirst Then sProducts = sProducts & vbCrLf
sProducts = .Fields(strNameField)
bFirst = False
.MoveNext
Loop
SendReport strTable, strProduct, sProducts
End If
.Close
End With
End Sub

Private Sub SendReport(ByVal strTable As String, ByVal strProduct As String, ByVal sProducts As String)
On Error Resume Next
Dim olApp As Object
Dim objMail As Object
Set olApp = GetObject(, "Outlook.Application")
If Err Then Set olApp = CreateObject("Outlook.Application")
Set objMail = olApp.CreateItem(olMailItem)
With objMail
.BodyFormat = olFormatHTML
.To = "test@somewhere.com"
.Subject = "Automated message: " & strProduct & " Requires Ordering"
.HTMLBody = "There is less than three items (Part Number is : " & sProducts & ") in " & strTable & " table"
.Send
End With
End Sub

 

[VilaRestal]

Nice code

You could put a textbox on the form with a default value and set the address to the value of it:

.To = txtAddress.Value

If you want to be able to store it for the next time then you'd have to create a table for it I think and set the forms record source to the table and the textbox's control source to the field

 

[hardik_088]

Thanks,
I am going to try .

 

[hardik_088]

Thanks dear ,
its works. but it is sending to just one person if i add another record means email address then it is just sending to the first person.

Exa..
In my table i added two address but it is sending email to just firstrecords@somewhere.com


1. firstrecods@somewhere.com

2. second@somewhere.com

and if i try to write email address directly in textbox then the value in table is changed.

what i should do?