Copy cell value(hyperlinked)from excel and paste to email body

[Switto]

Dear All,


I am trying to copy cell value, which has linked to website,from Excel sheet and paste it into email body. While all other paste functionis working however hyperlink is not pasted so I am not able to select the cell.


Please see the below code:


.HTMLBody = "Dear " + Cells(i, 8) + ","_


+"<br>" _


+"<br>" _


+"<b>" + "Client Name: " + "</b> " +Cells(i, 3) + "<br>" _


+ "<b>" + "Engagement:" + "</b> " + Cells(i, 5) + "<br>" _ helprequired on this line


+"<b>" + "AML Folder reviewed: " +"</b>" + Cells(i, 6) + "<br>" +"<br>" _


+ "Aquality review by xxxxxxxx of the above AML Folder, used to support your xxxxxx, has identified the following gaps, which must now be urgently rectified toensure compliance with xxxxxxx : " + "<br>" +"<br>" + "<br>" _


+"<b>" + "<u>" + "Information to begathered:" + "</b>" + "</u>" +"<br>" + "<br>" _


+ Cells(i,10)





Thanks for your help.


Warm regards,


Switto

 

[Switto]

please see the code:

 .HTMLBody = "Dear " + Cells(i, 8) + "," _
            + "
" _
            + "
" _
            + "" + "Client Name: " + " " + Cells(i, 3) + "
" _
            + "" + "Engagement: " + " " + Cells(i, 5) + "(I require help hear)
" _
            + "" + "AML Folder reviewed: " + "" + Cells(i, 6) + "
" + "
" _
            + "A quality review by Risk Management of the above AML Folder, used to support your PACE engagement acceptance, has identified the following gaps, which must now be urgently rectified to ensure compliance with UK AML regulations: " + "
" + "
" + "
" _
            + "" + "" + "Information to be gathered:" + "" + "" + "
" + "
" _
            + Cells(i, 10)

 

[Switto]

Hi All,
I have tried the below code to get the hyperlink from the excel:

"Engagement: " + " " + "" + Cells(i, 5) + "" + "
"

It has hyperlinked the cell in the email body however instead of the actual web page address it is showing cell reference.


Any idea how to counter it and get the web page address listed in the excel.

****I don't know hy full code which contains "ahref code" not showing in the above code.


Many Thanks
Switto

 

[daverunt]

Hi see if this works

Cells(i, 5).Hyperlinks(1).Address

 

[Switto]

Hi see if this works

Cells(i, 5).Hyperlinks(1).Address

Thanks for the code. Really appreciate it .
Code is working however it is not completely copying the hyperlink
Is it something to do with Subaddress as well. Not sure

 

[daverunt]

I don't know how to create a SubAddress - do tell, but there is code for it and I obviously haven't tested it.

Try

+ cells(i, 5).Hyperlinks(1).SubAddress

 

[Switto]

I don't know how to create a SubAddress - do tell, but there is code for it and I obviously haven't tested it.

Try

+ cells(i, 5).Hyperlinks(1).SubAddress

Thanks for your response.
I have tested with Sub Address and it is showing the partial hyperlink.
My full hyperlink address is : https://defra-pace.ey.net/PACE/Default.aspx#/Assessment/23200151/Summary
While using .Address in the code, output is coming till :https://defra-pace.ey.net/PACE/Default.aspx#
and the rest half is coming while using the .Subaddress i.e /Assessment/23200151/Summary
any idea why this is happening ,it suppose to copy the entire hyperlink not just bits and pieces

Thanks again to look into this.

 

[daverunt]

Hi,

the idea is to use both and see if that works. Sorry that wasn't clear.

Cells(i, 5).Hyperlinks(1).Address + cells(i, 5).Hyperlinks(1).SubAddress

 

[Switto]

Hi,

the idea is to use both and see if that works. Sorry that wasn't clear.

Cells(i, 5).Hyperlinks(1).Address + cells(i, 5).Hyperlinks(1).SubAddress

Perfect it is working fine. Thanks for the tip.

 

[daverunt]

Glad to help.