[AccessD] Can I open outlook messages from Access?

Jim Lawrence accessd at shaw.ca
Mon May 11 00:37:57 CDT 2009


Hi Lonnie:

The piece of code is a little complex but will allow you to run a
application on the system... from Excel to Outlook

<Code>
Given in Public module:
' Code for handling the shell API code
Public Declare Function apiShellExecute Lib "shell32.dll" _
    Alias "ShellExecuteA" _
    (ByVal hWnd As Long, _
    ByVal lpOperation As String, _
    ByVal lpFile As String, _
    ByVal lpParameters As String, _
    ByVal lpDirectory As String, _
    ByVal nShowCmd As Long) _
    As Long

'***App Window Constants***
Public Const WIN_NORMAL = 1         'Open Normal
Public Const WIN_MAX = 3            'Open Maximized
Public Const WIN_MIN = 2            'Open Minimized

'***Error Codes***
Public Const ERROR_SUCCESS = 32&
Public Const ERROR_NO_ASSOC = 31&
Public Const ERROR_OUT_OF_MEM = 0&
Public Const ERROR_FILE_NOT_FOUND = 2&
Public Const ERROR_PATH_NOT_FOUND = 3&
Public Const ERROR_BAD_FORMAT = 11&

'...and a collection as follows:

Private Type ProjectDocument
    DefaultDir As String
    RawCodeGroup As String
    ID As Long
    Placement As String
    SubCategory As String
    CodeGroup As String
    FileName As String
    FileLocation As String
End Type

Dim tmpPD As ProjectDocument

'-----------------------------------------------------------
' a sample of how tmpPD the collection is filled
Private Sub Form_Open(Cancel As Integer)

    With tmpPD
        
        .RawCodeGroup = Forms![frm_Project]![txtFileFlag]
    
        If Len(.RawCodeGroup) > 0 Then
            ' set the default start search directory
            .DefaultDir = "\\bolt\S8627\S8627_SG_GPE_Share\\SYSTEMS+"
            
            ' get/set current record and potential new record
            ' defining code
            .CodeGroup = Trim(Forms![frm_Project]![txtFileFlag])
        
            ' Setup up access to database and recordset
            ' and get the criteria for the listbox
            If Initialize(.CodeGroup) = False Then Cancel = True
            ' repopulate list display
            lstFileNames.Requery
            
            setTitle	'Just sets header of form depending on option
        Else
            MsgBox "Form Project must currently" & vbCr & _
                    "active to supply seed information.", vbExclamation,
"Error..."
            Cancel = True
        End If
    End With
    
End Sub
 

'---------------------------------------------------------- 
Private Sub ViewCurrentFile()
     
    With tmpPD
        If Len(Dir(tmpPD.FileLocation)) > 0 Then
            DoCmd.Hourglass True
            ' View file and ignore any errors
            LoadFile tmpPD.FileLocation, WIN_NORMAL
            DoCmd.Hourglass False
        Else
            MsgBox "File " & .FileName & " could not be found in directory"
& vbCr & _
                    .DefaultDir & "." & vbCr & _
                    "File may have been moved or is otherwise unavailable
from this location.", vbInformation, "File not found"
        End If
    End With
    
End Sub

'------------------------------------------------------
Public Function LoadFile(stFile As String, lngShowHow As Long) As String
    
    Dim lRet As Long, varTaskID As Variant
    Dim stRet As String
    
    LoadFile = ""
    
    'First try ShellExecute
    lRet = apiShellExecute(hWndAccessApp, vbNullString, stFile,
vbNullString, vbNullString, lngShowHow)
            
    If lRet > ERROR_SUCCESS Then
        stRet = vbNullString
        lRet = -1
    Else
        Select Case lRet
            Case ERROR_NO_ASSOC:
                'Try the OpenWith dialog
                varTaskID = Shell("rundll32.exe shell32.dll,OpenAs_RunDLL "
_
                        & stFile, WIN_NORMAL)
                lRet = (varTaskID <> 0)
            Case ERROR_OUT_OF_MEM:
                stRet = "Error: Out of Memory/Resources. Couldn't Execute!"
            Case ERROR_FILE_NOT_FOUND:
                stRet = "Error: File not found.  Couldn't Execute!"
            Case ERROR_PATH_NOT_FOUND:
                stRet = "Error: Path not found. Couldn't Execute!"
            Case ERROR_BAD_FORMAT:
                stRet = "Error:  Bad File Format. Couldn't Execute!"
            Case Else:
        End Select
    End If
    
    LoadFile = lRet & IIf(stRet = "", vbNullString, ", " & stRet)
    
End Function
</code>

It might be over kill but it can be used to receive a variety of extensions.
This code was used in an accounting project where each account and invoice
item was lited in a sub form. By clicking on each entry listed in each row
the particular file would open with the associated application. Adobe for
PDF, Word for a document or Outlook if an email was selected.

I hope there is enough here to help you out.

Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Lonnie Johnson
Sent: Sunday, May 10, 2009 6:49 PM
To: AccessD solving'
Subject: [AccessD] Can I open outlook messages from Access?

I am creating an app for a friend. He wants to see his task, emails and
appointments in an Access form. I was able to do that.

What he wants to do now is double click on one of the emails in a continuous
form I build and the email actually opens. Does anyone have code for that? 
 
May God bless you beyond your imagination!
Lonnie Johnson
ProDev, Professional Development of MS Access Databases
Visit me at ==> http://www.prodev.us


      
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com





More information about the AccessD mailing list