Leonardo Marcondes
Leonardo Marcondes

Reputation: 1

Creating outlook email with hyperlink through VBA

I have a macro which the idea is to create an Outlook email from a textbox that I have in excel. The problem is when I write a word and put a hyperlink on it, when the email is displayed, the hyperlink is not there.

Sub Envio()
Dim endereco, arquivo, destino, assunto, mensagem, nome, copia, anexo As String
Dim row, report As Integer
Dim i As Integer
Dim OutApp As Outlook.Application
Dim outMail As Outlook.MailItem

anexo = ThisWorkbook.Sheets("Mensagem").Cells(39, 2).Value
assunto = ThisWorkbook.Sheets("Mensagem").Cells(5, 2).Value
mensagem = ThisWorkbook.Sheets("Mensagem").[TextBox].Text & vbCrLf
copyblind = ThisWorkbook.Sheets("Mensagem").Cells(8, 2).Value


i = 2

destino = ThisWorkbook.Sheets("Emails").Cells(i, 1).Value



Do Until destino = ""
nome = ThisWorkbook.Sheets("Emails").Cells(i, 2).Value
copia = ThisWorkbook.Sheets("Emails").Cells(i, 3).Value


Application.DisplayAlerts = False
Set OutApp = CreateObject("Outlook.Application")
Set outMail = OutApp.CreateItem(olMailItem)


With outMail

.To = destino
If copia <> "" Then
.CC = copia
Else
.CC = ""
End If
.BCC = copyblind
.Subject = nome & ", " & assunto
.Body = mensagem
If anexo <> "" Then
.Attachments.Add (anexo)
End If
.BodyFormat = olFormatHTML
.HTMLBody = "<BODY style=font-size:11pt;font-family:Calibri>" & mensagem & "<BR><BR>" & _
"</BODY>"




.Display

End With

i = i + 1
destino = ThisWorkbook.Sheets("Emails").Cells(i, 1).Value

Set outMail = Nothing
Set OutApp = Nothing

Loop

Application.DisplayAlerts = True

End Sub

Can someone help me?

Upvotes: 0

Views: 10956

Answers (1)

brianyates
brianyates

Reputation: 399

Try putting HTML tags for a link in the body of your email like so:

<a href="https://yourhyperlink.com">Your Hyperlink</a>

Upvotes: 1

Related Questions