vba - How to preserve/retain hyperlinks in email body when using RangetoHTML from Excel -


i using ron de bruin's rangetohtml example send email selection on current worksheet in excel. working perfectly, 2 columns of sheet contain hyperlinks coming plain text (and not clickable). further tried implement suggestion mail range formatting through vba in excel , added "for each hyperl" loop, after doing surprisingly whole email body comes out empty. can see temp file not empty, hyperlinks missing there too.

below example - appreciating ideas on have done wrong!

function rangetohtml(rng range) dim fso object dim ts object dim tempfile string dim tempwb workbook dim hlink hyperlink  tempfile = environ$("temp") & "\" & format(now, "dd-mm-yy h-mm-ss") & ".htm"  'copy range , create new workbook past data in rng.copy set tempwb = workbooks.add(1) tempwb.sheets(1)     .cells(1).pastespecial paste:=8     .cells(1).pastespecial paste:=xlpastevalues     .cells(1).pastespecial paste:=xlpasteformats     .cells(1).select     application.cutcopymode = false     on error resume next     .drawingobjects.visible = true     .drawingobjects.delete     on error goto 0 end  each hlink in rng.hyperlinks     tempwb.sheets(1).hyperlinks.add _     anchor:=tempwb.sheets(1).range(hlink.range.address), _     address:=hlink.address, _     texttodisplay:=hlink.texttodisplay next hlink   'publish sheet htm file tempwb.publishobjects.add( _      sourcetype:=xlsourcerange, _      filename:=tempfile, _      sheet:=tempwb.sheets(1).name, _      source:=tempwb.sheets(1).usedrange.address, _      htmltype:=xlhtmlstatic)     .publish (true) end  'read data htm file rangetohtml set fso = createobject("scripting.filesystemobject") set ts = fso.getfile(tempfile).openastextstream(1, -2) rangetohtml = ts.readall ts.close rangetohtml = replace(rangetohtml, "align=center x:publishsource=", _                       "align=left x:publishsource=")  'close tempwb tempwb.close savechanges:=false  'delete htm file used in function kill tempfile  set ts = nothing set fso = nothing set tempwb = nothing end function

here calling subroutine code:

code sub sendbugreport() dim source range dim dest workbook dim wb workbook dim tempfilepath string dim tempfilename string dim fileextstr string dim fileformatnum long dim outapp object dim outmail object

set wb = activeworkbook set source = nothing on error resume next set source = selection.specialcells(xlcelltypevisible) on error goto 0  if source nothing     msgbox "the source not range or sheet protected, please correct , try again.", vbokonly     exit sub end if  application     .screenupdating = false     .enableevents = false end  set outapp = createobject("outlook.application") set outmail = outapp.createitem(0)  on error resume next outmail         .to = sheets("email subject , dlist").range("b1").value         .cc = ""         .bcc = ""         .subject = sheets("email subject , dlist").range("b5").value         .htmlbody = rangetohtml(source)         .display end on error goto 0 '    .close savechanges:=false  set outmail = nothing set outapp = nothing  application     .screenupdating = true     .enableevents = true end end sub

i came across following link in this.

as summary, adding following in rangetohtml() suffice:

at top:

dim hlink hyperlink

just before publish code:

for each hlink in rng.hyperlinks tempwb.sheets(1).hyperlinks.add _ anchor:=tempwb.sheets(1).range(hlink.range.address), _ address:=hlink.address, _ texttodisplay:=hlink.texttodisplay next hlink

Comments

Post a Comment

Popular posts from this blog

javascript - jQuery: Add class depending on URL in the best way -

i have html progress meter: <div class="col-md-3" style="margin-left: -20px;"> <div class="progress-pos active" id="progess-1"> <div class="progress-pos-inner"> login </div> </div> </div> <div class="col-md-3 progress-pos-container"> <div class="progress-pos" id="progess-2"> <div class="progress-pos-inner"> scan items </div> </div> </div> <div class="col-md-3 progress-pos-container"> <div class="progress-pos" id="progess-3"> <div class="progress-pos-inner"> confirm address </div> </div> </div> <div class="col-md-3 progress-pos-container"> <div class="progress-pos" id="progess-4"> <div class="progress-pos-in...
Read more

caching - How to check if a url path exists in the service worker cache -

i need check if particular url path exists in service worker cache. example, suppose url is: /myserviceworker/service?a=110&b=70 this url exists in cache, there many of them different values of , b. now, suppose want refresh of these urls, how can that? i want know how access key values service worker cache. if know key, plan follows: var url = new url(key); if(url.pathname === "\/myserviceworker/service") refetch key but not sure how cache key , in format is. mean, string or url? cache api has match() method returns promise resolving in response object if match or undefined if no match exists. second parameter object can specify ignoresearch not take account url parameters. the ignoresearch option supported firefox ( chrome status here ). in other hand, retrieve cache entries, can use keys() method.
Read more

Redirect to a HTTPS version using .htaccess -

i need modify .htaccess file redirect urls https version without "www". http://example.com --> https://example.com http://www.example.com --> https://example.com https://www.example.com --> https://example.com this how .htaccess file looks like: rewriteengine on rewritecond %{request_filename} !-d rewritecond %{request_filename} !-f rewriterule ^(.*)$ redirect.php?id=$1&page=$2 [qsa,l] what modifications need make .htaccess in order forward https without "www"? it depends on doing current code. looks file called redirect trying display php page seo friendly url. think might confusing people. anyway, need , force https without www, need 1 more rule above. also second rule needs two capture groups in rewriterule test string because wanting 2 different values using $1 , $2 references. rewriteengine on #rediect www non www and/or http https --- combinations. rewritecond %{http_host} !^example\.com [nc,or] rewritecond %{https} !^...
Read more