Outlook Emails To Access

[pjd218]

I have a large database with both contacts and projects. No problem emailing from Access (via Outlook) and attaching emails to contacts and projects. However,I also need to attach received emails in Outlook to individual contacts or projects in Access. The most desireable method would be to have a menu item in Outlook to perform this action or a command button on the contact/project form that would browse the various Outlook folders and allow selection of individual emails and either import these emails to the database or create a hyperlink back to the message.

 

[SeeThru]

You can link to outlook - there is a wizard add-on available for Acc97, and I think it is built in in later versions.

Specify the folder to link to, and you can see all the messages. As these are a linked table, you may want to copy them to a local table, related to the contact or project.

SeeThru
Synergy Connections Ltd - Telemarketing Services

http://www.synergyconnections.co.uk

 

[pjd218]

Importing the emails would be fine but we have many project folders in outlook and I need to automate the task. A naming convention of the ouloook folders to match the project names would simplify things but I need to make this idiot proof. Many contact management programs have the ability to attach outlook emails to contacts (Act, Maximizer, etc.)

 

[pjd218]

Using Access 2007. Any ideas on a macro or code to replicate the the link to Outlook folder function.

I need to import project specific emails and link them to a project. If I could do this from my project form, I could pass the project id to the table during import.

 

[nim180]

Hi pjd218 How are ya......

Try this its quite a long winded code but put it in a new module and name it "basOpenFile". Im not sure who wrote this code but it may work for you.

*****************************************************

' Declarations for Windows Common Dialogs procedures
Private Type CLTAPI_OPENFILE
strFilter As String ' Filter string
intFilterIndex As Long ' Initial Filter to display.
strInitialDir As String ' Initial directory for the dialog to open in.
strInitialFile As String ' Initial file name to populate the dialog with.
strDialogTitle As String ' Dialog title
strDefaultExtension As String ' Default extension to append to file if user didn't specify one.
lngFlags As Long ' Flags (see constant list) to be used.
strFullPathReturned As String ' Full path of file picked.
strFileNameReturned As String ' File name of file picked.
intFileOffset As Integer ' Offset in full path (strFullPathReturned) where the file name (strFileNameReturned) begins.
intFileExtension As Integer ' Offset in full path (strFullPathReturned) where the file extension begins.
End Type

Const ALLFILES = "All Files"

Private Type CLTAPI_WINOPENFILENAME
lStructSize As Long
hWndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustrFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
Flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustrData As Long
lpfnHook As Long
lpTemplateName As String
End Type

Const OFN_ALLOWMULTISELECT = &H200
Const OFN_CREATEPROMPT = &H2000
Const OFN_EXPLORER = &H80000
Const OFN_FILEMUSTEXIST = &H1000
Const OFN_HIDEREADONLY = &H4
Const OFN_NOCHANGEDIR = &H8
Const OFN_NODEREFERENCELINKS = &H100000
Const OFN_NONETWORKBUTTON = &H20000
Const OFN_NOREADONLYRETURN = &H8000
Const OFN_NOVALIDATE = &H100
Const OFN_OVERWRITEPROMPT = &H2
Const OFN_PATHMUSTEXIST = &H800
Const OFN_READONLY = &H1
Const OFN_SHOWHELP = &H10

Declare Function CLTAPI_GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" _
(pOpenfilename As CLTAPI_WINOPENFILENAME) _
As Boolean

Declare Function CLTAPI_GetSaveFileName Lib "comdlg32.dll" Alias "GetSaveFileNameA" _
(pOpenfilename As CLTAPI_WINOPENFILENAME) _
As Boolean

Declare Sub CLTAPI_ChooseColor Lib "msaccess.exe" Alias "#53" _
(ByVal hwnd As Long, rgb As Long)

Function GetOpenFile_CLT(strInitialDir As String, strTitle As String) As String


Dim fOK As Boolean
Dim typWinOpen As CLTAPI_WINOPENFILENAME
Dim typOpenFile As CLTAPI_OPENFILE
Dim strFilter As String

On Error GoTo PROC_ERR

' Set defaults for the structure
strFilter = CreateFilterString_CLT("All Files (*.*)", "*.*", "Database Files (*.MDB)", "*.MDB")

If strInitialDir <> "" Then
typOpenFile.strInitialDir = strInitialDir
Else
typOpenFile.strInitialDir = CurDir()
End If

If strTitle <> "" Then
typOpenFile.strDialogTitle = strTitle
End If

typOpenFile.strFilter = strFilter
typOpenFile.lngFlags = OFN_HIDEREADONLY Or OFN_SHOWHELP

' Convert the CLT structure to a Win structure
ConvertCLT2Win typOpenFile, typWinOpen

' Call the Common dialog
fOK = CLTAPI_GetOpenFileName(typWinOpen)

' Convert the Win structure back to a CLT structure
ConvertWin2CLT typWinOpen, typOpenFile

GetOpenFile_CLT = typOpenFile.strFullPathReturned

PROC_EXIT:
Exit Function

PROC_ERR:
GetOpenFile_CLT = ""
Resume PROC_EXIT

