How to send multiple hyperlinks with mail in Excel VBA, depending on the cell value

Stones 0
Viewed 33

There is and light order managment system where you can order a few items. When you have chosen your order, you click a button, and the rows that is order and number of items is copied to a new confirmation sheet. This sheet is then supposed to be sent to a chosen reciever by oMail. This works fine. But in one of the columns there is a certificate on PDF(hyperlink), that is linked to a server(local file on my computer:)) I'm trying to send this PDF's as multiple hyperlinks in the mail, but that is no sucess :p. The thing I want to do is check if the cell is empty, if not, attach the hyperlink(s) in my stringbody. And send the mail.

Here is my code:

enter image description here

I use a function also to make the range to html format:

enter image description here

vba excel
1 Answers

It's more useful to edit e-mail body via GetInstector

For example:

Dim myInspector As Object'inspector object
Dim wdDoc As Object''document
Dim myitem As Object'mailitem
   
   
Set myitem = CreateObject("Outlook.Application").CreateItem(0)'creating mailitem from Excel
Set myInspector = myitem.GetInspector'set inspector to edit mailitem
Set wdDoc = myInspector.WordEditor'set MS Word to edit mailbody
 
With myitem'here you are setting the e-mail properties

     .To = Email'fill to e-mail
     .Subject = Subja'Subject
     .Display'display mailitem etc.
    
End With
'here edit the body with MS Word methods
wdDoc.Range.Paragraphs.Add
wdDoc.Range.Paragraphs.item(1).Range.Text = "Something you want in 1st paragraph" & vbCr
wdDoc.Range.Paragraphs.Add
wdDoc.Range.Paragraphs.item(2).Range.Text = "Something else you want in 2nd paragraph " & vbCr

So, actually you edit e-mail programmatically the same as you do it in Word. It makes useless long and complicated string with HTML-tags. You add MS Word objects and forms.

IvanSTV0
Related