lalithaw
asked on
Write to html file & textfile and email it .
Hi.,
I am developing a billing system.What I want to do is get data from database and write it to html file or text file (as customer's request) and email it to customers.
I appreciate if some body can give me a code example or give me a link to tutorial.
Thanks.
I am developing a billing system.What I want to do is get data from database and write it to html file or text file (as customer's request) and email it to customers.
I appreciate if some body can give me a code example or give me a link to tutorial.
Thanks.
Éric Moreau
to send html e-mail, use http://www.freevbcode.com/ShowCode.Asp?ID=109
To write to a file use something like:
Open "filename" For Output as #1
Print #1, "text for file" & vbCrLf
....
Close 1
use vbCrLf to start a new line.
There are probably APIs for creating HTML without having to code all the tags. Look in your installed references for something to looks like an HTML parser.
Good Luck, K
Open "filename" For Output as #1
Print #1, "text for file" & vbCrLf
....
Close 1
use vbCrLf to start a new line.
There are probably APIs for creating HTML without having to code all the tags. Look in your installed references for something to looks like an HTML parser.
Good Luck, K
What type of database? (Oracle?,Jet?,SQL?)
Database Structure? (Tables,Fields,etc?)
I can give you a *basic* routine to do this,
but I really need the above info to give you
any kind of decent code.
Database Structure? (Tables,Fields,etc?)
I can give you a *basic* routine to do this,
but I really need the above info to give you
any kind of decent code.
ASKER
M.E.Peirce.,
Database is SQL Server.
Table stucture ofcourse very dificult to write here because it is very big.So Can u give me a code assuming few fields.I need to send bill to customers.So some customers need bill as text format and some need it in html format.Thats why first I need to display billing data in html/text file and send it to receipients through email.If u can give me code for those task I appreciate it much.
Thanks.
Database is SQL Server.
Table stucture ofcourse very dificult to write here because it is very big.So Can u give me a code assuming few fields.I need to send bill to customers.So some customers need bill as text format and some need it in html format.Thats why first I need to display billing data in html/text file and send it to receipients through email.If u can give me code for those task I appreciate it much.
Thanks.
ASKER
Hi emoreau .,
Thank you very much.I got that sample code.It is working.Now I can send mails from VB.I want to know one more thing .Ie how can I write the data to text file or html file after retrieving from database.I am here going to send bill to customers.So I want to get customer's transaction data from the database and want to write it to text/Html file and send it to customer.
I appreciate if u can help me for that also.
Thank you very much.I got that sample code.It is working.Now I can send mails from VB.I want to know one more thing .Ie how can I write the data to text file or html file after retrieving from database.I am here going to send bill to customers.So I want to get customer's transaction data from the database and want to write it to text/Html file and send it to customer.
I appreciate if u can help me for that also.
see this sample that converts a recordset into HTML: http://www.freevbcode.com/ShowCode.Asp?ID=1810
ASKER
Thanks emoreau.,
I have accepted ur answer 80%.Thanks for ur help.But u have used asp.I need it to do in Vb enviorenment.Because I need to get that html/text file to email body.Could u tell me please how can I write data to html and how can I get it to message body of a email.I got that vb code to send emails.But there u have to type the message.I need to get it automatically as html or text.Except that point others r ok.
Thanks again for ur help.
I have accepted ur answer 80%.Thanks for ur help.But u have used asp.I need it to do in Vb enviorenment.Because I need to get that html/text file to email body.Could u tell me please how can I write data to html and how can I get it to message body of a email.I got that vb code to send emails.But there u have to type the message.I need to get it automatically as html or text.Except that point others r ok.
Thanks again for ur help.
This is the sample of the link a little bit modified:
strM = "<HTML> <BODY> "
strM = strM & "<TABLE><TR>"
For each f in rs.Fields
strM = strM & "<TH><B>" & f.Name & "</B></TH>"
Next
strM = strM & "</TR>"
strM = strM & "<TR><TD> </TD></TR>"
sAns = _
rs.GetString( , , "</TD><TD>", "</TD></TR><TR><TD>", " ")
strM = strM & "<TR><TD>" & Left(sAns, Len(sAns) - 8)
strM = strM & "</TABLE>"
strM = strM & "</BODY> </HTML>"
strM = "<HTML> <BODY> "
strM = strM & "<TABLE><TR>"
For each f in rs.Fields
strM = strM & "<TH><B>" & f.Name & "</B></TH>"
Next
strM = strM & "</TR>"
strM = strM & "<TR><TD> </TD></TR>"
sAns = _
rs.GetString( , , "</TD><TD>", "</TD></TR><TR><TD>", " ")
strM = strM & "<TR><TD>" & Left(sAns, Len(sAns) - 8)
strM = strM & "</TABLE>"
strM = strM & "</BODY> </HTML>"
ASKER
emoreau.,
But there we have to write server side scripts to get the data from database.If we can do that part at Vb source code and create the html file then it will be ok.But that part I dont know how to do.And refer the send emails program u said there we have to pass the string to method cant we pass file (html or text).Then my problem solve.Because as I said I need to display transaction and amount, total amount in message body when I send bill to customer.
Ex.,
In message body.,
Bill for Month of July
----------------------
Customer Name : James Bond
Customer No : 1111
Bill Month : 03 rd July 2002
Date Transactin Amount
01/07/02 jhjhjhjhj 1000
.
.
.
.
.
Total Amount 343434
So help me to get message body from retrieving data from database.Appreciate ur help.
Lalitha.
But there we have to write server side scripts to get the data from database.If we can do that part at Vb source code and create the html file then it will be ok.But that part I dont know how to do.And refer the send emails program u said there we have to pass the string to method cant we pass file (html or text).Then my problem solve.Because as I said I need to display transaction and amount, total amount in message body when I send bill to customer.
Ex.,
In message body.,
Bill for Month of July
----------------------
Customer Name : James Bond
Customer No : 1111
Bill Month : 03 rd July 2002
Date Transactin Amount
01/07/02 jhjhjhjhj 1000
.
.
.
.
.
Total Amount 343434
So help me to get message body from retrieving data from database.Appreciate ur help.
Lalitha.
>>But there we have to write server side scripts to get the data from database
??? No server side script is required! You open a recordset into your VB application and use the lines of my previous post into your VB application (to build strM) and you give strM as the message body of the e-mail.
To format it the wat you want, you will need to HTML code to strM. Something like:
strM = "<HTML> <BODY> "
strM = strM & "Customer Name : " & rs.fields("CustomerName") & "<br>"
strM = strM & "Customer No : " & rs.fields("CustomerNo") & "<br>"
strM = strM & "<TABLE><TR>"
??? No server side script is required! You open a recordset into your VB application and use the lines of my previous post into your VB application (to build strM) and you give strM as the message body of the e-mail.
To format it the wat you want, you will need to HTML code to strM. Something like:
strM = "<HTML> <BODY> "
strM = strM & "Customer Name : " & rs.fields("CustomerName") & "<br>"
strM = strM & "Customer No : " & rs.fields("CustomerNo") & "<br>"
strM = strM & "<TABLE><TR>"
ASKER
emoreau..,
In earlier version(Before add ur last post) when I send emails for the first mail it says 'Invalid or missing HOST NAME" and disconnected it.But agian connected it automaticaly and sent other mails.But anyway I could send mails without first mail.But after I added ur last post it is giving message in Satus List as '
connecting to SMTP Server(IP address here)
Initializing communications
Clossing connection... '
and stucked there.I debugged the both applications so I realized that for the first mail it is going out at getIpAddress procedure.Till that point parameters has IP address value.But after executing that procedure it was blank and says it in invalid.But for the rest of emails it was ok.I can send mails without errors.But after adding ur last post it also not possible and I cant send mails.
If u have any idia about this error please post it.I have to finish this asap.So please help me.
In earlier version(Before add ur last post) when I send emails for the first mail it says 'Invalid or missing HOST NAME" and disconnected it.But agian connected it automaticaly and sent other mails.But anyway I could send mails without first mail.But after I added ur last post it is giving message in Satus List as '
connecting to SMTP Server(IP address here)
Initializing communications
Clossing connection... '
and stucked there.I debugged the both applications so I realized that for the first mail it is going out at getIpAddress procedure.Till that point parameters has IP address value.But after executing that procedure it was blank and says it in invalid.But for the rest of emails it was ok.I can send mails without errors.But after adding ur last post it also not possible and I cant send mails.
If u have any idia about this error please post it.I have to finish this asap.So please help me.
ASKER
emoreau..,
One more thing I had to define variable 'f' when adding ur last post.So I defined it as a variant.Because we have to define it as variant or object.So I defined it as variant.
Please give me an advice asap.
Thanks
Lalitha
One more thing I had to define variable 'f' when adding ur last post.So I defined it as a variant.Because we have to define it as variant or object.So I defined it as variant.
Please give me an advice asap.
Thanks
Lalitha
You should not use Variant and Object datatype unless necessary.
What are you storing in this variable?
Anyway, a variant can contain anything (including an object).
What are you storing in this variable?
Anyway, a variant can contain anything (including an object).
ASKER
emoreau..,
>> For each f in rs.Fields
strM = strM & "<TH><B>" & f.Name & "</B></TH>"
Next
For this 'f' I used cariant.That because it is giving an error message when compile and run the program.
emoreau why is that previous version(before ur post) it couldnt send first mail and this version it cant be sent any mails.I think it has some relationship there.In first version I couldnt send first mail (Error - 'Invalid or missing HOST NAME" )and rest of the mails I could sent.Second version I couldnt send completely.
I expect ur response asap.
Lalitha.
>> For each f in rs.Fields
strM = strM & "<TH><B>" & f.Name & "</B></TH>"
Next
For this 'f' I used cariant.That because it is giving an error message when compile and run the program.
emoreau why is that previous version(before ur post) it couldnt send first mail and this version it cant be sent any mails.I think it has some relationship there.In first version I couldnt send first mail (Error - 'Invalid or missing HOST NAME" )and rest of the mails I could sent.Second version I couldnt send completely.
I expect ur response asap.
Lalitha.
replace for each with
dim i as integer
for i = 0 to rs.fields.count -1
strM = strM & "<TH><B>" & rs.fields(i).Name & "</B></TH>"
next i
dim i as integer
for i = 0 to rs.fields.count -1
strM = strM & "<TH><B>" & rs.fields(i).Name & "</B></TH>"
next i
ASKER
emoreau..,
Thank you very much for ur quick answer.
But still giving same error.ie
connecting to SMTP Server(IP address here)
Initializing communications
Clossing connection... '
0 messages sent in 32,837.4 seconds
I have a problem here why in first version we couldnt send first mail.I think if we think about that, then we may be able to find a solution.May that cause the problem in here also.
expecting ur response asap.
Thanks u very much again.
Lalitha.
Thank you very much for ur quick answer.
But still giving same error.ie
connecting to SMTP Server(IP address here)
Initializing communications
Clossing connection... '
0 messages sent in 32,837.4 seconds
I have a problem here why in first version we couldnt send first mail.I think if we think about that, then we may be able to find a solution.May that cause the problem in here also.
expecting ur response asap.
Thanks u very much again.
Lalitha.
is your SMTP server properly configured?
Have you tried sending e-mails with the sample provided with the vbSendMail application?
Have you tried sending e-mails with the sample provided with the vbSendMail application?
ASKER
emoreau..,
Yes just now I sent emails from that sample (But after modifying to get recipient address and name from database).
mmm is there any problem with that.Below is the code of that part.Here I have pasted only form load and cmdSend procedures.
Private Sub Form_Load()
Set poSendMail = New clsSendMail
With Me
.Move (Screen.Width - .Width) / 2, (Screen.Height - .Height) / 2
.lblProgress = ""
.lblTime = ""
.Show
.Refresh
End With
With poSendMail
.SMTPHostValidation = VALIDATE_HOST_DNS
.EmailAddressValidation = VALIDATE_SYNTAX
.Delimiter = ";"
End With
RetrieveSavedValues
cmDialog.Flags = cdlOFNFileMustExist + cdlOFNHideReadOnly
Set cnRecipient = New ADODB.Connection
With cnRecipient
.Provider = "Microsoft.Jet.OLEDB.3.51"
.ConnectionString = "Data source=d:\lalitha\billing\
.Open
End With
Set rsRecipient = New ADODB.Recordset
rsRecipient.Open "select * from Recipient", cnRecipient, adOpenForwardOnly, adLockReadOnly
' rsRecipient.MoveFirst
End Sub
Private Sub cmdSend_Click()
Dim lCount As Long
Dim lCtr As Long
Dim t!
Dim strM, sAns As String
Dim i As Integer
cmdSend.Enabled = False
bSendFailed = False
lstStatus.Clear
lblTime.Caption = ""
Screen.MousePointer = vbHourglass
strM = "<HTML> <BODY> "
strM = strM & "<TABLE><TR>"
For i = 0 To rsRecipient.Fields.Count - 1
strM = strM & "<TH><B>" & rsRecipient.Fields(i).Name
Next i
strM = strM & "</TR>"
strM = strM & "<TR><TD> </TD></TR>"
sAns = _
rsRecipient.GetString(, , "</TD><TD>", "</TD></TR><TR><TD>", " ")
strM = strM & "<TR><TD>" & Left(sAns, Len(sAns) - 8)
strM = strM & "</TABLE>"
strM = strM & "</BODY> </HTML>"
With poSendMail
'Set the Basic values simillar to all messages
.SMTPHost = txtServer.Text
.From = txtFrom.Text
.FromDisplayName = txtFromName.Text
.Message = strM
.Attachment = Trim(txtAttach.Text)
.Subject = txtSubject.Text
.AsHTML = True
' establish the server connection
If poSendMail.Connect Then
' loop through the Recordset
Do While Not rsRecipient.EOF
poSendMail.Recipient = rsRecipient!Recepient
poSendMail.RecipientDispla
poSendMail.Send
rsRecipient.MoveNext
Loop
' disconnect from the server
poSendMail.Disconnect
rsRecipient.Close
cnRecipient.Close
Set rsRecipient = Nothing
Set cnRecipient = Nothing
End If
End With
' display the results
If Not bSendFailed Then lblTime.Caption = Str(lCount) & " Messages sent in " & Format$(Timer - t!, "#,##0.0") & " seconds."
Screen.MousePointer = vbDefault
cmdSend.Enabled = True
End Sub
May be some problem there .Could u check it and let me please.
Lalitha.
Yes just now I sent emails from that sample (But after modifying to get recipient address and name from database).
mmm is there any problem with that.Below is the code of that part.Here I have pasted only form load and cmdSend procedures.
Private Sub Form_Load()
Set poSendMail = New clsSendMail
With Me
.Move (Screen.Width - .Width) / 2, (Screen.Height - .Height) / 2
.lblProgress = ""
.lblTime = ""
.Show
.Refresh
End With
With poSendMail
.SMTPHostValidation = VALIDATE_HOST_DNS
.EmailAddressValidation = VALIDATE_SYNTAX
.Delimiter = ";"
End With
RetrieveSavedValues
cmDialog.Flags = cdlOFNFileMustExist + cdlOFNHideReadOnly
Set cnRecipient = New ADODB.Connection
With cnRecipient
.Provider = "Microsoft.Jet.OLEDB.3.51"
.ConnectionString = "Data source=d:\lalitha\billing\
.Open
End With
Set rsRecipient = New ADODB.Recordset
rsRecipient.Open "select * from Recipient", cnRecipient, adOpenForwardOnly, adLockReadOnly
' rsRecipient.MoveFirst
End Sub
Private Sub cmdSend_Click()
Dim lCount As Long
Dim lCtr As Long
Dim t!
Dim strM, sAns As String
Dim i As Integer
cmdSend.Enabled = False
bSendFailed = False
lstStatus.Clear
lblTime.Caption = ""
Screen.MousePointer = vbHourglass
strM = "<HTML> <BODY> "
strM = strM & "<TABLE><TR>"
For i = 0 To rsRecipient.Fields.Count - 1
strM = strM & "<TH><B>" & rsRecipient.Fields(i).Name
Next i
strM = strM & "</TR>"
strM = strM & "<TR><TD> </TD></TR>"
sAns = _
rsRecipient.GetString(, , "</TD><TD>", "</TD></TR><TR><TD>", " ")
strM = strM & "<TR><TD>" & Left(sAns, Len(sAns) - 8)
strM = strM & "</TABLE>"
strM = strM & "</BODY> </HTML>"
With poSendMail
'Set the Basic values simillar to all messages
.SMTPHost = txtServer.Text
.From = txtFrom.Text
.FromDisplayName = txtFromName.Text
.Message = strM
.Attachment = Trim(txtAttach.Text)
.Subject = txtSubject.Text
.AsHTML = True
' establish the server connection
If poSendMail.Connect Then
' loop through the Recordset
Do While Not rsRecipient.EOF
poSendMail.Recipient = rsRecipient!Recepient
poSendMail.RecipientDispla
poSendMail.Send
rsRecipient.MoveNext
Loop
' disconnect from the server
poSendMail.Disconnect
rsRecipient.Close
cnRecipient.Close
Set rsRecipient = Nothing
Set cnRecipient = Nothing
End If
End With
' display the results
If Not bSendFailed Then lblTime.Caption = Str(lCount) & " Messages sent in " & Format$(Timer - t!, "#,##0.0") & " seconds."
Screen.MousePointer = vbDefault
cmdSend.Enabled = True
End Sub
May be some problem there .Could u check it and let me please.
Lalitha.
ASKER
emoreau..,
I debuged the application again and I found that in the class module(clsSendMail)when it pass sHostName to gethostname as a result it passed value 0.So it goes to else code.There it assigns IP address as " "(refer code below).I checked the sHostName value it is 192.168.0.3|.I have a doubt about that '|' part.that IP address is ok correct.Why it is pass it as 192.168.0.3|.If IpHost has value passed then we will not get any error I hope so.If u have any idia let me know please.
Lalitha.
lpHost = gethostbyname(sHostName)
If lpHost Then
' extract the data...
CopyMemory HOST, ByVal lpHost, Len(HOST)
CopyMemory dwIPAddr, ByVal HOST.hAddrList, 4
ReDim tmpIPAddr(1 To HOST.hLen)
CopyMemory tmpIPAddr(1), ByVal dwIPAddr, HOST.hLen
' convert format
For i = 1 To HOST.hLen
sIPAddr = sIPAddr & tmpIPAddr(i) & "."
Next
' set the return value
GetIPAddress = Mid$(sIPAddr, 1, Len(sIPAddr) - 1)
Else
WSAGetLastError
GetIPAddress = ""
End If
I debuged the application again and I found that in the class module(clsSendMail)when it pass sHostName to gethostname as a result it passed value 0.So it goes to else code.There it assigns IP address as " "(refer code below).I checked the sHostName value it is 192.168.0.3|.I have a doubt about that '|' part.that IP address is ok correct.Why it is pass it as 192.168.0.3|.If IpHost has value passed then we will not get any error I hope so.If u have any idia let me know please.
Lalitha.
lpHost = gethostbyname(sHostName)
If lpHost Then
' extract the data...
CopyMemory HOST, ByVal lpHost, Len(HOST)
CopyMemory dwIPAddr, ByVal HOST.hAddrList, 4
ReDim tmpIPAddr(1 To HOST.hLen)
CopyMemory tmpIPAddr(1), ByVal dwIPAddr, HOST.hLen
' convert format
For i = 1 To HOST.hLen
sIPAddr = sIPAddr & tmpIPAddr(i) & "."
Next
' set the return value
GetIPAddress = Mid$(sIPAddr, 1, Len(sIPAddr) - 1)
Else
WSAGetLastError
GetIPAddress = ""
End If
>>I have a doubt about that '|' part
If you look at the line just over GetHostByName, you will see that chr$(0) is appended to the IP address.
Instead of:
.SMTPHostValidation = VALIDATE_HOST_DNS
Try
.SMTPHostValidation = VALIDATE_HOST_NONE
If you look at the line just over GetHostByName, you will see that chr$(0) is appended to the IP address.
Instead of:
.SMTPHostValidation = VALIDATE_HOST_DNS
Try
.SMTPHostValidation = VALIDATE_HOST_NONE
Also, Recipient is not a collection, it is a string. The way you built it, you will get only the last record of your recordset to receive the email.
Use something like this:
With rstTemp
Do Until .EOF
pstrSendTo = pstrSendTo & .Fields("usrEMail").Value & "; "
.MoveNext
Loop
.Close
End With
Set rstTemp = Nothing
'Il semble que si la chaine de caractères se termine par un «;», le e-mail n'est pas envoyé
pstrSendTo = Trim$(pstrSendTo)
If Right$(pstrSendTo, 1) = ";" Then pstrSendTo = Left$(pstrSendTo, Len(pstrSendTo) - 1)
poSendMail.Recipient = pstrSendTo
Use something like this:
With rstTemp
Do Until .EOF
pstrSendTo = pstrSendTo & .Fields("usrEMail").Value & "; "
.MoveNext
Loop
.Close
End With
Set rstTemp = Nothing
'Il semble que si la chaine de caractères se termine par un «;», le e-mail n'est pas envoyé
pstrSendTo = Trim$(pstrSendTo)
If Right$(pstrSendTo, 1) = ";" Then pstrSendTo = Left$(pstrSendTo, Len(pstrSendTo) - 1)
poSendMail.Recipient = pstrSendTo
ASKER
emoreau..,
I tried both posts.But still same error.
emoreau before u send last post I download sample application and send mails again without any modifications.
(ie sent bulk mail with seperating using ';')But there also it is not sending first mail and rest of the mails I could send.For the first mail it says same error(Invalid or missing host).
Thank u very much .
Lalitha.
I tried both posts.But still same error.
emoreau before u send last post I download sample application and send mails again without any modifications.
(ie sent bulk mail with seperating using ';')But there also it is not sending first mail and rest of the mails I could send.For the first mail it says same error(Invalid or missing host).
Thank u very much .
Lalitha.
Are all your e-mail addresses belongs to the same domain? What if you try to put the first address into the second slot (instead of "add1@domain.com; add2@domain.com" try "add2@domain.com;add1@doma
One thing I can say for sure is that I have many applications use this component every day to send e-mails to distribution and I don't have this problem.
The only limitations is that some SMTP server won't let me send e-mails to addresses that are not into the same domain (for security reasons).
The only limitations is that some SMTP server won't let me send e-mails to addresses that are not into the same domain (for security reasons).
ASKER
emoreau..,
As for testing I am using only my email address which is in the same domain.Now I have 5 records in the table.all 5 records have same email addresses but name is different.Is it not possible to send mails for same email address.
I also feel that some security problem in the SMTP server.
Thanks
Lalitha.
As for testing I am using only my email address which is in the same domain.Now I have 5 records in the table.all 5 records have same email addresses but name is different.Is it not possible to send mails for same email address.
I also feel that some security problem in the SMTP server.
Thanks
Lalitha.
As I have said, I use this component without any problems with properly configured servers.
ASKER
emoreau..,
Yes I understand this may be server problem.Anyway thank u very much for ur help.I accept ur answer.
Thank you
Lalitha.
Yes I understand this may be server problem.Anyway thank u very much for ur help.I accept ur answer.
Thank you
Lalitha.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.