Follow a Hyperlink that creates a new Outlook e-mail message by using a VBA Excel Macro.

[Agente442]

Hello,

I'm new to the forum and I hope someone can help me out. OK here we go. I have a hyperlink in a G2 cell that opens a new e-mail message in Outlook. This message is intended to inform suppliers of the invoices that are being paid.

=HYPERLINK("mailto:"&VLOOKUP(C3,VENDORS!A:F,6,0)&"?subject="&B2&"&cc=email1@domain.com;email2@domain.com;email3@domain.com;email4@domain.com","SEND E-MAIL"))

When I click on G2 the new e-mail is created. I have a macro that selects the relevant information and copies it to the clipboard so that I may paste it in the e-mail's body. What I'm trying to do is to get a macro to follow the hyperlink and create the new e-mail message. I've been struggling with the codes suggested in the related questions that have been previously answered because I get an "out of range" message.

Would someone please assist me?

Thank you very much in advance.

 

[OaklandJim]

I'd be willing to try to assist but I'll need something to work with or I'll have to try to create fake data. Might you post a link to a file with fake-but-realistic data? Put the file on Dropbox or 1 Drive. Use the link icon above the message area.

Is there only one hyperlink? Where are the email addresses to include? Are they in separate cells?

Probably no need to use clipboard if email body includes one or more cell ranges.

 

[Agente442]

Thank you very much for your reply OaklandJim,

TEST FILE UPLOADED IN GOOGLE DRIVE

I have created a file with minimum information that shows what I'm trying to do. It contains three tabs:
a) "Payment advice" (cell G2 has the hyperlink function) and c3 has a combo box that allows to select vendor code from sheet "Payments".
b) "Payments" that is populated with a macro from a database generated by our ERP
c) "Vendors" which is vendor master file.

When I click on G2, a new e-mail message is created with the addressees and cc's as well as the relevant e-mail subject.
What I'm trying to do is get is a VBA code to follow that hyperlink without clicking on G2 (I intend to append that code to a button that contains a macro to copy the relevant information to the e-mail body)

I hope this is clearer than my first post. And again thank you for your interest and your time.

 

[OaklandJim]

Why have a hyperlink if you want to "follow that hyperlink without clicking on G2."

What is "relevant information" and where is it located?

 

[Agente442]

Why have a hyperlink if you want to "follow that hyperlink without clicking on G2."

What is "relevant information" and where is it located?

Please accept my apologies as English is not my native language and sometimes words don't convey what I really mean.

I normaly click on G2 to follow this hyperlink. However, this time I intend to "activate" or follow that link to open an Outlook e-mail message by way of a VBA code so that I may append this code to another macro that selects that "relevant information" in other words, a list of invoices being paid (invoice number, date, amount, etc) and a custom text and copies to the clipboard. After e-mail is opened, I paste the information.

That's whay I mean by "not having to click on the cell containing the hyperlink". The link I provided with the file may make this more clear.

Thank you.

 

[OaklandJim]

No apologies are needed. Your English is better than that of many Americans.

The link provided does not allow me to download the file. I sent a request to access the file through Google.

 

[Agente442]

I granted you access this morning. Thank you very much.

 

[OaklandJim]

I hope that THIS WORKBOOK gives you something to work with. If the SEND E-MAIL link is clicked an email is created. The code is set up so you review the email before it is sent.

Worksheet event that responds when the hyperlink is clicked. It calls the sub that creates the email. You might want to Google Excel VBA worksheet events so you understand what they do: Google Search

Option Explicit

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    
    If UCase(Target.Name) Like "*SEND*" Then Call SendMail

End Sub

This sub creates the email

Option Explicit

Sub SendMail()

'   Outlook application objects.
    Dim olApp As Object
    
    Dim olMailItm As Object

    Dim wsEmailMail As Worksheet
    
    Dim sAddress As String
    
    Dim sCC As String
    
    Dim sBCC As String
    
    Dim sSubject As String
    
    Dim rBody As Range
    
    Set wsEmailMail = Worksheets("Sheet1") '<= Change this if the sheet name changes.
    
    With wsEmailMail
        Set rBody = .Range("D12:F27") '<= range where email body is located.

        sAddress = .Range("C3").Value
        sCC = .Range("C4").Value
        sBCC = .Range("C5").Value
        sSubject = .Range("C7").Value
    
    End With

'   Create the Outlook application and the empty email.
    Set olApp = CreateObject("Outlook.Application")
    Set olMailItm = olApp.CreateItem(0)
   
    With olMailItm
        .To = sAddress
        .CC = sCC
        .BCC = sBCC
        .Subject = sSubject
        .HTMLBody = RangetoHTML(rBody)
        
        .Display '.Send
    
    End With

'   Clean up the Outlook application.
    Set olMailItm = Nothing
    Set olApp = Nothing

End Sub

This sub creates the email body as html.

Option Explicit

' ----------------------------------------------------------------
' Procedure Name: RangetoHTML
' Purpose: Create HTML for the body of an email.
' Procedure Kind: Function
' Procedure Access: Public
' Parameter rng (Range): The range to process into an email body.
' Author: Jim
' Date: 6/22/2023
' ----------------------------------------------------------------
'From https://www.rondebruin.nl/win/s1/outlook/bmail2.htm

Function RangetoHTML(rng As Range)
 Dim fso As Object
 Dim ts As Object
 Dim TempFile As String
 Dim TempWB As Workbook

 TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

 'Copy the range and create a new workbook to past the data in
 rng.Copy
 Set TempWB = Workbooks.Add(1)
 With TempWB.Sheets(1)
     .Cells(1).PasteSpecial Paste:=8
     .Cells(1).PasteSpecial xlPasteValues, , False, False
     .Cells(1).PasteSpecial xlPasteFormats, , False, False
     .Cells(1).Select
     Application.CutCopyMode = False
     On Error Resume Next
     .DrawingObjects.Visible = True
     .DrawingObjects.Delete
     On Error GoTo 0
 End With

 'Publish the sheet to a htm file
 With TempWB.PublishObjects.Add( _
      SourceType:=xlSourceRange, _
      Filename:=TempFile, _
      Sheet:=TempWB.Sheets(1).Name, _
      Source:=TempWB.Sheets(1).UsedRange.Address, _
      HtmlType:=xlHtmlStatic)
     .Publish (True)
 End With

 'Read all data from the htm file into RangetoHTML
 Set fso = CreateObject("Scripting.FileSystemObject")
 Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
 RangetoHTML = ts.readall
 ts.Close
 RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                       "align=left x:publishsource=")

 'Close TempWB
 TempWB.Close savechanges:=False

 'Delete the htm file we used in this function
 Kill TempFile

 Set ts = Nothing
 Set fso = Nothing
 Set TempWB = Nothing
End Function

 

[Agente442]

You're a genius! Thank you very much. I understand what you did but I might never understand how you did it. I adapted your code to my original book and I have what I needed. Thank you, thank you, thank you.