End Function

Sub ConvertCLT2Win(CLT_Struct As CLTAPI_OPENFILE, Win_Struct As CLTAPI_WINOPENFILENAME)
' Comments : Converts the passed CLTAPI structure to a Windows structure
' Parameters: CLT_Struct - record of type CLTAPI_OPENFILE
' Win_Struct - record of type CLTAPI_WINOPENFILENAME
' Returns : Nothing
'
Dim strFile As String * 512

On Error GoTo PROC_ERR

Win_Struct.hWndOwner = Application.hWndAccessApp
Win_Struct.hInstance = 0

If CLT_Struct.strFilter = "" Then
Win_Struct.lpstrFilter = ALLFILES & Chr$(0) & "*.*" & Chr$(0)
Else
Win_Struct.lpstrFilter = CLT_Struct.strFilter
End If
Win_Struct.nFilterIndex = CLT_Struct.intFilterIndex

Win_Struct.lpstrFile = String(512, 0)
Win_Struct.nMaxFile = 511

Win_Struct.lpstrFileTitle = String$(512, 0)
Win_Struct.nMaxFileTitle = 511

Win_Struct.lpstrTitle = CLT_Struct.strDialogTitle
Win_Struct.lpstrInitialDir = CLT_Struct.strInitialDir
Win_Struct.lpstrDefExt = CLT_Struct.strDefaultExtension

Win_Struct.Flags = CLT_Struct.lngFlags

Win_Struct.lStructSize = Len(Win_Struct)

PROC_EXIT:
Exit Sub

PROC_ERR:
Resume PROC_EXIT

End Sub

Sub ConvertWin2CLT(Win_Struct As CLTAPI_WINOPENFILENAME, CLT_Struct As CLTAPI_OPENFILE)
' Comments : Converts the passed CLTAPI structure to a Windows structure
' Parameters: Win_Struct - record of type CLTAPI_WINOPENFILENAME
' CLT_Struct - record of type CLTAPI_OPENFILE
' Returns : Nothing
'
On Error GoTo PROC_ERR

CLT_Struct.strFullPathReturned = Left(Win_Struct.lpstrFile, InStr(Win_Struct.lpstrFile, vbNullChar) - 1)
CLT_Struct.strFileNameReturned = RemoveNulls_CLT(Win_Struct.lpstrFileTitle)
CLT_Struct.intFileOffset = Win_Struct.nFileOffset
CLT_Struct.intFileExtension = Win_Struct.nFileExtension

PROC_EXIT:
Exit Sub

PROC_ERR:
Resume PROC_EXIT

End Sub

Function CreateFilterString_CLT(ParamArray varFilt() As Variant) As String
' Comments : Builds a Windows formatted filter string for "file type"
' Parameters: varFilter - parameter array in the format:
' Text, Filter, Text, Filter ...
' Such as:
' "All Files (*.*)", "*.*", "Text Files (*.TXT)", "*.TXT"
' Returns : windows formatted filter string
'
Dim strFilter As String
Dim intCounter As Integer
Dim intParamCount As Integer

On Error GoTo PROC_ERR

' Get the count of paramaters passed to the function
intParamCount = UBound(varFilt)

If (intParamCount <> -1) Then

' Count through each parameter
For intCounter = 0 To intParamCount
strFilter = strFilter & varFilt(intCounter) & Chr$(0)
Next

' Check for an even number of parameters
If (intParamCount Mod 2) = 0 Then
strFilter = strFilter & "*.*" & Chr$(0)
End If

End If

CreateFilterString_CLT = strFilter

PROC_EXIT:
Exit Function

PROC_ERR:
CreateFilterString_CLT = ""
Resume PROC_EXIT

End Function

Function RemoveNulls_CLT(strIn As String) As String
' Comments : Removes terminator from a string
' Parameters: strIn - string to modify
' Return : modified string
'
Dim intChr As Integer

intChr = InStr(strIn, Chr$(0))

If intChr > 0 Then
RemoveNulls_CLT = Left$(strIn, intChr - 1)
Else
RemoveNulls_CLT = strIn
End If

End Function

********************************************************

Once you have done that create a button and on the onclick event of the button place this code

Dim strFile As String

strFile = GetOpenFile_CLT("C:\localfiles", "Select a File To Attach") 'point the C:\ to whichever folder all the attachments in outlook is.

Me!TEXTBOX NAME = strFile '


Create a textbox so you can attach the path to point to which attachment you want. Replace TEXTBOX NAME with the name of your textbox. I dont know if this will help but give it a shot.

nim

 

[pjd218]

nim180,

The code worked great for picking a file from a location other than Outlook. Plan on saving and implementing later in the project. Thanks.

Still need to open the Outlook folders,browse to a particular folder, then link to that folder from my project database.

I found some code that works the other way (from Outlook) and implemented the browse function from Access but it uses a DSN to import the emails. I do not need to import the emails to the database per se, but a link to the project folder in Outlook is what I am after. Once the project is closed, the emails are converted to a PDF file, saved to a hard drive, and then I can link using your code, which is great.

Tks for the suggestion. Any others are appreciated.