Outlook vba updating access table

Private Sub Ennumerate Folders(By Ref o Fld As Object, By Val l Fld Num As Long) On Error Resume Next Dim o Fld As Object Dim str Ins As String Dim str SQL As String Dim arr Flds() As String Dim int Dep As Integer Dim str Fld As String Dim int Cnt As Integer The code is pretty straight-forward but a few notes of clarification. As you might expect, the Outlook Security model doesn’t like it when code does this either, so again your users will get a security dialog asking for permission to proceed. Execute s SQL Next Next End Function The best way to understand this process is to run the code in break mode, stepping through it one line at a time.

I perform a REPLACE() on the folder name to replace single quotes with a pair of single quotes. It seems like the Redemption library should allow a way to circumvent this, but my initial attempts proved unsuccessful. This will allow you to see how the text strings are parsed and to identify any issues that might arise which are peculiar to your mail setup.

This is all accomplished in the function named Get MAPISubfolders() which accepts a long integer value to identify which of our two top folders to process. The Enumerate Folders() function, when it encounters a subfolder, calls the Enumerate Folders() function, which in turn calls the Enumerate Folders() function on it’s subfolders and the process continues in recursion until there are no more subfolders. Doug was having some issues with the code but I got it working and the final result is in the download for this article.

Each time it finds a folder, it writes the metadata to a table, which ends up as the source of the listbox shown above in the demo form. The function, Dist List Peek(), peeks into all the distribution lists and allows you to extract the names and email addresses. Type s Name = Trim(Replace(s Name, "(" & s Email & ")", "")) If s Name = "" Then s Name = s Email s List = Replace(s List, "'", "''") s Type = Replace(s Type, "'", "''") s Name = Replace(s Name, "'", "''") s SQL = "INSERT INTO tbl Contacts " & _ "(List Name, List Type, Position, Name, Email) " & _ "VALUES ('" & s List & "','" & _ s Type & "'," & i Pos & ",'" & _ s Name& "','" & s Email & "')" Current Db.

Since it had already been rolled out, I didn’t try to go back and correct it. The first step is to give the user an option for which mail folders they wish to process.

To keep the demo simple only the Inbox, Sent Mail and their subfolders will be considered.

One final point: The code in the article has been modified to avoid line breaks where possible and will not exactly correspond to that found in the demo code.

I also noticed that some of my comment lines in the demo code were nonsense.

outlook vba updating access table-89

Below are the results from a scan of my Outlook folders where my subfolders go only 1 level deep below the Inbox and Sent Items.The demo application includes the code for processing selected rows of the list box and it’s basic stuff, so I won’t reproduce that here but the function it calls to process all emails for a named folder is shown below. What’s important to note here is that the variables l Folder and s Folder are passed to this function. The only other thing that might not be obvious is the int Dep variable. I found that some text parsing and cleanup had to be done to get a good list and once again, this extent cleanup may depend on the quality of your address lists. Get Namespace("MAPI") s SQL = "DELETE FROM tbl Contacts" Current Db. The code supplied with this article should be an adequate starting point for all your Outlook to Access automation projects.It represents the “depth” of the subfolder. Sender Email Address str Recip = "" For int Recip = 1 To . Dim s SQL As String Dim i Pos As Integer Dim s List As String Dim s Name As String Dim s Type As String Dim s Email As String Set o Out = Get Outlook Object() Set o NS = o Out.

Leave a Reply