HTML Tabbing (I Know) and Currency Formatting (1 Viewer)

D

Douglas Post

Registered User.
Local time
Yesterday, 23:54
Joined
Feb 10, 2012
Messages
22
I have created a bit of code that pulls data from a form and populates an email for use in Outlook. I am down to a few formatting hurdles and am unsure of how to go about it.

The First Issue. My current output looks like this
[FONT=&quot]SHOWING INFORMATION[/FONT][FONT=&quot]
Agent: Some Agent
Office: Some Office
Showing Date: Some Date
Showing Time: Some Times
Occupancy: Vacant[/FONT]

I want the results to be lined up as if I was using a TAB. I know HTML cannot do tabs:

My other issue us that currency fields are being displayed as:
[FONT=&quot]2011 Assessed Value: 65500

When I want them displayed as:
[/FONT][FONT=&quot]2011 Assessed Value: $65,500[/FONT]

My bloated code:
Code: 
Private Sub Send_Info_Click()
  
    Dim OutApp As Object
    Dim OutMail As Object
    Dim Strbody As String
    Dim Strbody2 As String
    Dim SigString As String
    Dim Signature As String
    Dim SendEmail As String 'SendEmail is the hyperlink field containing the email address

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    SendEmail = Left(Me!Email, InStr(1, Me!Email, "#") - 1) 'This removes the #mailto:XXX@XXXXX#
    Address = Me!Address
    City = Me!City
    State = Me!State
    Zip = Me!Zip
    MLS = Me!MLS
    ListingLink = Me!
[Listing Link]
    ParcelLink = Me![Parcel Data Link]
    
    Strbody = "<font face=verdana size=2 color=#4F81BD>Dear " & [First Name] & ":" & "<br><br>" & _
              "Thank-you for showing my listing located at " & [Address] & ", " & [City] & ", " & [State] & " " & [Zip] & "." & "<br><br>" & _
              "In an effort to better educate the Seller on this property's position in the market, please provide showing feedback regarding pricing, condition and further interest." & "<br><br>" & _
              "<b><u>SHOWING INFORMATION</u></b>" & _
              "<br>Agent: " & [First Name] & " " & [Last Name] & _
              "<br>Office: " & [Office Name] & _
              "<br>Showing Date: " & [Showing Date] & _
              "<br>Showing Time: " & [Showing Time] & _
              "<br>Occupancy: " & [Occupancy] & _
              "<br><br><b><u>ACCESS INFORMATION</u></b>" & _
              "<br>Access Location: " & [Keybox Location] & _
              "<br>Access Code: " & [Keybox Code] & _
              "<br><br><b><u>PROPERTY INFORMATION</u></b>" & _
              "<br>Tax ID# " & [Tax ID] & _
              "<br>MLS# " & [MLS] & _
              "<br>Address: " & [Address] & ", " & [City] & ", " & [State] & " " & [Zip] & _
              "<br>" & [Tax Year] & " Assessed Value: " & [Assessed Value] & _
              "<br>" & [Tax Year] & " Taxable Value: " & [Taxable Value] & _
              "<br><br>Type: " & [Property Type] & _
              "<br>SF: " & [SF] & _
              "<br>Acreage: " & [Acreage] & _
              "<br>School District: " & [School District] & _
              "<br>Government Unit: " & [Government Unit]


    Strbody2 = "<br><br><A HREF=" & ParcelLink & ">Link to Parcel Data</A><br><br>" & _
               "<A HREF=" & ListingLink & ">Link to Listing</A></font>"

    SigString = "C:\Users\Douglas Post\AppData\Roaming\Microsoft\Signatures\Brokerage Activity.htm"

    If Dir(SigString) <> "" Then
        Signature = GetBoiler(SigString)
    Else
        Signature = ""
    End If

    On Error Resume Next
    With OutMail
        .To = SendEmail
        .From = "post@theLOcompany.com"
        '.CC = ""
        '.BCC = ""
        .Subject = Address & ", " & City & ", " & State & " " & Zip & " (MLS#" & MLS & ")"
        .HTMLBody = Strbody & Strbody2 & "<br><br>" & Signature
        '.Attachments.Add ("C:\test.txt")
        .Display 'or use .Send if you want to send without preview
        '.ReadReceiptRequested = True or False
        .Importance = 2 'Importance Level  0=Low,1=Normal,2=High
        '.Save
    End With

    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Function GetBoiler(ByVal sFile As String) As String
    Dim fso As Object
    Dim ts As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
    GetBoiler = ts.readall
    ts.Close
End Function
 
V

vbaInet

