• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 195
  • Last Modified:

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.
0
lalithaw
Asked:
lalithaw
  • 13
  • 13
  • +1
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
0
 
KMANCommented:
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
0
 
M.E.PierceCommented:
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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
lalithawAuthor Commented:
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.
0
 
lalithawAuthor Commented:
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.
 
0
 
Éric MoreauSenior .Net ConsultantCommented:
see this sample that converts a recordset into HTML: http://www.freevbcode.com/ShowCode.Asp?ID=1810
0
 
lalithawAuthor Commented:
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.
0
 
Éric MoreauSenior .Net ConsultantCommented:
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>&nbsp;</TD></TR>"

sAns = _
 rs.GetString( , , "</TD><TD>", "</TD></TR><TR><TD>", "&nbsp;")

strM = strM & "<TR><TD>" & Left(sAns, Len(sAns) - 8)
strM = strM & "</TABLE>"

strM = strM & "</BODY> </HTML>"
0
 
lalithawAuthor Commented:
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.
 
0
 
Éric MoreauSenior .Net ConsultantCommented:
>>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>"
0
 
lalithawAuthor Commented:
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.
0
 
lalithawAuthor Commented:
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
0
 
Éric MoreauSenior .Net ConsultantCommented:
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).
0
 
lalithawAuthor Commented:
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.


0
 
Éric MoreauSenior .Net ConsultantCommented:
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
0
 
lalithawAuthor Commented:
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.
0
 
Éric MoreauSenior .Net ConsultantCommented:
is your SMTP server properly configured?

Have you tried sending e-mails with the sample provided with the vbSendMail application?
0
 
lalithawAuthor Commented:
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\billing.mdb"
        .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 & "</B></TH>"
    Next i
    strM = strM & "</TR>"

    strM = strM & "<TR><TD>&nbsp;</TD></TR>"

    sAns = _
    rsRecipient.GetString(, , "</TD><TD>", "</TD></TR><TR><TD>", "&nbsp;")

    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.RecipientDisplayName = rsRecipient!RecepientName
            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.
0
 
lalithawAuthor Commented:
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

0
 
Éric MoreauSenior .Net ConsultantCommented:
>>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
0
 
Éric MoreauSenior .Net ConsultantCommented:
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
0
 
lalithawAuthor Commented:
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.
0
 
Éric MoreauSenior .Net ConsultantCommented:
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@domain.com") ?
0
 
Éric MoreauSenior .Net ConsultantCommented:
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).
0
 
lalithawAuthor Commented:
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.
0
 
Éric MoreauSenior .Net ConsultantCommented:
As I have said, I use this component without any problems with properly configured servers.
0
 
lalithawAuthor Commented:
emoreau..,

Yes I understand this may be server problem.Anyway thank u very much for ur help.I accept ur answer.

Thank you
Lalitha.
0
 
Éric MoreauSenior .Net ConsultantCommented:
>>I accept ur answer.

to send html e-mail, use http://www.freevbcode.com/ShowCode.Asp?ID=109 
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 13
  • 13
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now