Solved

Send Email subject line to MS Access table

Posted on 2015-01-05
22
415 Views
Last Modified: 2015-01-11
Hi, I have a routine which processes emails from MS Outlook, moving PDF attachments, renaming them and then moving the emails to a 'processed' folder.

Due to some network restrictions I cannot link the inbox to the Access database but want to capture the subject line in an table, for reconciliation purposes (against the processed PDF files).

Is there a VBA method for capturing the subject line for each of the selected emails, and sending it to a table, possibly called tbl_email_reconciliation?

I've been struggling with this for some time and would appreciate any help you can offer.

Thanks, Jon
0
Comment
Question by:jonlake
  • 8
  • 6
  • 5
  • +1
22 Comments
 
LVL 18

Assisted Solution

by:SimonAdept
SimonAdept earned 200 total points
ID: 40532521
You want the mailItem.subject property.
Is your routine running in Outlook VBA or Access VBA? If you are already making an OLE connection to Outlook to loop through folder contents you can read the subject line while in the loop and write it to a recordset.

pseudo code, assuming you have already set up a table called tblEmailDetails:
dim rs as DAO.recordset
set rs = currentdb.openrecordset("tblEmailDetails")
for each i in oFolder.mailItems
  'Do your PDF attachment processing
  mySubject=i.subject
  rs.addnew
  rs!subject = mySubject
  rs.update
