Putting an active link in HTMLBody in CDO

C

corquando

Board Regular
Joined
Jan 8, 2009
Messages
82
Greetings, all.

I have tried without success to put an active link to a URL in the body of an email.

I'm simply going through a database of email addresses and sending each one an email asking to click on a link to go someplace really cool and awesome.

Instead of descriptions, here's the code:

Rich (BB code): 
        For i = 2 To x
            If Range("A" + CStr(i)) = "" Then GoTo 27
            Set iMsg = CreateObject("CDO.Message")
            Set iConf = CreateObject("CDO.Configuration")
 
            iConf.Load -1     'CDO Source Defaults
 
            On Error GoTo 27  'Bad address bailout
 
            Set Flds = iConf.fields
            With Flds
                .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
                .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
                = "MY-EXCHMAIL.massive"
                .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
                .Update
            End With
 
                StrBod = "< HTML >"
                StrBod = StrBod & "< HEAD >< /HEAD >"
                StrBod = StrBod & "< BODY >"
                StrBod = StrBod & "Dear Valued People(s),< p >< p >"
                StrBod = StrBod & "Blahblahblah.< p >< p >"
                StrBod = StrBod & "Blahblahblah..< p >< p >"
                StrBod = StrBod & "Please click on the link below for more information.< p >< p >"
                StrBod = StrBod & "Sincerely,< p >< p >"
                StrBod = StrBod & "Your Marvelous Friends< p >< p >"
                StrBod = StrBod & "< a href = http://www.wxyz.com/and/so_on/and/so_forth.html >< /a >"  
                StrBod = StrBod & "< /BODY >"
                StrBod = "< /HTML >"
          End If
 
            Subj = "Howdy, folks"
            EML = Range("B" + CStr(i)) 'Email address record
 
            With iMsg
                Set .Configuration = iConf
                .To = EML
                .CC = ""
                .BCC = ""
                .From = "Us@OurOutbox"
                .Subject = Subj
                .HTMLBody = StrBod
 
 
            Set iMsg = Nothing
            Set iConf = Nothing
            Set Flds = Nothing
27      Next

(The spaces between the "<" & ">" are on purpose so the code doesn't try to execute.)

When I do this, I get the body of the email exactly as I want it, but the link refuses to show up.

I'm sure it's something vibrantly stupid or else I wouldn't be missing it.

Thanks to all who try, success or not.
 
Sort by date Sort by votes
DonkeyOte said:
Have you tried assigning some text to display ?
Click to expand...


Excellent "think small" track, but still nothing. It made sense, though. Providing an actual clickable piece of text to link to would have been the perfect "duh" solution, but it seems my issues go even further down the evolutionary ladder.

I will play around with that some more, I think - I may have an acceptable "quote" pattern as far as the code is concerned but not one that creates the link as I wish.

Keep the suggestions coming, please; I will "endeavor to persevere."
 
Upvote 0

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
If you run a Debug.Print on that specific element of the message body and/or the whole Body itself does it generate the expected tag ?
 
Upvote 0
This worked fo me:

HTML: 
Sub MailURL()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    strbody = "<HTML><BODY>"
    strbody = strbody & "<A href=http://www.MrExcel.com>URL Text</A>"
    strbody = strbody & "</BODY></HTML>"
    On Error Resume Next
    With OutMail
        .To = "APerson@Somewhere.com"
        .Subject = "Testing URL"
        .HTMLBody = strbody
        .Send
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Pay particular attention to the < A > tag syntax.
 
Upvote 0
SUCCESS!

I was going the wrong way in the quote arrangement. I should have known "simple is best."

This is tremendous, and I hope others can share in this as well.

Thanks to everybody. This week will be wonderful now, no matter what.
 
Upvote 0
Andrew,

I'm having an issue. Here's how I've implemented the code below:

In cell T1 the value is "<A href=\\the server location\folder name\folder_name2\>Your Link<\A>"

I altered the below code to pull Range("T1").Value and send in the HTML body. When I run the code the email generated has the active hyperlink but the address inserts as,"\\theserverlocation\foldername\folder_name2\" without spaces which invalidates the address.

I need to pull the address from a cell for the hyperlink because it varies depending on the user. Otherwise I'd leave it in VBA.

Thoughts?

Thanks for your brain! It's helped me immensely over the years.

-SyrupToes (Que?)

Andrew Poulsom said:
This worked fo me:

HTML: 
Sub MailURL()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    strbody = "<HTML><BODY>"
    strbody = strbody & "<A href=http://www.MrExcel.com>URL Text</A>"
    strbody = strbody & "</BODY></HTML>"
    On Error Resume Next
    With OutMail
        .To = "APerson@Somewhere.com"
        .Subject = "Testing URL"
        .HTMLBody = strbody
        .Send
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Pay particular attention to the < A > tag syntax.
Click to expand...
 
Upvote 0
I do apologize... Obviously I didn't account for the site thinking my HTML would hyperlink 3/4 of my message.

To summarize my post, when pulling the HTML for a hyperlink from a cell value is there a way to leave spaces in the hyperlink? Angled brackets don't work when pulled with VBA code and added into an Outlook email.

Thanks,
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
VBA to email multiple ranges on a sheet.
Replies
0
Views
253
shophoney
S
F
  • Solved
CDO Emails
Replies
1
Views
1K
Fire_Chief
F
S
  • Question
Email with VBA without outlook
Replies
4
Views
902
Shloime
S
B
  • Solved
Unable to attach file using CDO mail
Replies
2
Views
1K
bblevins
B
T
  • Question
vba to send calendar appointments rather then emails
Replies
1
Views
444
Tej92
T

Forum statistics

Threads
1,215,143
Messages
6,123,276
Members
449,093
Latest member
Vincent Khandagale

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top