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

sasils

New Member
Joined
Jun 3, 2017
Messages
24
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:confused:
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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...

HTML:
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...

Code:
.htmlbody = xMailBody

Hope this helps!
 
Upvote 0
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...

HTML:
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...

Code:
.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
 
Upvote 0
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...

HTML:
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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top