Solved

Write to html file & textfile and email it .

Posted on 2002-06-26
28
184 Views
Last Modified: 2010-05-02
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
Comment
Question by:lalithaw
  • 13
  • 13
  • +1
28 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7110272
0
 
LVL 5

Expert Comment

by:KMAN
ID: 7110501
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
 
LVL 1

Expert Comment

by:M.E.Pierce
ID: 7110795
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
 
LVL 1

Author Comment

by:lalithaw
ID: 7115578
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
 
LVL 1

Author Comment

by:lalithaw
ID: 7115689
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
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7122520
see this sample that converts a recordset into HTML: http://www.freevbcode.com/ShowCode.Asp?ID=1810
0
 
LVL 1

Author Comment

by:lalithaw
ID: 7123787
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
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7124245
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
 
LVL 1

Author Comment

by:lalithaw
ID: 7125866
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
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7126501
>>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
 
LVL 1

Author Comment

by:lalithaw
ID: 7128946
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
 
LVL 1

Author Comment

by:lalithaw
ID: 7128995
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
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7129516
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
 
LVL 1

Author Comment

by:lalithaw
ID: 7130942
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7130948
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
 
LVL 1

Author Comment

by:lalithaw
ID: 7130971
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
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7130978
is your SMTP server properly configured?

Have you tried sending e-mails with the sample provided with the vbSendMail application?
0
 
LVL 1

Author Comment

by:lalithaw
ID: 7130981
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
 
LVL 1

Author Comment

by:lalithaw
ID: 7131097
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
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7131551
>>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
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7131557
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
 
LVL 1

Author Comment

by:lalithaw
ID: 7131624
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
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7131726
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
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7131730
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
 
LVL 1

Author Comment

by:lalithaw
ID: 7134959
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
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7134963
As I have said, I use this component without any problems with properly configured servers.
0
 
LVL 1

Author Comment

by:lalithaw
ID: 7145418
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
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 100 total points
ID: 7145611
>>I accept ur answer.

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

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now