Create boxes in email VBA

[cbrsix]

I have the following code that I use to send emails and in the body I have "Old Address" and "New Address." Right now they are displayed on top of each other separated by a space. How can I get them to be enclosed in box lined up next to each other? For example:

Old Address	New Address
address goes here	address goes here

Private Sub Command14_Click()
On Error GoTo SendEmail_Err
    Dim myOlApp As Object
    Dim myNameSpace As Object
    Dim myFolder As Object
    Dim myItem As Object
    Dim myAttachments, myRecipient As Object
    Dim recipient As String
    Dim file_name As String
    Dim mySubject As Object
    Dim dbs As Object
    Dim rst As Object
    Dim strSQL As String
    
    strSQL = "Query1" 'Select the Query where you want your information to be drawn from
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL)
    rst.MoveFirst
    While Not rst.EOF
        recipient = rst!repemail 'This is the email address that you corresponds to your recipient
        Set myOlApp = CreateObject("Outlook.Application")
        Set myItem = myOlApp.CreateItem(olMailItem)
        Set myAttachments = myItem.Attachments
        Set myRecipient = myItem.Recipients.Add(recipient)
        Set myRecipient = myItem.Recipients.Add("xxx@xxx.com")
        myRecipient.Type = olCC
        '"Message Subject String Here"
        myItem.Subject = "Test"
        '"Put Message Body Text Here"
        myItem.Body = rst!repname & "," & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "Below is an address change we received for you client, " & rst!clientname & "." & Chr(10) & Chr(13) & "Old Address:" & Chr(10) & rst!Exp & Chr(10) & Chr(13) & "New Address:" & Chr(10) & rst!Exp1
        myItem.Display
        rst.Edit
        rst!emailedrep = True
        rst.Update
        rst.MoveNext
    Wend
    DoCmd.Close acForm, "Table3" 'Closes the form
    Set myRecipient = Nothing
    Set myAttachments = Nothing
    Set myItem = Nothing
    Set myOlApp = Nothing
    Set rst = Nothing
SendEmail_Exit:
    Exit Sub
SendEmail_Err:
    MsgBox Err.Description
    Resume SendEmail_Exit
End Sub

[June7]

Might be possible with HTML code tags.

Instead of myItem.Body use myItem.HTMLBody. Review http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

Use tags that will build a table http://www.w3schools.com/html/html_tables.asp

[cbrsix]

I have looked at these and am really not sure how to add them into my code. Any suggestions?

[DepricatedZero]

I have looked at these and am really not sure how to add them into my code. Any suggestions?

I use Outlook templates along with HTMLReplace and replacement strings.

I create a "TemplateData" object that holds all the data I want to be able to pass to my mailer, then take the data and feed it through the mailer a little like this:

Dim oApplication As Outlook.Application
Dim oItem As Outlook.MailItem
Dim oAccount As Outlook.Accounts
Dim signature As String

Public Function BuildTemplate(data As TemplateData)

Set oApplication = New Outlook.Application
Set oItem = oApplication.CreateItemFromTemplate(data.GetPath)
With oItem
    If Int(Year(data.GetStartTime)) > 2000 Then
        .HTMLBody = Replace(.HTMLBody, "INSERT_INCIDENT_START_DATE_TIME", Format(data.GetStartTime, "mm/dd/yyyy h:mm AMPM") & " EST")
    Else
        .HTMLBody = Replace(.HTMLBody, "INSERT_INCIDENT_START_DATE_TIME", " ")
    End If
    .HTMLBody = Replace(.HTMLBody, "INSERT_INCIDENT_DESCRIPTION", data.GetShortDescription)
    .HTMLBody = Replace(.HTMLBody, "INSERT_BRIDGE", data.GetBridge)
    .HTMLBody = Replace(.HTMLBody, "INSERT_SITUATION", data.GetSituation)
    .HTMLBody = Replace(.HTMLBody, "INSERT_IMPACT", data.GetImpact)
    .BCC = .BCC & "; " & data.GetBCC
    .CC = .CC & "; " & data.GetCC
    .subject = data.GetSubject
End With
End Function


Public Function Send()
    oItem.Display
    
End Function

You are, of course, welcome to use whatever you like from that. There are better ways to do it, and I would do it different if I rebuilt it now, being more experienced than when I did build that. Anyway, data.GetPath points to the template being used. The template can include any decoration or formatting you want, and then simple text replacement will let you fill out the variable data you need to put in there.

[June7]

The HTML code tags are just typed as text within quote marks as shown by example in first link. Will have to learn HTML syntax as shown in second link.

.HTMLBody = "<table border="1"><tr><th>Header 1</th><th>Header 2</th></tr><tr><td>row 1, cell 1</td><td>row 1, cell 2</td></tr><tr><td>row 2, cell 1</td><td>row 2, cell 2</td></tr></table>"

Only part I am not clear on is how to handle HTML parameter like the <table border="1">. Not sure if those embedded quote marks need to be changed to apostrophes or doubled quotes.

Do some testing. Send yourself email using the HTML coded body. Incorporate your variable data by concatenation. Then figure out the template approach suggested by DeprecationZero. I have never used templates for anything.

[cbrsix]

Thanks for the reply. I am able to get it to work how I want partially. The only thing I can't figure out is how to get the address in the second row of cells. Currently, I have it looking up the address in the rst!Exp & rst!Exp1. In the html code

myItem.HTMLBody = rst!repname & "," & "" & "" & "Below is an address change we received for your client, " & rst!clientname & "." & "" & "" & "<table border=""2""><tr><th>Old Address</th><th>New Address</th></tr><tr><td>rst!Exr</td><td>rst!Exp1</td></tr></table>"

It just shows the rst!Exp and rst!Exp1 like so. How do I get it to pull the info from the recordset into the table?

Old Address	New Address
rst!Exr	rst!Exp1

[June7]

Concatenation of literal text (the HTML code) and the variables (recordset fields).

rst!repname & ", Below is an address change we received for your client, " & rst!clientname & ".<table border=""2""><tr><th>Old Address</th><th>New Address</th></tr><tr><td>" & rst!Exr & "</td><td>" & rst!Exp1 & "</td></tr></table>"

[cbrsix]

I have tried this. It returns an error of Expected: End of Statement. with this highlighted "</td><td>"

[June7]

There is nothing wrong with the concatenation syntax I suggested, I tested it and it works. Post the exact code you used.

[cbrsix]

I got it to work. Thanks.