How to export urls from email to excel and check the status of the url ?

showrya.krovvidi

New Member
Outlook version
Outlook 2016 64 bit
Email Account
Outlook.com (as MS Exchange)
Hi Everyone,

I have been searching for a solution since the past two weeks for one of my critical projects. We have configured emails to be triggered for multiple instances and we receive approximately 1000 email alerts a day stating the configured URLs are down. Most of them are false positives and it has become a tedious job for me to check all the urls manually. I am a novice in writing macros, but I have tried multiple ways for my problem and no luck.
I am searching for a macro that will open the first url from all the selected emails in an excel and then checks for the http status of the same and return the status in the excel. Getting all the first urls from a set of emails is the toughest part where I have been stuck since the past 2 weeks. Can anyone please let me know if this can be ever achieved through a macro. If yes, how?
Thanks in advance.
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
This is a macro i have for outlook - Open All Hyperlinks in an Outlook Email Message - it opens all links in a web browser.

If you just need to get content from the page or server - you can use an function instead - I use this to get values from a web page - but a similar method could get just the status.


Code:
Private Function HttpOpen(ByVal strArgURL As String, Optional ByVal strArgMethod As String)
       If Not Len(strArgMethod) > 0 Then strArgMethod = "GET" Else strArgMethod = UCase(strArgMethod)
    Dim oHttp As Object
    Dim strX As String
    Set oHttp = CreateObject("MSXML2.ServerXMLHttp")
        With oHttp
            .Open strArgMethod, strArgURL, False
            .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
            .send (strArgURL)
            strX = .responseText
            .abort
        End With
    HttpOpen = strX
    
End Function

I pass a url to this code in the macro to get the value in a specific element - i don't think i have this written up in an article (but probably should).

Code:
Dim doc As MSHTML.HTMLDocument
Dim table As MSHTML.HTMLTable

Set objHttp = CreateObject("MSXML2.ServerXMLHTTP")
objHttp.Open "GET", strURL, False
objHttp.send ""

Title = ""
Title = objHttp.responseText

Set doc = New MSHTML.HTMLDocument
    doc.Body.innerHTML = HttpOpen(strURL, "GET")

Dim aEle As HTMLDivElement
For Each aEle In doc.getElementsByClassName("stats-num-item")
 

showrya.krovvidi

New Member
Outlook version
Outlook 2016 64 bit
Email Account
Outlook.com (as MS Exchange)
This is a macro i have for outlook - Open All Hyperlinks in an Outlook Email Message - it opens all links in a web browser.

If you just need to get content from the page or server - you can use an function instead - I use this to get values from a web page - but a similar method could get just the status.
Hi Diane,

Greetings. Thank you for the quick response and helping with the above link. I have tried that previously and set it as to run as a script in the rules, but if something goes wrong with my monitoring beacon, 100s of emails are generated at once and all pile up in outlook and this script for which I have set rule for is trying to open all the emails at once and my system is get hung by that. So, all I need now is to extract all the first url all selected emails to an excel or to a text file. I have googled different ways and tried multiple scenarios but no luck.

I have tried the solution provided in How to Quickly Extract All URLs from an Outlook Email to a Text File - Data Recovery Blog , but am getting a bad file descriptior 52 error in this block;

Create a new text file
strTextFile = "C:\Hyperlinks (" & objMail.Subject & ").txt"
Set objFileSystem = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFileSystem.CreateTextFile(strTextFile, True)
objTextFile.WriteLine ("Extracted URLs:" & vbCrLf & vbCrLf)

also tried solution provided in How to Quickly Export All Hyperlinks in Multiple Outlook Emails to Excel - Data Recovery Blog , but this doesn't even run in system.
 

showrya.krovvidi

New Member
Outlook version
Outlook 2016 64 bit
Email Account
Outlook.com (as MS Exchange)
I pass a url to this code in the macro to get the value in a specific element - i don't think i have this written up in an article (but probably should).
Hi Daine, where do I need to put the piece of code? I tried running the script by editing the function and am getting an error as below;

