We help IT Professionals succeed at work.

Check for emails from a particular domain in outlook via VBA - MS ACCESS

caandal
caandal asked
on
216 Views
Last Modified: 2017-04-14
Hi Experts

I have a client who receives orders via email in outlook.  Because of the size of the client (i.e. many different divisions ) there is no set template or anything that gets used to request and order (the client is not interested in changing the way that they work) Is there anyway that I can retrieve the mail header / subject line into a table in VBA or SQL and then set a trigger for it to be processed?

Many thanks
Alan
Comment
Watch Question

Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
What are you using to interact with Outlook?

Jim.
In Access: use External data \ More \ Outlook Folder.
then use a table data-macro or timer in Access to process new records.

In Outlook: code around the Application / ItemAdd event
e.g.
In this Visual Basic for Applications (VBA) example, when a new contact is added to the Contacts folder, the contact item is attached to an e-mail message and sent to a distribution list named "Sales Team". The sample code must be placed in a class module, and the Initialize_handler routine must be called before the event procedure can be called by Microsoft Outlook.

Public WithEvents myOlItems As Outlook.Items

 
Public Sub Initialize_handler()
 Set myOlItems = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderContacts).Items
End Sub
 
Private Sub myOlItems_ItemAdd(ByVal Item As Object)
 Dim myOlMItem As Outlook.MailItem
 Dim myOlAtts As Outlook.Attachments
 Set myOlMItem = myOlApp.CreateItem(olMailItem)
 myOlMItem.Save
 Set myOlAtts = myOlMItem.Attachments
 ' Add new contact to attachments in mail message
 myOlAtts.Add Item, olByValue
 myOlMItem.To = "Sales Team"
 myOlMItem.Subject = "New contact"
 myOlMItem.Send
End Sub

Author

Commented:
Hi Jim

It is an Access 2016 front end with a SQL backend
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
What I meant was how you were tied to Outlook; using CDO, vbMAPI, Outlook Redemption, or just talking straight to Outlook with OLE and dealing with the security warnings.

As Coachman showed, you can work with Outlook directly, but most use a 3rd party product (which have their own object models) to avoid security warnings.

Jim.

Author

Commented:
Hi Jim
At this stage nothing is in place - it would be relatively easy using a direct link to an outlook folder with OLE.  The issue that I have is retrieving attachments with the mails
President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Many thanks Jim - all looks good - your solutions have always worked for me in the past
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.