I am looking to store information in Access and generate e-mails with contents of the DB. Being new to VBA this has been difficult and I have made lots of headway but Ive not hit a wall and stuck. The problem I?m having is looping the query in access. I can get the contents in from the first entry but that?s it. I?ve tried EOF and FOR and nada. Any ideas? I also want to put the data in a format that?s styled. Help!!!
[VBA]
Sub Net_Status()
MyDate = Date
Time = Now
NextHour = Hour(Time) + 1 & ":00:00"
DaNextHour = Format(NextHour, "hh:00 AMPM")
Set myolapp = CreateObject("Outlook.application")
Set myitem = myolapp.CreateItem(olMailItem)
Dim curMessage As Outlook.MailItem
Set curMessage = Application.ActiveInspector.CurrentItem
datasep = " - "
Dim rsOutages As ADODB.Recordset
Dim conOutages As ADODB.Connection
Dim strConnectionString As String
'Set the connection string and open the connection
Set conOutages = New ADODB.Connection
strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=H:\outages.mdb;" & _
"Persist Security Info=False"
conOutages.Open strConnectionString
'Attempt to retrieve task records from the database for the given job
Set rsOutages = New ADODB.Recordset
rsOutages.Open "select * from Outages where Include_ = True", _
conOutages, adOpenStatic, adLockReadOnly
Set myOlInspectors = Application.Inspectors
curMessage.SentOnBehalfOfName = "User 2"
curMessage.To = "User 1"
curMessage.CC = "User 3"
curMessage.Subject = "Network Status Summary " & MyDate & " " & DaNextHour & " PST"
curMessage.HTMLBody = "<HTML>" & Location & " " & Ticket & " " & TicStatus & "</html>"
Location = rsOutages.Fields("Location_")
Ticket = rsOutages.Fields("Ticket_")
TicStatus = rsOutages.Fields("TicStatus_")
'rsOutages.MoveFirst
'For Each Field In rsOutages
'If rsOutages.Fields("Include_") = "True" Then
'curMessage.HTMLBody = "<HTML>" & Location & " " & Ticket & " " & TicStatus & "</html>"
'Exit For
'End If
'Next
'Do While Not rsOutages.EOF
'For Each Field In rsOutages
'Do While rsOutages.Fields("Include_")
'If rsOutages.Fields("Include_") = "True" Then
'Debug.Print Location & " " & Ticket & " " & TicStatus
'curMessage.HTMLBody = "<HTML>" & Location & " " & Ticket & " " & TicStatus & "</html>"
'Next
'rsOutages.MoveNext
'Loop
'myitem.Send
End Sub
[/VBA]