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!

Register Log in
  • Congratulations SkipVought 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

08211987

Programmer
Apr 20, 2012
187
0
0
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.
 
Sort by date Sort by votes
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.
 
Upvote 0 Downvote
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!
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
We'd probably need to see your code to see how you are building your emails and how exactly you are using the oft
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
ok Andy, let me try what you just sent, thanks
 
Upvote 0 Downvote
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

 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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!
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Of course it was my typing! Can't copy to work machine... Left off the = after 'a href'!
Much appreciated!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Moss100
  • Locked
  • Question
Vba code I can call from a query to reformat text
Replies
8
Views
136
Moss100
Moss100
Omarahmed22
  • Locked
  • Question
VBA Formating
Replies
1
Views
97
Andrzejek
Andrzejek
Moss100
  • Question
Filter vba problem
Replies
0
Views
85
Moss100
Moss100
AJAY0618
  • Locked
  • Question
How to pass a variable in VBA code by opening a form and storing the variable
Replies
3
Views
71
AJAY0618
AJAY0618
renigar
  • Locked
  • Question
Code to write file path to field always writes to 1st record 1
Replies
5
Views
129
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top