"Wrong Number of arguments or invalid property assigned".


Could you please let me know how to extract the first url from all selected emails to excel?
 
Thread starter Similar threads Forum Replies Date
K Disabling import/export button to restrict PST creation Using Outlook 3
K Export Folder to PST Outlook VBA and Custom Forms 2
R Outlook 2010 How do you export 2 email Accounts' 2010 Inbox Files to new computer (2019)? Using Outlook 0
O Export Outlook calendar appointments by filters and date range Outlook VBA and Custom Forms 1
M Export-Import .pst file problems Using Outlook 2
M How to export Voting Results with user names and their responses Outlook VBA and Custom Forms 13
J Export Zimbra to Outlook PST Using Outlook.com accounts in Outlook 1
C Export NSF Lotus Notes files in Outlook PST format? Using Outlook 1
C How to export Outlook data to csv? Using Outlook 0
T How to Export mailbox from Outlook 2019 to MBOX format? Using Outlook 1
M Auto-export mail to Excel Outlook VBA and Custom Forms 2
P How to export voting results using VBA? Outlook VBA and Custom Forms 2
R How To Repair OST File & Export OST File to Outlook PST ? Using Outlook 3
JohnViuf Export task list view settings to other pc Outlook VBA and Custom Forms 16
J Copy or Export Outlook Mail to Excel Outlook VBA and Custom Forms 6
N Export details to a excel spreadsheet using macros Using Outlook 0
avant-guvnor Export Calendar, Birthdays and Holidays Outlook VBA and Custom Forms 0
Diane Poremsky Export (Save) Outlook Contact photos New Slipstick.com Articles 0
K Macro to search emails between two time slots on public folders with excel export Outlook VBA and Custom Forms 12
A How to export one email data to excel Using Outlook 1
B Macro To Create Rule To Export From Certain Folder Email Information in one workbook multiple sheets Outlook VBA and Custom Forms 0
W macro to export outlook emails to excel Outlook VBA and Custom Forms 6
E Exchanger service export detection Exchange Server Administration 1
R Correct a wrong csv export file from WLM 2012 Using Outlook 0
Q Outlook 2016\365 export specific rules to import in another system Exchange Server Administration 1
H How to export *all* fields from Outlook Using Outlook 2
K Export Organizational Forms Library Exchange Server Administration 1
N Export Outlook custom forms fields to excel Outlook VBA and Custom Forms 1
E RSS export as OPML problem Using Outlook 4
A export free / busy from exchange Exchange Server Administration 0
C Export to excel missing items, but a larger total? BCM (Business Contact Manager) 6
M Outlook 2013/365 won't export 99% of my contacts to csv Using Outlook 2
T Outlook 2007 Export tasks to Excel Using Outlook 2
J Opening/Archiving/Import/Export PST files Using Outlook 4
H Select one of Contact-Mailadesses to Export > Excel or Winword Outlook VBA and Custom Forms 2
Sarge USMC Cannot import/export Outlook 13 Using Outlook 6
L Outlook 2007 Codes Export and Import Using Outlook 9
L Outlook 2007 Toolbar Export and Import Using Outlook 7
S Requried a VBA Code to export Calander details to excel... Outlook VBA and Custom Forms 4
Jennifer Murphy Can I export individual emails? Using Outlook 1
R Export Address Book VBA Using Outlook 0
W How to export additional fields in contacts Using Outlook 1
R Recover/export rules from a backup .PST Using Outlook 2
J Formatting Contacts to Export for iPhone Using Outlook 0
B No export way Using Outlook 4
F Export Outlook contacts by One Category Using Outlook 1
Jennifer Murphy Difference between export and archive? Using Outlook 10
J How can I EXPORT my email address bok? Using Outlook 1
D Macro to export mail data based on assigned category Using Outlook 3
W calendar contacts export problem Using Outlook 1
Similar threads


















































Top