Checking Sender Email Address for trusted domain from list on intranet

leeford

New Member
Outlook version
Outlook 365 64 bit
Email Account
Exchange Server
My company has a list of "Trusted" domains that we are allowed to open links and attachments when we receive emails from these domains. If the email comes from outside our company, it is automatically get a highlighted phrase added to the body of the email to let us know it is from an outside source, but it also does this for the emails that come from the "Trusted" domains.
The "Trusted" domains are listed on our company's intranet. I would like to grab the domain of the sender's email address and then search the internal web page to see if the domain is found on that page and if it is, add a category to the email as "Trusted".

I'm not a great coder and primarily just hack through examples that I find on the web, but I haven't been able to find anything that does all of these things for me.

This is what I have so far.

Code:
Sub GoToEdge()
Dim objMail As Outlook.MailItem
Dim senderaddress As String
Dim senderdomain As String
Dim addresslen As Integer


' Sender E=mail Address
senderaddress = objItem.SenderEmailAddress

'Searches for @ in the email address and gets the domain
addresslen = Len(senderaddress)
senderdomain = Right(senderaddress, addresslen - InStr(senderaddress, "@"))


'open website'
CreateObject("Shell.Application").ShellExecute "microsoft-edge:https://internalsitepage.com/Trusted.aspx"

'search page for senderdomain'
End Sub

