VBA to Click Hyperlink in Outlook Email

[bt_24]

Hi All,

Here is my primary issue I am trying to solve using VBA in excel, the other steps not listed I should be able to figure out. I get an email sent to me everyday - contained in the email is a hyperlink that when manually clicked downloads a file using my preferred browser(I always have a browser open first). There is no actual file attachment just one hyperlink. Is there a simple solution in VBA to simulate me manually clicking this hyperlink in the email body to kick off the download? After it downloads I plan to save in in another path and modify said document.

Currently I have a solution that from a master excel can access a specific outlook folder path and download file but only when there are actual attachments.

Even if you are able to point me in the general direction any help would be greatly appreciated.

 

[RedBeard]

This might be of help: https://www.slipstick.com/developer/code-samples/open-hyperlinks-email-message/

 

[bt_24]

Hi @RedBeard - Thanks for pointing me in the right direction I was able to modify code on that site to fit my needs.

For everyone else here is the code I used that will loop through a specific email folder a few folders deep and testing all unread emails for the first hyperlink and following them in my default browser (Chrome) if they are there. Then marking the email as read. Feel free to comment if you have questions.

Option Explicit
Sub OpenLinksMessage()


'note need to set up the reference to use regex
'also include reference to outlook


Dim olApp As Outlook.Application
Dim ns As Outlook.Namespace
Dim Inbox_Secret_Files As MAPIFolder


Dim olMail As Outlook.MailItem


Dim RegNew As RegExp
Dim m1 As MatchCollection
Dim m As Match
Dim URLstr As String


Dim wnd As Object
Set wnd = VBA.CreateObject("WScript.Shell")



'this is the actual folder path used - needed because I want to use chrome not IE which is needed for a shell command
 Dim browserPath As String
        browserPath = Chr(34) & "C:\Program Files (x86)\Google\Chrome\Application\chrome.exe" & Chr(34)
        'chr(34 is a double quote


    Set ns = GetNamespace("MAPI")
    Set Inbox_Secret_Files = ns.GetDefaultFolder(olFolderInbox).Folders("Test").Folders("Test2.0") _
          .Folders("Test3").Folders("Test4")
    
            'Check the pacing file folder for an unread email
            'If there are no undread emails then display a message
               If Inbox_Secret_Files.Items.Restrict("[UnRead] = True").Count = 0 Then
                MsgBox "All of the emails and the files have been read the rest of this macro will not run"
                 Exit Sub
               End If
               
               For Each olMail In Inbox_Secret_Files.Items
                If olMail.UnRead Then
                   Set RegNew = New RegExp
                       With RegNew
                           .Pattern = "(https?[:]//([0-9a-z=\?:/\.&-^!#$;_])*)" 'to understand the pattern better search regex - http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops
                           .Global = False 'false will only capture the first link (regex pattern) in the mail body
                           .IgnoreCase = True
                       End With
                    
                          'testing if the pattern is in the email body
                          If RegNew.Test(olMail.Body) = True Then
                             Set m1 = RegNew.Execute(olMail.Body)
                           
                              For Each m In m1
                               URLstr = m.SubMatches(0)
                               Debug.Print URLstr
                               
                         
                                wnd.Run browserPath & " -url " & URLstr, vbMinimizedFocus, True
                                DoEvents
                                
                                      
            
                                Set RegNew = Nothing
                                Set m1 = Nothing
                                Set m = Nothing
                             
                             Next
                         End If
                   olMail.UnRead = False
                   DoEvents
                   olMail.Save
                                     
              End If
            Next

End Sub

 

[Ebraham]

Hi,

what if i want to click on the second link or on the link named as "Download the file here" in the body.

Kindly help advise.

 

[Viv1049]

This helped me! Thank you.