VBA code to add URL from excel cell into Outlook email line.

[sasils]

Hi Experts,

I need help on modified a VBA code below

Sub Mail_small_Text_Outlook()
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Dear All" & vbNewLine & vbNewLine & _
"Please see below link for master data request file name: " & Range("B13") & vbNewLine & vbNewLine & _
"Please link link" & vbNewLine & vbNewLine & _
Range("B6") & vbNewLine & vbNewLine & _
"Kindly proceed to update data in this file"
On Error Resume Next
With xOutMail
.To = ""
.CC = "chaiyapruckl@mitrphol.com;rapeepatc@mitrphol.com"
.BCC = ""
.Subject = Range("B14") & " New Master Data Request: " & Range("B13")
.Body = xMailBody
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

The Green TEXT needs your support for code modification below is the requirements
1. B6 cell is a file path, BUT I need to have it as a URL link, so when user open email, they can click on the URL link and will open excel file for them.

Hope you can help me out.

Thank you.

Sasils

 

[Domenic]

Re: HELP: VBA code to add URL from excel cell into Outlook email line.

You'll need to use HTML. First, the assignment to xMailBody will be as follows...

xMailBody = "Dear All<br><br>" & _
    "Please see below link for master data request file name: " & Range("B13").Value & "<br><br>" & _
    "Please link link<br><br>" & _
    "<a href=""" & Range("B6").Value & """>" & Range("B6").Value & "</a><br><br>" & _
    "Kindly proceed to update data in this file"

Then, you'll need to replace the assignment to .body with an assignment to .htmlbody...

.htmlbody = xMailBody

Hope this helps!

 

[sasils]

Re: HELP: VBA code to add URL from excel cell into Outlook email line.

You'll need to use HTML. First, the assignment to xMailBody will be as follows...

xMailBody = "Dear All<br><br>" & _
    "Please see below link for master data request file name: " & Range("B13").Value & "<br><br>" & _
    "Please link link<br><br>" & _
    "<a href=""" & Range("B6").Value & """>" & Range("B6").Value & "</a><br><br>" & _
    "Kindly proceed to update data in this file"

Then, you'll need to replace the assignment to .body with an assignment to .htmlbody...

.htmlbody = xMailBody

Hope this helps!

Hi,

It return errors.... on the first line in green text, i have change code based on your advise on orange text, how to correct it?

Sub Mail_small_Text_Outlook()
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
.htmlbody = "Dear All" & vbNewLine & vbNewLine & _
"Please see below link for master data request file name: " & Range("B13") & vbNewLine & vbNewLine & _
"Please link link" & vbNewLine & vbNewLine & _
"<a href=""" & Range("B6").Value & """>" & Range("B6").Value & "</a><br><br>" & _
"Kindly proceed to update data in this file"
On Error Resume Next
With xOutMail
.To = ""
.CC = "chaiyapruckl@mitrphol.com;rapeepatc@mitrphol.com"
.BCC = ""
.Subject = Range("B14") & " New Master Data Request: " & Range("B13")
.Body = xMailBody
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

 

[Domenic]

Re: HELP: VBA code to add URL from excel cell into Outlook email line.

In future, please use code tags when posting code. It makes it a lot easier to read the code. Anyway, your code should be amended as follows...

Sub Mail_small_Text_Outlook()

    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    
    xMailBody = "Dear All<br><br>" & _
        "Please see below link for master data request file name: " & Range("B13").Value & "<br><br>" & _
        "Please link link<br><br>" & _
        "<a href=""" & Range("B6").Value & """>" & Range("B6").Value & "</a><br><br>" & _
        "Kindly proceed to update data in this file"
    
    On Error Resume Next
    With xOutMail
        .To = ""
        .CC = "chaiyapruckl@mitrphol.com;rapeepatc@mitrphol.com"
        .BCC = ""
        .Subject = Range("B14") & " New Master Data Request: " & Range("B13")
        .htmlbody = xMailBody
        .Display 'or use .Send
    End With
    On Error GoTo 0
    
    Set xOutMail = Nothing
    Set xOutApp = Nothing
    
End Sub