next i
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40532541
Hi, I have a routine which processes emails from MS Outlook, moving PDF attachments, renaming them...
That sounds like you have the main part already flanged up.
As @SimonAdept says, the MailItem -- which you already have a handle on -- has a .Subject property you can use (it's a string with the subject in it)
Heck .Body is the whole email message body
There's a collection of .Recepients that can be parsed.
There will be a property for DateReceived, but its exact name I don't recall off the top of my head.

More than one email may have the same subject.
May as well snag enough data to be able to uniquely identify it!

Post some code!
We'll suggest the required additions
0
 

Author Comment

by:jonlake
ID: 40532915
Hi, the routine runs in Outlook. For reasons too dull to explain I can't link to the inbox form Access. I need to run the routine to capture the subject (and date received.....forgot that one), and add it to the table.
0
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40533007
OK, if the code is running in outlook, you would need to create an Access object in code, then a recordset within it and write the subject and date (and unique message ID?) to it.

It would be most useful if you could post your code routine so that we can advise on appropriate modifications to make it do what you want.
0
 
LVL 84
ID: 40533656
you would need to create an Access object in code,
To be clear, you don't need to create an Access Object. You can use the code just like SimonAdept posted in the first comment, except you'd have to use this when creating the recordset:

dim db As DAO.Database
Set db = DAO.OpenRecordset("path to your database")
dim rs as DAO.recordset
set rs = db.openrecordset("tblEmailDetails")

In fact, you really don't even need to open a Recordset. You can use straight SQL:

dim db As DAO.Database
Set db = DAO.OpenRecordset("path to your database")

for each i in oFolder.mailItems
  db.Execute "UPDATE YourEmailTable SET Subject='" & i.Subject & "', i.DateReceived='" & i.Date & "' WHERE IDFIeld=" & SomeIDVAlue
next i

I don't know if "DateReceived" is a valid MailItem property or not. You'd have to look through those to determine what exactly to use.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40533690
@Scott
Hi, the routine runs in Outlook.

If the code is in Outlook then some connection to an Access application is gonna be needful, isn't it? *Edit, no it appears not*
Or at least a reference in Outlook? *Edit, a reference to MS DAO 3.6 object library is needful unless some type of late binding syntax is used*
Without a reference there's no Intellisense support for DAO
And did you mean
Set db = DAO.OpenDatabase("path to your database")
*Edit -- very likely!*

.ReceivedTime is the syntax for when Outlook got the message

@Scott
Very neat!

Nick67
0
 
LVL 84
ID: 40533753
If the code is in Outlook then some connection to an Access application is gonna be needful, isn't it?
You don't need to connect to an Access Application to update a JET/ACE database. You can do that with DAO, ADO, etc. JET/ACE works best with DAO, so it's just easier to use DAO.

You would, of course, need a reference to DAO (or use Late Binding). For ACE database, you would use the Access Database Engine reference. For earlier, you would use 3.6 or whatever is on your machine.

Set db = DAO.OpenDatabase("path to your database")
Yes, thanks for catching that. It should be "DAO.OpenDatabase"
0
 

Author Comment

by:jonlake
ID: 40535566
I'm sorry but I'm not following. I want to place the code as a macro, accessible by clicking a button on the ribbon. The routine would send the subject line and date received to a table within an Access DB. I can't link the Outlook folder from Access.

Sorry for my ignorance, but as always, any help gratefully received.
0
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40535585
Do you already have a button on the ribbon that runs the " routine which processes emails from MS Outlook, moving PDF attachments, renaming them and then moving the emails to a 'processed' folder."?

My suggestion above was to add the functionality of writing the subject line (and any other attributes) of the email to your Access database at the same time, by adding a few lines within the processing loop.

If you can post your existing routine's code, we can suggest how to modify it to store the data to Access.
0
 
LVL 84
ID: 40535948
I don't think you can do this entirely in a Macro. Your macro would have to call a VBA routine, which connected to the Access database and ran code as suggested above.
0
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40535993
terminology clarification: jonlake, when you mention macro do you mean VBAor embedded Macro?
The only way I know to have code in Outlook is VBA code attached to a button in a custom group on the developer tab of the ribbon.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:jonlake
ID: 40536275
Sorry, this would have helped you earlier, here is my current code which I run in Outlook:

Public Sub SaveAttachments()
Dim objOL As Outlook.Application
Dim objMsg As Outlook.MailItem
Dim objAttachments As Outlook.Attachments
Dim objSelection As Outlook.Selection
Dim i As Long
Dim lngCount As Long
Dim strFile As String
Dim strFolderPath As String
Dim strDeletedFiles As String
Dim strName As String
Dim objNS As NameSpace
Dim objFolder As Folder

Dim objSubject As String

' select a path/folder to save the attachments to
strFolderPath = "Y:\Contact Centre\04 - Main\14 - DVLA\_system\01_holding_folder"

' initiate an Outlook Application object.
Set objOL = CreateObject("Outlook.Application")

' get the collection of selected objects.
Set objSelection = objOL.ActiveExplorer.Selection

' set the Attachment folder.
strFolderPath = strFolderPath & "\"

' check each selected item for attachments. If attachments exist,
' save them to the strFolderPath folder and strip them from the item.
For Each objMsg In objSelection

'Set FileName to Subject
objSubject = objMsg.Subject

' get the Attachments collection of the item.
Set objAttachments = objMsg.Attachments
lngCount = objAttachments.Count
strDeletedFiles = ""

If lngCount > 0 Then

' use a count down loop for removing items
' from a collection; otherwise, the loop counter gets
' confused and only every other item is removed.

For i = lngCount To 1 Step -1

    'capture the senders name or email address
    strName = Format(objMsg.ReceivedTime, "yymmdd") & "_" & Format(objMsg.ReceivedTime, "hhmmss")
   
    ' create the file name.
    'strFile = objSubject & "_" & Replace(objAttachments.Item(i).FileName, ".pdf", "") & ".pdf"
    strFile = Split(objSubject, "=")(UBound(Split(objSubject, "="))) & "_" & Replace(objAttachments.Item(i).FileName, ".pdf", "") & ".pdf"

    ' combine with the path to the folder.
    strFile = strFolderPath & strFile

    ' save the attachment as a file.
    objAttachments.Item(i).SaveAsFile strFile

    ' delete the attachment.
    'objAttachments.Item(i).Delete

    'write the save as path to a string to add to the message
    'check for html and use html tags in link
    If objMsg.BodyFormat <> olFormatHTML Then
        strDeletedFiles = strDeletedFiles & vbCrLf & "<file://" & strFile & ">"
        Else
        strDeletedFiles = strDeletedFiles & "<br>" & "<a href='file://" & _
        strFile & "'>" & strFile & "</a>"
    End If
   
Next i

' adds the filename string to the message body and save it
' check for HTML body
If objMsg.BodyFormat <> olFormatHTML Then
    'objMsg.Body = vbCrLf & "The file(s) were saved to " & strDeletedFiles & vbCrLf & objMsg.Body
Else
    objMsg.HTMLBody = "<p>" & "The file(s) were saved to " & strDeletedFiles & "</p>" & objMsg.HTMLBody
End If
    objMsg.Save
End If

'Mark the email as read
objMsg.UnRead = False

Next

ExitSub:

'Select Outlook folder to store message(s)

    Set objNS = Application.GetNamespace("MAPI")
    Set objFolder = objNS.PickFolder
    For Each objMsg In objSelection
    objMsg.Move objFolder
   
Next

Set objAttachments = Nothing
Set objMsg = Nothing
Set objSelection = Nothing
Set objOL = Nothing

End Sub
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 300 total points
ID: 40536331
So, here
   'capture the senders name or email address
     strName = Format(objMsg.ReceivedTime, "yymmdd") & "_" & Format(objMsg.ReceivedTime, "hhmmss")

you aren't doing what the comment says.
This is using the ReceivedTime to create a filename

Still, this is your baby objMsg
It is the MailItem and it has all the stuff you want
Now, you want to stuff .Subject (and maybe some other things too?) into tbl_email_reconciliation
You haven't told us the name or path of your Access database but now, per @Scott McDaniel (and me!) we'll get that flanged up

Up top in your code, we'll need some objects to be able to access your database
Dim Engine as Object
Set Engine = CreateObject("DAO.DBEngine.36") 'assuming an mdb file.  
'.accdb files need to be "DAO.DBEngine.120"
Dim db as Object
Dim rs as Object
Set db = Engine.OpenDatabase("FullPathToYourDatabase") ' c:\temp\mydb.mdb for example
Set rs = db.OpenRecordset("select * from tbl_email_reconciliation where 1=2;",dbOpenDynaSet)


That gets your database on the go and ready.
Now, back in the area of the first snippet I quoted (in your loop of objMsg handling, you'll want
With rs
    .AddNew
    !WhateverFieldYouAreSavingTheSubjectIn = objMsg.Subject
    'anything additional you plan on doing besides the subject gets done here
    .Update
End With


At the end of the code, cleaning up your objects
rs.close
set rs = nothing
db.close
set db = nothing
set engine = nothing


Make sense?
0
 

Author Comment

by:jonlake
ID: 40538379
That maske sense but I'm getting an error on:

Set rs = db.OpenRecordset("select * from tbl_email_reconciliation where 1=2;",dbOpenDynaSet)

I've double checked the db location, table name and field name and can't see a mismatch. I'm unsure what the 'where 1=2' part does.
0
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40538388
The 1=2 is just a way of providing a recordset with no records in (because 1 will never equal 2). It means you get the table structure and can add new records but don't waste bandwidth pulling existing records across the LAN.

It would be most helpful if you could specify the error number and description.
0
 

Author Comment

by:jonlake
ID: 40538401
Oops, I forgot the references...

I'm using Outlook 2010 and can't immediately locate the DAO.DBEngine.120. Is it referred to as something else possibly?
0
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40538414
In VBE/Tools/References, it will be listed as Microsoft DAO 3.6 Object library.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40538430
And if you add the reference, then the code can change.
I tried to code it for late binding.
If you create a reference that won't be needful
This bit
Dim Engine as Object
 Set Engine = CreateObject("DAO.DBEngine.36") 'assuming an mdb file.  
 '.accdb files need to be "DAO.DBEngine.120"
 Dim db as Object
 Dim rs as Object
 Set db = Engine.OpenDatabase("FullPathToYourDatabase") ' c:\temp\mydb.mdb for example
 Set rs = db.OpenRecordset("select * from tbl_email_reconciliation where 1=2;",dbOpenDynaSet)

will become
Dim db as DAO.database
 Dim rs as DAO.Recordset
 Set db = DAO.OpenDatabase("FullPathToYourDatabase") ' c:\temp\mydb.mdb for example
 Set rs = db.OpenRecordset("select * from tbl_email_reconciliation where 1=2;",dbOpenDynaSet)
0
 

Author Comment

by:jonlake
ID: 40538638
Got it, and it works beautifully! Fantastic effort, thank you for curing a rather large headache.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40538676
@jonlake
I always firmly maintain that the Asker should assign points based solely on their own discretion.
Still, it was a joint effort. @Simon and @Scott knew that you could get it done without an Access.Application object.
I didn't know that before this Q.
And the final result does owe something to their contributions, too.

If you wish to re-consider -- and that is solely at your discretion -- up at the top there is a Request Attention link (CTRL-F and 'request attention' will help you find it if you miss it!) that you can request to re-open the Q to re-allocate points.

Nick67
0
 

Author Comment

by:jonlake
ID: 40540911
Absolutely! My enthusiasm to put this into action go the better of me, and I'll fix it.
0
 

Author Closing Comment

by:jonlake
ID: 40543056
Hopefully that worked to update the points, both exceedingly helpful, thanks again.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now