Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to code in VBA a HTML body of email, fonts, hyperlinks for text fields... 2

Status
Not open for further replies.

08211987

Programmer
Apr 20, 2012
187
US
I am building emails in VBA and would like to define the overall font for the body of the email. I would also provide text that would actually be a hyperlink, such as: go to this our SharePoint site and it would go to that site, or People names that would be a <mailto> link. I currently have the text formatted in a .oft outlook html template but when it displays no formatting is maintained. I would rather code it in the .Body of the VBA code if I knew how. I will go ahead and add any attachments when I need a lot of formatting.
 
What I have done in this situation is - create a Word document, format it the way you want with all hyperlinks, fonts, etc., and save it as HTML file. Then open the file in Notepad and see how it is done.
You could also open this HTML file in IE and go to View - Source and see the same information.
Just pick what you want and recreate it in your VBA code. It is just a text with some tags.


---- Andy

There is a great need for a sarcasm font.
 
If I am using the .oft template does the .HTMLBody need to be included in the VBA? What am I missing that is causing the formatted template from dropping the bolds and causing the actual URL hyperlinks to display

If I decide not to use the .oft template I'm not sure how to code the .HTMLBody = "value" or variable(s) value. Can you give me some examples? This would also resolve another post that I have and will give you that number once I am out of this screen.

Appreciate your help!
 
How about something simple:

Code:
Dim strBody As String

strBody = "<html> <body>"
strBody = strBody & "Click here to go to <a href="[URL unfurl="true"]http://www.Google.com">Google</a></p>"[/URL]
strBody = strBody & "</body> </html>"

With YourMessage
    ....
    .HTMLBody = strBody
    ...
End With


---- Andy

There is a great need for a sarcasm font.
 
As far as examples, I am trying:

"Hi " & ",<br><br>" & _
"THIS IS AN ALERT" & _ how to bold or change font?
"Please contact HR Services" & _ how to underline HR Services and make it a hyperlink (< and not show hyperlink?
 
We'd probably need to see your code to see how you are building your emails and how exactly you are using the oft
 
strongm is right, your code here would be beneficial, big time.

As for your example:
Code:
strBody = "<html> <body>Hi<br><br>"
strBody = strBody & "THIS IS AN <b><span style='color:red'>ALERT</span></b><br>"
strBody = strBody & "Please contact  <a href="[URL unfurl="true"]http://www.Google.com">HR[/URL] Services</a></p>"
strBody = strBody & "</body> </html>"
would give you:

HI

THIS IS AN [RED]ALERT[/RED]
Please contact HR Services


---- Andy

There is a great need for a sarcasm font.
 
I'll work on getting my code together because I'd like to show how I bring in the template which would be the ultimate because I would not need an attachment with the format I want for instructions, I could have those in the email itself!

But for now when I am trying the example above from Andy - the 3rd statement - strBody = strBody & "Click here to go to <a href="I get an "Expected: end of statement" and it has the http highlighted.
 
Actually, a quick review (I set up a quick HTML-based OFT) and then used VBA to send it to myself - and encountered the same problems that you describe) suggests that there is a 'feature' regarding using a template to create an HTML mail - it appears expect to be .Dislay(ed) before sending, to ensure format is properly synchronised, but you probably don't want to do that.

So I had a little think … how else can we trigger internal synchronisation? Well, presumably setting HTMLBody does - so I tried .HTMLbody = .HTMLbody before .Send … and it worked.

So, if using a template - make sure you force synchronisation before .Send(ing) with .HTMLBody = .HTMLBody.

And if handcrafting your HTML, stick it in .HTMLBody, not .Body

 
Wow that sounds promising,let me try. I got the same error on the http highlighted end of statement error, like I need something set to reference http? Let me try what your suggestion strongm.
 
This is perfect! Amazing 1 line of code to resolve bringing the template format .HTMLBody = .HTMLBody
strongm, if you want to put that same suggestion in Post: thread705-1789870, I'll give you a star there also, I wasn't getting any hits there!

Thanks again and also to Andy for the HTML examples, I'll use them another time!
 
Andy, any idea why I would be getting "Compile Error: Expected: end of statement on this statement:
strBody = strBody & "Please contact <a href=" Services</a></p>"
and the http is highlighted? Is there any particular reference that needs to be made?
 
Ooops. My fault.
There are double quotes in line "Please contact..."

try:[tt]
strBody = "<html> <body>Hi<br><br>"
strBody = strBody & "THIS IS AN <b><span style='color:red'>ALERT</span></b><br>"
strBody = strBody & "Please contact <a href=" & Chr(34) & " & Chr(34) & ">HR Services</a></p>"
strBody = strBody & "</body> </html>"
[/tt]


---- Andy

There is a great need for a sarcasm font.
 
I got past the error but I am not seeing where any of the text is a hyperlink. Can you help me understand how a text value becomes a hyperlink?
 
Sure.
Start new Access. Have one form with one button
Add a Reference to Microsoft CDO for Windows 2000 Library
Paste this code into your _Click event for your button:

Code:
Dim objMessage As New CDO.Message
Dim strBody As String

strBody = "<html> <body>Hi<br><br>"
strBody = strBody & "THIS IS AN <b><span style='color:red'>ALERT</span></b><br>"
strBody = strBody & "Please contact <a href=" & Chr(34) & "[URL unfurl="true"]http://www.Google.com"[/URL] & Chr(34) & ">HR Services</a></p>"
strBody = strBody & "</body> </html>"

With objMessage
    .From = "[red]YourEMail@domain.com[/red]"
    .To = "[red]YourEMail@domain.com[/red]"
    .Subject = "This is a test alert"
    
    .HTMLBody = strBody
    
    With .Configuration.Fields
        .Item(CDO.cdoSMTPServer) = "[blue]YourSMTPServer[/blue]"
        .Item(CDO.cdoSMTPServerPort) = 25
        .Item(CDO.cdoSendUsingMethod) = CDO.cdoSendUsingPort
        .Item(cdoSMTPConnectionTimeout) = 10
        .Update
    End With
    .Send
End With

Set objMessage = Nothing

Replace the [red]RED[/red] text with your e-mail address, and [blue]BLUE[/blue] text with your SMTPServer address.

Run this code and you should get an e-mail with the text as in my post from 27 Sep 18 14:00


---- Andy

There is a great need for a sarcasm font.
 
Of course it was my typing! Can't copy to work machine... Left off the = after 'a href'!
Much appreciated!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top