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

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

CodePudding user response：

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.

CodePudding user response：

I see that this is a simpler way to making the body. And thank you very much for the answer. But the thing I actually need help to sort out is how I can attach my link to the PDF into the mail. I have made the StringBody, and just want to attach the links bellow the text now. Thats what im trying to do with this For structure, checking how many links there are in sheet. And then get one line for each link.

enter image description here

My excel document looks like this:

enter image description here

The blue ring is the hyperlink i want to add to my email body.

Steinar