Hyperlink from cell value
 

I want to take the value of a cell and use it as the address for a hyperlink.
I created Day1 as a variable and to pull the address from the excel document
using Set Day1 = ActiveSheet.Range("M184"). I run into problems once i try
to use Day1 in the href tag in Format(Range("E4") - 2, "mm/dd") & " ECCR (<A
HREF=Day1Daily<A, MTD)<br<br" & _. Any ideas how i can do this
correctly? I have included the entire code that i have. Thanks



Sub Mail_workbook_Outlook_1_Weekly()

Dim OutApp As Object
Dim OutMail As Object
Dim Attach1 As Range
Dim Attach2 As Range
Dim Attach3 As Range
Dim Day1 As Range
Dim Day2 As Range
Dim Day3 As Range

Set Attach1 = ActiveSheet.Range("I184")
Set Attach2 = ActiveSheet.Range("I185")
Set Attach3 = ActiveSheet.Range("I186")
Set Day1 = ActiveSheet.Range("M184")
Set Day2 = ActiveSheet.Range("M185")
Set Day3 = ActiveSheet.Range("M186")

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

strbody = "" & _
Format(Range("E4") - 2, "mm/dd") & " ECCR (<A
HREF=Day1Daily<A, MTD)<br<br" & _
Format(Range("E4") - 1, "mm/dd") & " ECCR (<A
HREF=Day2Daily<A, MTD)<br<br" & _
Format(Range("E4"), "mm/dd") & " ECCR (<A HREF=Day3Daily<A,
MTD)<br" & _
""

On Error Resume Next
With OutMail
.To = "Daily DP Report"
.CC = ""
.BCC = ""
.Subject = Format(Range("E4") - 2, "mm/dd") & " - " &
Format(Range("E4"), "mm/dd") & " ECCR (Daily, MTD) Prepay DP"
.htmlBody = strbody
.attachments.Add (Attach1)
.attachments.Add (Attach2)
.attachments.Add (Attach3)
.Display
.ReadReceiptRequested = False
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

Hyperlink from cell value
 

It is hard to tell what you are doing. Not familar with the ECCR function

Day1 is a variable and if you want it to be replaced it has to be outside a
double quoted string. try the statement below.


strbody = Format(Range("E4") - 2, "mm/dd") & _
" ECCR (<A HREF=" & Day1 & "Daily<A, MTD)<br<br" & _
Format(Range("E4") - 1, "mm/dd") & _
" ECCR (<A HREF=" & Day2 & "Daily<A, MTD)<br<br" & _
Format(Range("E4"), "mm/dd") & _
" ECCR (<A HREF=" & Day3 & "Daily<A, MTD)<br"

"Greg H." wrote:

I want to take the value of a cell and use it as the address for a hyperlink.
I created Day1 as a variable and to pull the address from the excel document
using Set Day1 = ActiveSheet.Range("M184"). I run into problems once i try
to use Day1 in the href tag in Format(Range("E4") - 2, "mm/dd") & " ECCR (<A
HREF=Day1Daily<A, MTD)<br<br" & _. Any ideas how i can do this
correctly? I have included the entire code that i have. Thanks



Sub Mail_workbook_Outlook_1_Weekly()

Dim OutApp As Object
Dim OutMail As Object
Dim Attach1 As Range
Dim Attach2 As Range
Dim Attach3 As Range
Dim Day1 As Range
Dim Day2 As Range
Dim Day3 As Range

Set Attach1 = ActiveSheet.Range("I184")
Set Attach2 = ActiveSheet.Range("I185")
Set Attach3 = ActiveSheet.Range("I186")
Set Day1 = ActiveSheet.Range("M184")
Set Day2 = ActiveSheet.Range("M185")
Set Day3 = ActiveSheet.Range("M186")

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

strbody = "" & _
Format(Range("E4") - 2, "mm/dd") & " ECCR (<A
HREF=Day1Daily<A, MTD)<br<br" & _
Format(Range("E4") - 1, "mm/dd") & " ECCR (<A
HREF=Day2Daily<A, MTD)<br<br" & _
Format(Range("E4"), "mm/dd") & " ECCR (<A HREF=Day3Daily<A,
MTD)<br" & _
""

On Error Resume Next
With OutMail
.To = "Daily DP Report"
.CC = ""
.BCC = ""
.Subject = Format(Range("E4") - 2, "mm/dd") & " - " &
Format(Range("E4"), "mm/dd") & " ECCR (Daily, MTD) Prepay DP"
.htmlBody = strbody
.attachments.Add (Attach1)
.attachments.Add (Attach2)
.attachments.Add (Attach3)
.Display
.ReadReceiptRequested = False
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

Hyperlink from cell value
 

That worked perfect. Thanks

As for ECCR, its not a function, its the name of an application I use. For
this email its just text describing what the link is for. Thanks again.

"Joel" wrote:

It is hard to tell what you are doing. Not familar with the ECCR function

Day1 is a variable and if you want it to be replaced it has to be outside a
double quoted string. try the statement below.


strbody = Format(Range("E4") - 2, "mm/dd") & _
" ECCR (<A HREF=" & Day1 & "Daily<A, MTD)<br<br" & _
Format(Range("E4") - 1, "mm/dd") & _
" ECCR (<A HREF=" & Day2 & "Daily<A, MTD)<br<br" & _
Format(Range("E4"), "mm/dd") & _
" ECCR (<A HREF=" & Day3 & "Daily<A, MTD)<br"

"Greg H." wrote:

I want to take the value of a cell and use it as the address for a hyperlink.
I created Day1 as a variable and to pull the address from the excel document
using Set Day1 = ActiveSheet.Range("M184"). I run into problems once i try
to use Day1 in the href tag in Format(Range("E4") - 2, "mm/dd") & " ECCR (<A
HREF=Day1Daily<A, MTD)<br<br" & _. Any ideas how i can do this
correctly? I have included the entire code that i have. Thanks



Sub Mail_workbook_Outlook_1_Weekly()

Dim OutApp As Object
Dim OutMail As Object
Dim Attach1 As Range
Dim Attach2 As Range
Dim Attach3 As Range
Dim Day1 As Range
Dim Day2 As Range
Dim Day3 As Range

Set Attach1 = ActiveSheet.Range("I184")
Set Attach2 = ActiveSheet.Range("I185")
Set Attach3 = ActiveSheet.Range("I186")
Set Day1 = ActiveSheet.Range("M184")
Set Day2 = ActiveSheet.Range("M185")
Set Day3 = ActiveSheet.Range("M186")

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

strbody = "" & _
Format(Range("E4") - 2, "mm/dd") & " ECCR (<A
HREF=Day1Daily<A, MTD)<br<br" & _
Format(Range("E4") - 1, "mm/dd") & " ECCR (<A
HREF=Day2Daily<A, MTD)<br<br" & _
Format(Range("E4"), "mm/dd") & " ECCR (<A HREF=Day3Daily<A,
MTD)<br" & _
""

On Error Resume Next
With OutMail
.To = "Daily DP Report"
.CC = ""
.BCC = ""
.Subject = Format(Range("E4") - 2, "mm/dd") & " - " &
Format(Range("E4"), "mm/dd") & " ECCR (Daily, MTD) Prepay DP"
.htmlBody = strbody
.attachments.Add (Attach1)
.attachments.Add (Attach2)
.attachments.Add (Attach3)
.Display
.ReadReceiptRequested = False
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

End Sub