How to add a Hyperlink in an E-Mail based on Excel

[AnnAbel97]

Dear World,

my Name is Annabel and I am quite new at world of VBA. I must admit I absolutely adore the feeling, when something works. Maybe you can help me, to get this feeling back.

I just learned how to generate an E-Mail based on an Excel-table. Now I would like to add a LINK when clicking on a special word.

The actual code is as follows:

Sub Bulkversand()


Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Bulkversand_JF_TP")


Dim OA As Object
Dim msg As Object


'Update Date in List


ThisWorkbook.Sheets("Bulkversand").Range("G2").Value = Date


'Create Mail based on List


Set OA = CreateObject("Outlook.Application")


Dim i As Integer
Dim last_row As Integer


last_row = Application.WorksheetFunction.CountA(sh.Range("A:A"))


For i = 2 To last_row


Set msg = OA.CreateItem(0)


msg.To = sh.Range("A" & i).Value
msg.CC = sh.Range("B" & i).Value
msg.Subject = sh.Range("C" & i).Value
msg.Body = sh.Range("D" & i).Value




If sh.Range("E" & i).Value <> "" Then
msg.Attachments.Add sh.Range("E" & i).Value


End If


msg.Display 'alternativ .send


Next i


End Sub

[/ICODE]

The basic table is like this:

To	CC	Subject	Body	Attachments
XXX@x.com	someone@y.com	Something	Hi there, please check out the following Link. (Here I would aprreciate a Link to Sharepoint when clicking on the word "Link") BR Annabel

Do you know how to edit the code?

 

[Crystalyzer]

Is the link already part of the "Body" data or is that going to come from another cell in the worksheet?

 

[AnnAbel97]

Hi Crystalyzer, thanks for your message! Until now I haven't decided, it didn't work. Maybe you have an idea?

 

[Crystalyzer]

If you are able to change your worksheet format as below

Book3
	A	B	C	D	E	F	G	H	I
1	To	CC	Subject	Greeting	Body	Link	Closing	Signature	Attachment
2	xxx@x.com	someone@Yahoo.com	Something	Hi There,	Please check out the following	Doodle Champion Island Games!	Best Regards,	Annabel
Bulkversand_JF_TP

Then this code should do the trick

Sub Bulkversand()
    Dim sh As Worksheet
    Dim OA As Outlook.Application
    Dim msg As Outlook.MailItem
    Dim i As Integer
    Dim last_row As Integer
    
    Set sh = ThisWorkbook.Sheets("Bulkversand_JF_TP")
    Set OA = New Outlook.Application
    
    last_row = sh.Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 2 To last_row
        Set msg = OA.CreateItem(olMailItem)
        msg.To = sh.Range("A" & i).Value
        msg.CC = sh.Range("B" & i).Value
        msg.Subject = sh.Range("C" & i).Value
        msg.HTMLBody = sh.Range("D" & i).Value & "<br /><br />" & _
             sh.Range("E" & i).Value & " <a href='" & sh.Range("E" & i).Value & "'>LINK</a>." _
             & "<br /><br />" & sh.Range("G" & i).Value & "<br />" & sh.Range("H" & i).Value
        If sh.Range("I" & i).Value <> "" Then msg.Attachments.Add sh.Range("E" & i).Value
        msg.Display 'alternativ .send
    Next i
End Sub

 

[AnnAbel97]

Dear Crystalyzer, awesome, it works!! THAAAAANK you so much!

 

[AnnAbel97]

Hi Crystalyzer, do you maybe also know how to add a signature? I would love to put the signature thats popping up when opening a new mail under my text. I've tried various ways of the internet, but unfortunately they are not really working.. Can you help me?

 

[Crystalyzer]

Does your signature already exist in outlook? If not, you would need to create it and provide me with the name of the file you want to use and the path of it's location.

 

[AnnAbel97]

If I write a normal E-Mail there is a signature. But when I start my Mailing-Macro it's away and I have to add it manually. So you mean I have to export it in a PDF or which kind of data-format? Let's say the file was named "Signature" and was stored on my desktop. What would be the code?