Reading Email detail - Sender, Body into Access for parsing etc and use in tables. Win7/Access2016 and Win10/Access365 (1 Viewer)

Dave1975

Member
Local time
Today, 17:24
Joined
Oct 21, 2015
Messages
39
Morning All

I have been everywhere for months and cannot get to the bottom of the following problem

Scenrio: Currently get requirements from the business via a request in the shape of a table (excel) that is imported into an Access DB.
Our company is currently moving to win10/office 365 and i want to get our request system updated via small steps in the right direction.
I basically want requests to come in via email instead and an AccessDB will import these emails, parse them, then do as they need to similar to imported excel tables.

Other problem i have is - with moving to win10 - the code i write needs to work on win7/office 2016 and win10/office365.
The current code works fine in Access2016 on win7 but fails on win10/office365

current code (which is just test code to make sure the right data is being grabbed)

Code:
Private Sub Command4_Click()


Dim outlookApp As Outlook.Application
Dim OutlookNamespace As NameSpace
Dim Folder As MAPIFolder
Dim OutlookMail As Variant
Dim i As Integer

Set outlookApp = New Outlook.Application
Set OutlookNamespace = outlookApp.GetNamespace("MAPI")
Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox).Folders("Import")

i = 1

For Each OutlookMail In Folder.Items
       
        If OutlookMail.SenderEmailType = "SMTP" Then
            one = MsgBox(OutlookMail.Subject, vbOKOnly, "1")
            two = MsgBox(OutlookMail.ReceivedTime, vbOKOnly, "2")
            three = MsgBox(OutlookMail.SenderEmailAddress, vbOKOnly, "3")
            four = MsgBox(OutlookMail.Body, vbOKOnly, "4")
        Else
           
       
            one = MsgBox(OutlookMail.Subject, vbOKOnly, "1")
            two = MsgBox(OutlookMail.ReceivedTime, vbOKOnly, "2")
            three = MsgBox(OutlookMail.Sender.GetExchangeUser().PrimarySmtpAddress, vbOKOnly, "3")
            four = MsgBox(OutlookMail.Body, vbOKOnly, "4")
           
        End If
        i = i + 1
   
Next OutlookMail

Set Folder = Nothing
Set OutlookNamespace = Nothing
Set outlookApp = Nothing

End Sub

the code fails on the red lines.... the third msgbox has had soooo many different code types and references....

anyone able to point me in the right direction.
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:24
Joined
Sep 21, 2011
Messages
10,617
Sadly, when you use the code tags, no colours are shown. :(
You can just link the folder as well you know?, then work on the linked table?
 

Dave1975

Member
Local time
Today, 17:24
Joined
Oct 21, 2015
Messages
39
the lines in question - within the ELSE part of the statement
three = MsgBox(OutlookMail.Sender.GetExchangeUser().PrimarySmtpAddress, vbOKOnly, "3")
four = MsgBox(OutlookMail.Body, vbOKOnly, "4")

linking the folder - would this link work for other users - i wondered about this but thought it wouldnt work so avoided..?
the DB is used by multiple people and the IMPORT folder is/will be a shared mailbox...
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:24
Joined
Sep 21, 2011
Messages
10,617
TBH, I do not know, but I would have certainly tried it in the BE.?
You would still need someway of working out what had been processed and what had not?

You would need to Debug.print what those MSGBOX items are copy and paste here, and that would help others to help you.
I have not worked with Exchange Accounts, just my own Outlook for various reasons.
 

Dave1975

Member
Local time
Today, 17:24
Joined
Oct 21, 2015
Messages
39
cant do debug.print on this because i get error 287 when trying to run this code on access365/win10
first 2 lines work fine in both 2016 and 365 but then the senderemail and body refuse to run in 365/10


going to try linking the folder though and see if any benefit from that..
 

Dave1975

Member
Local time
Today, 17:24
Joined
Oct 21, 2015
Messages
39
ok - so linking folders worked but as you say i would need a mechanism to know when an email has been dealt with - might have some code that can shift the email out of the folder when 'imported'. As for whether other users can make use of the linked folder remains to be seen (awaiting their feedback)

still believe there must be a code option - some variable or descriptor i am missing...

I have seen a bit of talk about using CDO but that seemed to need hardcoded passwords which will be a no-no with my IT security
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:24
Joined
Sep 21, 2011
Messages
10,617
When I linked to an outlook folder, there were plenty of empty fields? If you populate one of them with the date completed, you could identify those not worked?
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:24
Joined
Sep 21, 2011
Messages
10,617
cant do debug.print on this because i get error 287 when trying to run this code on access365/win10
first 2 lines work fine in both 2016 and 365 but then the senderemail and body refuse to run in 365/10


going to try linking the folder though and see if any benefit from that..
Why does everyone think some people know the vba error codes to heart?😔 As an an amateur I certainly do not, and I would suspect the experts only know the common ones?

Walk through the code with F8 and report on which line the error occurs.
 

Dave1975

Member
Local time
Today, 17:24
Joined
Oct 21, 2015
Messages
39
top notch idea that - could use a flag - see how on off is interpreted by access and code accordingly. thanks for that - will update on my progress.

if anyone else knows of the VBA code solution - feel free to respond.
 

Dave1975

Member
Local time
Today, 17:24
Joined
Oct 21, 2015
Messages
39
Why does everyone think some people know the vba error codes to heart?😔 As an an amateur I certainly do not, and I would suspect the experts only know the common ones?

Walk through the code with F8 and report on which line the error occurs.
i didnt think that at all - and i stated which lines fail. that was in response to the debug.print request.
 

Dave1975

Member
Local time
Today, 17:24
Joined
Oct 21, 2015
Messages
39
wish access let me copy/paste the error description as is rather lengthy
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:24
Joined
Sep 21, 2011
Messages
10,617
That is why Debug.Print err would help.
I found it was something to do with html?
 

bastanu

AWF VIP
Local time
Today, 09:24
Joined
Apr 13, 2010
Messages
1,174
Please have a look at my free utility that was designed to do exactly what you're trying to achieve. It would loop through an Outlook folder, process each email in there and move it into a "Processed" subfolder of the same folder. Might need to adapt the code to deal with Exchange settings, but it should get you started:

Cheers,
Vlad
 

Users who are viewing this thread

Top Bottom