Extract all links from Outlook email, send to Excel

Status
Not open for further replies.
M

mylon

New Member
Outlook version
Outlook 2013 64 bit
Email Account
IMAP
Hi all,

I'm new to VBA coding so hoping you can help.

I'm trying to come up with a VBA and script that will look for all new emails that are coming from a particular email address to automatically extract all hyperlinks (whether naked URLs or links with anchors) and send them over to an Excel file. It should disregard the URLs of any png/jpg images.

I'm hoping to send all hyperlinks, along with the Subject line, the date/time, anchor text.. all in their respective columns inside just one Excel file.
So whenever an email is received it will add to the existing data in the Excel file.

Is this something that can be done?

Really appreciate your help.

Thank you.
 
It is doable. This article shows how to get the links out - i don't have any samples that write them to a file, but have other samples that write to Excel on my site.

www.slipstick.com

Open Hyperlinks in an Outlook Email Message

Use an Outlook macro to open every hyperlink in the selected email message or open hyperlinks in every message in the selected folder.
www.slipstick.com www.slipstick.com
 
Hi Diane, thank you for the link and your thoughts.

So far using the below code I managed to create a rule for each new email that is delivered to open all hyperlinks in my browser (Firefox).

Almost everything is working well, except that it clicks on the Unsubscribe link as well - even though it's supposed to skip those.

Code: 
Sub OpenLinksMessage(olMail As Outlook.MailItem)
 Dim Reg1 As RegExp
 Dim M1 As MatchCollection
 Dim M As Match
 Dim strURL As String

Dim browserPath As String
browserPath = Chr(34) & "C:\Program Files (x86)\Mozilla Firefox\firefox.exe" & Chr(34)
 
Set Reg1 = New RegExp

With Reg1
 .Pattern = "(https?[:]//([0-9a-z=\?:/\.&-^!#$%;_])*)"
' opens all links, false to open first
 .Global = True
 .IgnoreCase = True
 End With
 
If Reg1.Test(olMail.Body) Then
 
Set M1 = Reg1.Execute(olMail.Body)
 For Each M In M1
   strURL = M.SubMatches(0)

' skips links containing the word 'unsubscribe'
If InStr(strURL, "unsubscribe") Then GoTo NextURL

' skips links that are .png images'
If InStr(strURL, ".png") Then GoTo NextURL

' skips links that are .png images'
If InStr(strURL, ".jpg") Then GoTo NextURL

' skips links that are .png images'
If InStr(strURL, ".jpeg") Then GoTo NextURL

If Right(strURL, 1) = ">" Then strURL = Left(strURL, Len(strURL) - 1)
 
  Shell (browserPath & " -url " & strURL)
  DoEvents
 
NextURL:
  Next
  End If
 
Set Reg1 = Nothing
 End Sub


So, I'm fine with opening the links in browser.
Additionally, I'd like to take all those links and write them in an Excel file.

I found this post on Reddit where it explains: How to parse an Outlook email using vba in Excel

I'm just not sure how to modify the code to fit my use case: 1) Open all hyperlinks in Firefox, except the unsubscribe link, and 2) Write all hyperlinks from all new emails in one pre-defined Excel file.

Any help would be super appreciated.
 
Status
Not open for further replies.
< Help .. got lost ... installing Office like 2016 | Export-Import .pst file problems >
Similar threads
Thread starter Title Forum Replies Date
S Macro to extract and modify links from emails Outlook VBA and Custom Forms 3
C Wishlist Extract or scan new email addresses from out of office replies. Leads from OOO replies Using Outlook 1
D ISOmacro to extract active mail senders name and email, CC, Subject line, and filename of attachments and import them into premade excel spread sheet Outlook VBA and Custom Forms 2
M Extract "Date sent" from emails (saved to folder using drag and drop) Outlook VBA and Custom Forms 1
T vba extract data from msg file as attachment file of mail message Outlook VBA and Custom Forms 1
S Unable to extract text from an Outlook email message Using Outlook 2
S Macro to extract email addresses of recipients in current drafted email and put into clipboard Outlook VBA and Custom Forms 2
C Macro to extract sender name & subject line of incoming emails to single txt file Outlook VBA and Custom Forms 3
N Extract Outlook emails to excel Outlook VBA and Custom Forms 2
T Extract Data From Outlook Tasks Using Outlook 0
T Extract Data From Outlook Tasks Using Outlook 0
V extract users of a particular department Outlook VBA and Custom Forms 1
J Outlook 2013 Extract Flag Completed dates to Excel Macro Outlook VBA and Custom Forms 16
S How to extract mail items from multiple folders and shared mailboxes? Outlook VBA and Custom Forms 0
K Extract email address from body and auto-reply outlook Using Outlook 1
R Trying to extract information between two symbols from outlook subject Using Outlook 2
K Extract email to excel from a specific sender Outlook VBA and Custom Forms 3
O VBA to extract email (fields and body) to Excel Outlook VBA and Custom Forms 14
P Recover / Extract Rules from standalone PST file creating RWZ file Using Outlook 2
B Extract Dates for Appointment Item in Body of email Outlook VBA and Custom Forms 10
D Need to extract a line from a word attachment, and add it to the subject line Outlook VBA and Custom Forms 3
E Extract excel files from outlook Outlook VBA and Custom Forms 2
K extract certain text from an Outlook Email Message Outlook VBA and Custom Forms 2
D VBA Script to extract text matching specific criteria Outlook VBA and Custom Forms 1
M Extract text in existing message body for use in newmail items Using Outlook 17
M HELP--Extract Data from 2003 outlook transfer to excel spreadsheet Using Outlook 1
M VBA Code to extract data from an Outlook Form Using Outlook 0
M Extract attachments with a script Using Outlook 0
M HELP - Can't open outlook... How can I extract my Emails that I had in folders Using Outlook 3
H Extract emails from Outlokk 2007 email body Using Outlook 0
K Extract Global Address List Using Outlook 1
R Saving Outlook Email As Text File Extract Outlook VBA and Custom Forms 2
N Programming to extract automatically extract attachments Outlook VBA and Custom Forms 3
S How to extract outlook calendar data. Outlook VBA and Custom Forms 3
? outlook attachment Extract File ??? Outlook VBA and Custom Forms 1
N How to extract date and time stamp from messsages Outlook VBA and Custom Forms 6
V Extract Subject,Sent From, Message from mailbox to Excel Outlook VBA and Custom Forms 5
S Automatically extract attachments? Outlook VBA and Custom Forms 1
I How to extract email addresses from TO or CC line of a particular email Outlook VBA and Custom Forms 2
L Location Unsafe Warning for Links Using Outlook 1
L Outlook attachments from OneDrive as links Using Outlook 0
A Links in email getting error message about group policy Using Outlook 4
J Outlook 365 Emails showing as links and text only Using Outlook 4
M Seeking PDF Recommendation for Outlook that retains links Using Outlook 7
e_a_g_l_e_p_i Outlook 2010 How to set default email address for website links Using Outlook 3
G Recurring tasks break links Outlook 2016 Using Outlook 5
G Detect Google Drive and OneDrive links in incoming mail Outlook VBA and Custom Forms 3
F How to create phone number as links in notes of Contacts Using Outlook 2
R New Links on Navigation Pane, How to Remove? Using Outlook 1
Diane Poremsky Using Outlook Links Using Outlook 0

Similar threads

Back
Top