×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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.

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

You need to put your HTML in .HTMLBody not .Body

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

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.

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

(OP)
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!

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

How about something simple:

CODE

Dim strBody As String

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

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


---- Andy

There is a great need for a sarcasm font.

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

(OP)
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 (<http://) and not show hyperlink?

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

We'd probably need to see your code to see how you are building your emails and how exactly you are using the oft

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

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="http://www.Google.com">HR Services</a></p>"
strBody = strBody & "</body> </html>" 
would give you:

HI

THIS IS AN ALERT
Please contact HR Services


---- Andy

There is a great need for a sarcasm font.

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

(OP)
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="http://www.Google.com">Google</a></p>"
I get an "Expected: end of statement" and it has the http highlighted.

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

(OP)
ok Andy, let me try what you just sent, thanks

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

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

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

(OP)
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.

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

(OP)
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: sending a formatted email in Access 2016 VBA using Outlook Template, 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!

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

(OP)
Andy, any idea why I would be getting "Compile Error: Expected: end of statement on this statement:
strBody = strBody & "Please contact <a href="http://www.Google.com">HR Services</a></p>"
and the http is highlighted? Is there any particular reference that needs to be made?

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

Ooops. My fault.
There are double quotes in line "Please contact..."

try:
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) & "http://www.Google.com" & Chr(34) & ">HR Services</a></p>"
strBody = strBody & "</body> </html>"


---- Andy

There is a great need for a sarcasm font.

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

(OP)
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?

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

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) & "http://www.Google.com" & Chr(34) & ">HR Services</a></p>"
strBody = strBody & "</body> </html>"

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

Set objMessage = Nothing 

Replace the RED text with your e-mail address, and 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.

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

A text HTML link with displayable link text is defined as:

<a href="url">link text</a>

for which Andrzejek provided the following concrete example:

<a href="http://www.Google.com">HR Services</a>

You might want to review https://www.w3schools.com/Html/html_links.asp

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

(OP)
Of course it was my typing! Can't copy to work machine... Left off the = after 'a href'!
Much appreciated!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close