Any assistance you could provide would be greatly appreciated.
Thank you in advance!
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
How often does the list change? Saving the list as a text file and testing against the file would be easier ((IMHO) than pulling the webpage... but it is possible. Works best if the list is structured so you can look for a value - class name, div name etc.

I use this function

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


To look for the value in an element identified by a CSS class - in my example, I read it into a spreadsheet, but you could load it into an array or write it to a file - if you write to a text you could run a separate macro every day to replace the text file. The read the text file using your macro above.


Code:
Set objHttp = CreateObject("MSXML2.ServerXMLHTTP")
objHttp.Open "GET", "https://url.com/page", False
objHttp.send ""

Title = ""
Title = objHttp.responseText

Set doc = New MSHTML.HTMLDocument
    doc.Body.innerHTML = HttpOpen( "https://url.com/page", "GET")
      
i = 1
Range(useCol & newrow).Value = Now
For Each aEle In doc.getElementsByClassName("user-profile-stats-num-item")
Range(useCol & newrow).Offset(, i).Value = aEle.innerText
i = i + 1
If i = 4 Then GoTo NextCell

Next
 

leeford

New Member
Outlook version
Outlook 365 64 bit
Email Account
Exchange Server
How often does the list change? Saving the list as a text file and testing against the file would be easier ((IMHO) than pulling the webpage... but it is possible. Works best if the list is structured so you can look for a value - class name, div name etc.
Diane,

Thank you for your quick response. TBH, I'm not sure how often the list gets updated. I like the approach you have from above. Coming at it from that angle, I don't think we really care about the excess information that is provided with the domain such as the company it belongs to and the reason why we use it, etc. We really only care to read the web page and pull out all of the domains that are listed on the page. I really like your approach because we could easily read the webpage and create the list, then search the list in a much more structured way.

Am I on the right track for pulling the domain from the email address in the mail item?
I was thinking that I could simplify it and just create a macro that would be assigned to a button, then I click the button when I am on the mail item to go and check if it is safe or not. I could skip any automation of adding a category and everything.
Thoughts?
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
'Searches for @ in the email address and gets the domain
addresslen = Len(senderaddress)
senderdomain = Right(senderaddress, addresslen - InStr(senderaddress, "@"))

At most it might get 1 character too much or two little, but yeah, its correct.


And yes, you could run the macro manually on the messages.

Look at the HTML source in the web page - if the domains have css classes or other structure, it will be easy to pull them out. If it's basically one big paragraph, it will be harder.
 

leeford

New Member
Outlook version
Outlook 365 64 bit
Email Account
Exchange Server
'Searches for @ in the email address and gets the domain
addresslen = Len(senderaddress)
senderdomain = Right(senderaddress, addresslen - InStr(senderaddress, "@"))

At most it might get 1 character too much or two little, but yeah, its correct.


And yes, you could run the macro manually on the messages.

Look at the HTML source in the web page - if the domains have css classes or other structure, it will be easy to pull them out. If it's basically one big paragraph, it will be harder.

The web page that has the list has the data stored in a Table, so it does have the basic structure as follows:
<table><tbody><tr><td></td></tr></tbody></table>

There are 5 columns in the table and I want the information in the 2nd column.

I've tried to pull the information out and I got pretty close, but am not successful yet, using your snippets.
Thanks again for your assistance.
 
Similar threads
Thread starter Title Forum Replies Date
V Checking for empty field Outlook VBA and Custom Forms 2
S Problem Checking the available stores in my Inbox (Outlook VBA) Outlook VBA and Custom Forms 0
M Account sequence when checking mail Using Outlook 6
T Checking mailbox emails Outlook VBA and Custom Forms 0
B Auto name checking upon Tab or Click off To field(s) Using Outlook 19
S Checking attachment is passward protected or not before sending the mail Using Outlook 1
S Outlook Macro for checking the mail is sent to outside organization Using Outlook 3
J Checking PST File Keeps Crashing Using Outlook 2
L Script to: Change subject of email after checking body Using Outlook 2
H Redirection checking Exchange Server Administration 10
S Stop mail checking Using Outlook 5
B Too many ' tasks' when checking email Using Outlook 1
B Checking attachments as they arrive Outlook VBA and Custom Forms 1
F Forward incoming email with 4 embedded images in the body without original sender Outlook VBA and Custom Forms 22
R Capture Sender's Display name and Address Outlook VBA and Custom Forms 3
T Junk Email does not get added to the Blocked Sender List Using Outlook 0
M How create a Rule to filter sender's email with more that one @ sign Using Outlook 1
F Junk Email does not get added to the Blocked Sender List Using Outlook 4
U Outlook 2016 Outlook 2016 sender name Using Outlook 1
C Macro to extract sender name & subject line of incoming emails to single txt file Outlook VBA and Custom Forms 3
Terry Sullivan Sender's Name Doesn't Appear in the From Field on Outlook 365/IMAP Using Outlook 2
K Use VBA to find Sender and Recipient from Microsfot 365 Journaled Email Items Outlook VBA and Custom Forms 3
Terry Sullivan Sender Field Displays My E-Mail Address, Not My Name Using Outlook 1
J Autoreply email recieved from specific sender after deleting some text from body. Using Outlook 0
D Reply with a template loose the sender's embedded image Outlook VBA and Custom Forms 0
J Outlook 2016 How tell Outlook not to trust sender? Using Outlook 1
M Screen Scrape Sender IP Address Outlook VBA and Custom Forms 6
S Meeting Invite arrives from Wrong ("send-as") Sender Using Outlook 1
icacream Rule based on sender / wrong sender sent to folder Using Outlook 7
D Moving Emails Based on Recipient/Sender Outlook VBA and Custom Forms 4
B Looking to filter (or just find/search) for only messages that the sender has sent more than 1 messa Using Outlook 2
I How to display sender's name instead of email address in outlook 2013 message Using Outlook 5
A Sort emails into subfolders based on sender and deleting emails automatically Outlook VBA and Custom Forms 3
C Display Sender As Contact Outlook VBA and Custom Forms 4
Ed Sheehan Unusual behaviour in setting Sender (Outlook 2016) Outlook VBA and Custom Forms 4
J Auto Forward - Include Attachment and change Subject depending on original sender Outlook VBA and Custom Forms 3
Q Undisclosed recipients does not include sender Using Outlook 1
M Move new mail to folder based on sender address Outlook VBA and Custom Forms 2
D Inserting sender name and address with vba Outlook VBA and Custom Forms 1
M Blocked Sender List @Domain Not Working Using Outlook 0
P MS OUTLOOK 2013 - Adding Sender on the CC line Using Outlook 5
J Saving attachments from specific sender (phone number) to specific folder on hard drive Using Outlook 3
B Can't expose sender email address when linking outlook to access Using Outlook 3
Diane Poremsky Use VBA to create an Outlook Search Folder for Sender Using Outlook 0
K Extract email to excel from a specific sender Outlook VBA and Custom Forms 3
D Change sender name outlook vba 2010 Custom Userform Outlook VBA and Custom Forms 1
O See recent emails from a sender Using Outlook 2
S announce email sender and subject when new email arrives... Using Outlook 7
K check for sender, follow to my personal adress and delete the sent folder. Outlook VBA and Custom Forms 1
P Mail delivery failed returning message to sender Using Outlook 1

Similar threads

Top