AWF VIP
Local time
Today, 04:54
Joined
Jan 22, 2010
Messages
26,374
I would have thought you would ask these sort of questions in the Web Development section of the forum.
Douglas Post said:
I want the results to be lined up as if I was using a TAB. I know HTML cannot do tabs
Click to expand...
Anyway, my HTML is rusty but you need four of these &nbsp to simulate a tab.

Douglas Post said:
My other issue us that currency fields are being displayed as:
[FONT=&quot]2011 Assessed Value: 65500

When I want them displayed as:
[/FONT][FONT=&quot]2011 Assessed Value: $65,500[/FONT]
Click to expand...
If you use the Format() function or the FormatCurrency() function directly on the field it should return a currency formatted number. If that that doesn't work, then you will need to use one of the HTML functions.
 
D

Douglas Post

Registered User.
Local time
Yesterday, 23:54
Joined
Feb 10, 2012
Messages
22
I posted this in the VBA as the construct of the email was code driven. But I guess the HTML guys would probably have a better fix. The FormatCurrency() worked like a charm. Still unable to properly format the tabbing even with four of the &nbsp. I tried putting these in quotes and leaving them without quotes but they are not behaving properly.

Code: 
"<br>Agent:" &nbsp &nbsp &nbsp &nbsp & [First Name] & " " & [Last Name] & _
Returns: [FONT=&quot]Agent:Joseph Goldsberry

[/FONT]
Code: 
"<br>Agent:" & "&nbsp&nbsp&nbsp&nbsp" & [First Name] & " " & [Last Name] & _
Returns: [FONT=&quot]Agent: &nbspJoseph Goldsberry[/FONT]
 
T

tehNellie

Registered User.
Local time
Today, 04:54
Joined
Apr 3, 2007
Messages
751
If you're generating the HTML in code, why not stick the results in table with the relevant spacing?

SHOWING INFORMATION
Agent: Some Agent
Office: Some Office
Showing Date: Some Date
Showing Time: Some Times
Occupancy: Vacant

looks like a table to me.
 
D

Douglas Post

Registered User.
Local time
Yesterday, 23:54
Joined
Feb 10, 2012
Messages
22
I thought about that, I just find it odd having to put quotes around everything when I write the HTML. Do you know what the reason is that you have to do this in VBA?
 
spikepl

spikepl

Eledittingent Beliped
Local time
Today, 05:54
Joined
Nov 3, 2010
Messages
6,142
AS tehNellie wrote, I would use a table. There is a handy table generator here: http://www.quackit.com/html/html_table_generator.cfm

As to why quotes? Because you are operating on strings, and a string is delimited by quotes. The HTML is parsed in the message itself, but to pass it to the mailitem, you need to use strings.
 
T

tehNellie

Registered User.
Local time
Today, 04:54
Joined
Apr 3, 2007
Messages
751
because you're combining static characters (the HTML) and the contents of variables (some Agent etc) into another variable, strBody to make up the final HTML that outlook will interpret.

"Anything wrapped in quotes" is treated as a literal string
strNotInQuotes is assumed to be a variable.

VBA neither knows nor cares what the contents of strBody is, it's a string.
 
V

vbaInet

AWF VIP
Local time
Today, 04:54
Joined
Jan 22, 2010
Messages
26,374
Douglas Post said:
Code: 
"<br>Agent:" &nbsp &nbsp &nbsp &nbsp & [First Name] & " " & [Last Name] & _
Returns: [FONT=&quot]Agent:Joseph Goldsberry

[/FONT]
Code: 
"<br>Agent:" & "&nbsp&nbsp&nbsp&nbsp" & [First Name] & " " & [Last Name] & _
Returns: [FONT=&quot]Agent: &nbspJoseph Goldsberry[/FONT]
Click to expand...
It's in this form:
Code: 
"<br>Agent:     " & [First Name] & " " & [Last Name] & _
You can also use a borderless HTML table if you want a fixed layout.
 
D

Douglas Post

Registered User.
Local time
Yesterday, 23:54
Joined
Feb 10, 2012
Messages
22
Thanks to everyone for the guidance. Here is the final code for future reference that utilized a table.

