Display data from text file using VBA

[sariahdog]

I'd like to display the contents of a text file located on a network drive with my VBA module.

I have a VBA module in Access 2002 that creates an outlook HTML email message.

Currently, I either have to hard code the recipients individually or hard code an outlook distribution list name. I'd like to read from a text file that is delimeted by semi colons so it will work with outlook.

Below is what I have working currently, minus reading the text file which for demonstration purposes is on "\\myserver\folder\file.txt"

CODE
Private Sub Command83_Click()
Dim db As Database
Dim strBad As String
Dim strGood As String
Dim stringBody As String
Dim rsValues As Recordset

Set db = CurrentDb
Set rsValues = Me.tundra_jobs_query_subform.Form.Recordset
strBad = "<FONT COLOR='RED' SIZE=1>...FAILED</FONT>"
strGood = "<FONT COLOR='green' SIZE=1>...OK</FONT>"


Set appoutlook = CreateObject("outlook.application")
Set mailoutlook = appoutlook.CreateItem(olMailItem)


stringBody = "<TABLE border=0>" & vbCr & _
"<TR><TD><FONT SIZE=1>Data Migration</TD><TD>" & IIf(Me.[Check_usr_apps_data] = True, strGood, strBad) & "</FONT></TD></TR>" & vbCr & _
"<TR><TD><FONT SIZE=1>Search</TD><TD>" & IIf(Me.[Site_Search] = True, strGood, strBad) & "</FONT></TD></TR>" & vbCr & _
"<TR><TD><FONT SIZE=1>Quick Order</TD><TD>" & IIf(Me.[Site_Quick_Order] = True, strGood, strBad) & "</FONT></TD></TR>" & vbCr & _
"<TR><TD><FONT SIZE=1>Reports</TD><TD>" & IIf(Me.[Site_Report] = True, strGood, strBad) & "</FONT></TD></TR>" & vbCr & _
"<TR><TD><FONT SIZE=1>LinkSupport</TD><TD>" & IIf(Me.[Suport_Pages] = True, strGood, strBad) & "</FONT></TD></TR>" & vbCr & _
"<TR><TD><FONT SIZE=1>Place Order</TD><TD>" & IIf(Me.[Site_Place_Order] = True, strGood, strBad) & "</FONT></TD></TR>" & vbCr & _
"<TR><TD><FONT SIZE=1>Email</TD><TD>" & IIf(Me.[Site_Rec_Order_Email] = True, strGood, strBad) & "</FONT></TD></TR>" & vbCr & _
"<TR><TD><FONT SIZE=1>LNKPAS001</TD><TD>" & IIf(Me.[LNKPAS001] = True, strGood, strBad) & "</FONT></TD></TR>" & vbCr & _
"<TR><TD><FONT SIZE=1>LNKPAS002</TD><TD>" & IIf(Me.[LNKPAS002] = True, strGood, strBad) & "</FONT></TD></TR>" & vbCr & _
"<TR><TD><FONT SIZE=1>LNKPAS003</TD><TD>" & IIf(Me.[LNKPAS003] = True, strGood, strBad) & "</FONT></FONT></TD></TR></TABLE>"

stringBody = stringBody & "<TABLE border=1>" & vbCr
rsValues.MoveFirst

numFields = rsValues.Fields.Count
For y = 0 To rsValues.RecordCount Step 1
stringBody = stringBody & " <TR>" & vbCr
For x = 0 To (rsValues.Fields.Count - 1) Step 1
If (y = 0) Then
'If first run through the records then display header names
stringBody = stringBody & " <TD ALIGN='CENTER' bgcolor='GRAY'><FONT SIZE=1><B>" & rsValues(x).Name & "</B></TD>" & vbCr
Else
'After displaying header names write out the values
stringBody = stringBody & " <TD><FONT SIZE=1>" & rsValues(x) & "</TD>" & vbCr
End If
Next x
stringBody = stringBody & " </TR>" & vbCr
'If this is the first loop (for header names) dont move to next row just yet
If (y <> 0) Then
rsValues.MoveNext
End If
Next y
stringBody = stringBody & "</TABLE><TABLE border=1>" & vbCr & _
"<TR><TD><img src='\\Frafile2\ISData\TechnicalSupport\Data_Mig\weather\forcast.jpg'></TD></TR></TABLE>"

With mailoutlook


.To = "email@list.com"
.cc = ""
.Subject = "Daily Production Site Report for " & Now()
.HTMLBody = stringBody

.display
End With
End Sub

 

[namliam]

You could just use the OPEN command to open the file, read its contence and use that to mail....

Look it up in the help... to find out how exactly it works using Input and close...

Regards