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



## sasils (May 20, 2019)

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 (May 21, 2019)

*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 (May 21, 2019)

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



Domenic said:


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




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 (May 22, 2019)

*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
```


----------