Code: 
Private Sub Send_Info_Click()
  
    Dim OutApp As Object
    Dim OutMail As Object
    Dim Strbody As String
    Dim Strbody2 As String
    Dim Strbody3 As String
    Dim Strbody4 As String
    Dim Strbody5 As String
    Dim SigString As String
    Dim Signature As String
    Dim SendEmail As String 'SendEmail is the hyperlink field containing the email address

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    SendEmail = Left(Me!Email, InStr(1, Me!Email, "#") - 1) 'This removes the #mailto:XXX@XXXXX#
    
    Strbody = "<font face=verdana size=2 color=#4F81BD>Dear " & [First Name] & ":" & "<br><br>" & _
              "Thank-you for showing my listing located at " & [Address] & ", " & [City] & ", " & [State] & " " & [Zip] & "." & "<br><br>" & _
              "In an effort to better educate the Seller on this property's position in the market, please provide showing feedback regarding pricing, condition and further interest."

    Strbody2 = "<b><u>SHOWING INFORMATION</u></b>" & _
               "<table border=0 width=600><font face=verdana size=2 color=#4F81BD><tr>" & _
               "<td width=125>Agent:</td><td>" & [First Name] & " " & [Last Name] & "</td></tr>" & _
               "<tr><td>Office:</td><td>" & [Office Name] & "</td></tr>" & _
               "<tr><td>Showing Date:</td><td>" & [Showing Date] & "</td></tr>" & _
               "<tr><td>Showing Time:</td><td>" & [Showing Time] & "</td></tr>" & _
               "<tr><td>Occupancy:</td><td>" & [Occupancy] & "</table>"
                   
    Strbody3 = "<b><u>ACCESS INFORMATION</u></b>" & _
               "<table border=0 width=600><font face=verdana size=2 color=#4F81BD><tr>" & _
               "<td width=125>Access Location:</td><td>" & [Keybox Location] & "</td></tr>" & _
               "<tr><td>Access Code:</td><td>" & [Keybox Code] & "</table>"

    Strbody4 = "<b><u>PROPERTY INFORMATION</u></b>" & _
               "<table border=0 width=600><font face=verdana size=2 color=#4F81BD><tr>" & _
               "<td width=150>Tax ID#</td><td>" & [Tax ID] & _
               "<tr><td>MLS#</td><td>" & [MLS] & "</td></tr>" & _
               "<tr><td>Address:</td><td>" & [Address] & ", " & [City] & ", " & [State] & " " & [Zip] & "</td></tr>" & _
               "<tr><td>" & [Tax Year] & " Assessed Value:</td><td>" & FormatCurrency([Assessed Value], 0) & "</td></tr>" & _
               "<tr><td>" & [Tax Year] & " Taxable Value:</td><td>" & FormatCurrency([Taxable Value], 0) & "</td></tr>" & _
               "<tr><td>Type:</td><td>" & [Property Type] & "</td></tr>" & _
               "<tr><td>SF:</td><td>" & [SF] & "</td></tr>" & _
               "<tr><td>Acreage:</td><td>" & [Acreage] & "</td></tr>" & _
               "<tr><td>School District:</td><td>" & [School District] & "</td></tr>" & _
               "<tr><td>Government Unit:</td><td>" & [Government Unit] & "</table>"

    Strbody5 = "<A HREF=" & [Parcel Data Link] & ">Link to Parcel Data</A><br><br>" & _
               "<A HREF=" & 
[Listing Link] & ">Link to Listing</A></font>"

    SigString = "C:\Users\Douglas Post\AppData\Roaming\Microsoft\Signatures\Brokerage Activity.htm"

    If Dir(SigString) <> "" Then
        Signature = GetBoiler(SigString)
    Else
        Signature = ""
    End If

    On Error Resume Next
    With OutMail
        .To = SendEmail
        .From = "post@theLOcompany.com"
        '.CC = ""
        '.BCC = ""
        .Subject = Address & ", " & City & ", " & State & " " & Zip & " (MLS#" & MLS & ")"
        .HTMLBody = Strbody & "<br><br>" & Strbody2 & "<br><br>" & Strbody3 & "<br><br>" & Strbody4 & "<br><br>" & Strbody5 & "<br><br>" & Signature
        '.Attachments.Add ("C:\test.txt")
        .Display 'or use .Send if you want to send without preview
        '.ReadReceiptRequested = True or False
        .Importance = 2 'Importance Level  0=Low,1=Normal,2=High
        '.Save
    End With

    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Function GetBoiler(ByVal sFile As String) As String
    Dim fso As Object
    Dim ts As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
    GetBoiler = ts.readall
    ts.Close
End Function
 
You must log in or register to reply here.

Similar threads

J
Save Email to Directory
2 3
Replies
58
Views
2,551
johankotze
J
M
Add Signature to Email body
Replies
2
Views
760
Momma
M
M
Solved Sending Email to recipients from Listbox
Replies
10
Views
1,188
Momma
M
M
Solved Creating individual reports and send to multiple customers via email
Replies
13
Views
1,209
Momma
M
N
Solved Sending email without the attachment - How can i stop this...
Replies
5
Views
1,211
CJ_London
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom