CODEWITHSUNDEEP
vbaexcelemailoutlook
MopMop
MopMop

Reputation: 119

How to insert a link in an e-mail VBA Excel

I have created a macro that sends e-mails to certain people when a condition is filled. However, In the body of the e-mail I would like to insert a link like so Link instead of http://blabla.com.

Do you know how I could make that happen ?

The link is on my cell B4, and this is the following code.

Public Sub email_missing_forecast()

Application.ScreenUpdating = False

'Déclaration des variables

derniereligne = Range("B5000").End(xlUp).Row
Project_number = Cells(1, 2).Value
Project_name = Cells(2, 2).Value
Project_due = Cells(3, 2).Value
lien = Cells(4, 2).Value

Dim Objoutlook As New Outlook.Application
Dim Objectmail

'Condition

For i = 6 To derniereligne

Adresse = Cells(i, "D").Value
Adresse2 = Cells(i, "E").Value
Adresse3 = Cells(i, "F").Value

If Cells(i, "B").Value = "No" Then
    Set Objoutlook = New Outlook.Application
    Set Objectmail = Outlook.createitem(olmailitem)

        With Objectmail
        .To = Adresse & ";" & Adresse2 & ";" & Adresse3
        .Subject = "Bobbi Brown | " & Project_number & " " & Project_name & " | Forecast due " & Project_due
        .Body = "Dear All, " & Chr(10) & Chr(10) & "I kindly remind you that forecasts for program " & Project_number & " " & Project_name & " are due " & Project_due & "." & Chr(10) & Chr(10) & "Please enter your forecast at the link below." & Chr(10) & Chr(10) & lien & Chr(10) & Chr(10) & "Best Regards," & Chr(10) & "Christian Chen"
        .Send

End With
End If
Next i

Application.ScreenUpdating = True

MsgBox "Your e-mails have been sent successfully", , "FIY"
End Sub

Upvotes: 1

Views: 7772

Answers (1)

sn152
sn152

Reputation: 276

HTML body can be used to get this. Please use the below lines in your code. enter these lines below Set Objectmail = Outlook.createitem(olmailitem)

strbody = "Dear All, " & Chr(10) & Chr(10) & "I kindly remind you that forecasts for program " & Project_number & " " & Project_name & " are due " & Project_due & "." & Chr(10) & Chr(10) & "Please enter your forecast at the link below." & Chr(10) & Chr(10) & lien & Chr(10) & Chr(10) & "Best Regards," & Chr(10) & "Christian Chen"

Once you type the above lines, just remove

.Body = "Dear All, " & Chr(10) & Chr(10) & "I kindly remind you that forecasts for program " & Project_number & " " & Project_name & " are due " & Project_due & "." & Chr(10) & Chr(10) & "Please enter your forecast at the link below." & Chr(10) & Chr(10) & lien & Chr(10) & Chr(10) & "Best Regards," & Chr(10) & "Christian Chen" and add

.HTMLBody = StrBody & "<a href=""" & ActiveSheet.Range("B4") & """ >Name your link</a>"

Upvotes: 2

Related Questions