none
How to set defalut email "client" in Access / is it possible to have both Outlook and CDO mail in Report Preview right click RRS feed

  • Question

  • Hi

    I understand that Outlook is the defalut email client in Access. But if I want to change that?

    What I need is a way to send CDO emails via right click on an Access Report Preview. Is it possible?

    And further. Is it possible to have both options (Outlook and CDO) when right clicking a Report in Preview mode?


    Cheers // Peter Forss Stockholm

    Friday, October 11, 2019 11:20 AM

Answers

  • Hi Peter,

    As pointed out to you, not possible due to the fact that the Print Preview is a built-in toolbar/menu.

    The only way is to create a Ribbon for that Report.

    Good and Bad news.....

    The good news is that you can create a custom menu for the Report Print Preview.

    The bad news is that you cannot remove once you created it.

    Sub AddCustomMenuToReport()
    Dim NewItem  As Object
      Set NewItem = CommandBars("Print Preview Popup").Controls.Add(Type:=1)
      With NewItem
        .BeginGroup = True
        .Caption = "My CDO Email"   'your email???  I mean name what you want it
        .FaceId = 0
        .OnAction = "TestRoutine"   'run your function/sub?
      End With
    End Sub

    The code only need to run once.

    So, do it with caution for your FE.

    I have no idea how to remove that custom added command-menu. Once added, it becomes part of "Print Preview Popup" context menu. "Print Preview Popup" cannot be deleted.

    However, like Ribbons, you can create a custom toolbar(the old method).

    PS: Affect all Reports/Forms/etc in Print Preview Mode --- do with caution.

    HTH.



    • Edited by AccessVandal Wednesday, October 16, 2019 9:06 AM add
    • Marked as answer by ForssPeterNova Wednesday, October 16, 2019 9:14 AM
    Wednesday, October 16, 2019 9:02 AM
  • Hi Peter,

    It appears that it can be deleted. No harm to Windows or Access. The only problem is that you'll have to reset your Toolbars and Custom toolbars.

    Real tricky to deploy since you don't know the name of the subkey as it will be random. But you still can read it with an API to search the name of the menu.

    You can start the FE with the code and delete the menu on your FE during on Close or Exit of the database.

    After some time searching.....I came up with this.

    Option Compare Database
    Option Explicit
    
    Const ERROR_NO_MORE_ITEMS = 259&
    Const HKEY_CURRENT_CONFIG = &H80000005
    Const HKEY_LOCAL_MACHINE = &H80000002
    
    Const HKEY_CURRENT_USER = &H80000001
    Const REG_OPTION_BACKUP_RESTORE = 4     ' open for backup or restore
    Const REG_OPTION_VOLATILE = 1           ' Key is not preserved when system is rebooted
    Const REG_OPTION_NON_VOLATILE = 0       ' Key is preserved when system is rebooted
    Const STANDARD_RIGHTS_ALL = &H1F0000
    Const SYNCHRONIZE = &H100000
    Const READ_CONTROL = &H20000
    Const STANDARD_RIGHTS_READ = (READ_CONTROL)
    Const STANDARD_RIGHTS_WRITE = (READ_CONTROL)
    Const KEY_CREATE_LINK = &H20
    Const KEY_CREATE_SUB_KEY = &H4
    Const KEY_ENUMERATE_SUB_KEYS = &H8
    Const KEY_NOTIFY = &H10
    Const KEY_QUERY_VALUE = &H1
    Const KEY_SET_VALUE = &H2
    Const KEY_READ = ((STANDARD_RIGHTS_READ Or KEY_QUERY_VALUE Or KEY_ENUMERATE_SUB_KEYS Or KEY_NOTIFY) And (Not SYNCHRONIZE))
    Const KEY_WRITE = ((STANDARD_RIGHTS_WRITE Or KEY_SET_VALUE Or KEY_CREATE_SUB_KEY) And (Not SYNCHRONIZE))
    Const KEY_EXECUTE = (KEY_READ)
    Const KEY_ALL_ACCESS = ((STANDARD_RIGHTS_ALL Or KEY_QUERY_VALUE Or KEY_SET_VALUE Or KEY_CREATE_SUB_KEY Or KEY_ENUMERATE_SUB_KEYS Or KEY_NOTIFY Or KEY_CREATE_LINK) And (Not SYNCHRONIZE))
    
    Private Declare Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As Long) As Long
    Private Declare Function RegOpenKey Lib "advapi32.dll" Alias "RegOpenKeyA" (ByVal hKey As Long, ByVal lpSubKey As String, phkResult As Long) As Long
    Private Declare Function RegEnumKeyEx Lib "advapi32.dll" Alias "RegEnumKeyExA" (ByVal hKey As Long, ByVal dwIndex As Long, ByVal lpName As String, lpcbName As Long, ByVal lpReserved As Long, ByVal lpClass As String, lpcbClass As Long, lpftLastWriteTime As Any) As Long
    Private Declare Function RegEnumValue Lib "advapi32.dll" Alias "RegEnumValueA" (ByVal hKey As Long, ByVal dwIndex As Long, ByVal lpValueName As String, lpcbValueName As Long, ByVal lpReserved As Long, lpType As Long, lpData As Any, lpcbData As Long) As Long
    
    Private Declare Function RegDeleteKey Lib "advapi32.dll" Alias "RegDeleteKeyA" (ByVal hKey As Long, ByVal lpSubKey As String) As Long
    Private Declare Function RegCreateKeyEx Lib "advapi32.dll" Alias "RegCreateKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, ByVal Reserved As Long, ByVal lpClass As String, ByVal dwOptions As Long, ByVal samDesired As Long, lpSecurityAttributes As Any, phkResult As Long, lpdwDisposition As Long) As Long
    Private Declare Function RegOpenKeyEx Lib "advapi32.dll" Alias "RegOpenKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, ByVal Reserved As Long, ByVal samDesired As Long, phkResult As Long) As Long
    Private Declare Function RegDeleteValue Lib "advapi32.dll" Alias "RegDeleteValueA" (ByVal hKey As Long, ByVal lpValueName As String) As Long
    
    Sub LookForSubKeys()
        'KPD-Team 2001
        'URL: http://www.allapi.net/
        'E-Mail: KPDTeam@Allapi.net
        Dim hKey As Long, Cnt As Long, sName As String, sData As String, Ret As Long, RetData As Long
        Const BUFFER_SIZE As Long = 255
        'Set the forms graphics mode to persistent
        'Me.AutoRedraw = True
        'Me.Print "RegEnumKeyEx"
        'Debug.Print "RegEnumKeyEx"
        Ret = BUFFER_SIZE
        'Open the registry key
        If RegOpenKey(HKEY_CURRENT_USER, "CommandBars", hKey) = 0 Then
            'Create a buffer
            sName = Space(BUFFER_SIZE)
            'Enumerate the keys
            While RegEnumKeyEx(hKey, Cnt, sName, Ret, ByVal 0&, vbNullString, ByVal 0&, ByVal 0&) <> ERROR_NO_MORE_ITEMS
                'Show the enumerated key
                'Me.Print "  " + Left$(sName, Ret)
                Debug.Print "  " + Left$(sName, Ret)
                'prepare for the next key
                Cnt = Cnt + 1
                sName = Space(BUFFER_SIZE)
                Ret = BUFFER_SIZE
            Wend
            'close the registry key
            RegCloseKey hKey
        Else
            Debug.Print "  Error while calling RegOpenKey"
        End If
        Debug.Print vbCrLf + "RegEnumValue"
        Cnt = 0
        'Open a registry key
        If RegOpenKey(HKEY_CURRENT_USER, "Software\Microsoft\OFFICE\14.0\ACCESS\SETTINGS\COMMANDBARS\", hKey) = 0 Then
            'initialize
            sName = Space(BUFFER_SIZE)
            sData = Space(BUFFER_SIZE)
            Ret = BUFFER_SIZE
            RetData = BUFFER_SIZE
            'enumerate the values
            While RegEnumValue(hKey, Cnt, sName, Ret, 0, ByVal 0&, ByVal sData, RetData) <> ERROR_NO_MORE_ITEMS
                'show data
                If RetData > 0 Then Debug.Print "  " + Left$(sName, Ret) + "=" + Left$(sData, RetData - 1)
                'prepare for next value
                Cnt = Cnt + 1
                sName = Space(BUFFER_SIZE)
                sData = Space(BUFFER_SIZE)
                Ret = BUFFER_SIZE
                RetData = BUFFER_SIZE
            Wend
            'Close the registry key
            RegCloseKey hKey
        Else
            Debug.Print "  Error while calling RegOpenKey"
        End If
    End Sub
    
    Sub DeleteKeys()
    'KPD-Team 2000
        'URL: http://www.allapi.net/
        'E-Mail: KPDTeam@Allapi.net
        Dim Result As Long
        Dim hKey As Long, Cnt As Long, sName As String, sData As String, Ret As Long, RetData As Long
        Const BUFFER_SIZE As Long = 255
        'Debug.Print "RegEnumKeyEx"
        Ret = BUFFER_SIZE
        'Check if the specified key exists
        If RegOpenKey(HKEY_CURRENT_USER, "Software\Microsoft\OFFICE\14.0\ACCESS\SETTINGS\COMMANDBARS\", hKey) = 0 Then
        'initialize
            sName = Space(BUFFER_SIZE)
            sData = Space(BUFFER_SIZE)
            Ret = BUFFER_SIZE
            RetData = BUFFER_SIZE
            'enumerate the values
            While RegEnumValue(hKey, Cnt, sName, Ret, 0, ByVal 0&, ByVal sData, RetData) <> ERROR_NO_MORE_ITEMS
                'show data
                If RetData > 0 Then Debug.Print "  " + Left$(sName, Ret) + "=" + Left$(sData, RetData - 1)
                                If Left$(sName, Ret) Like "TEST" Then
                                RegDeleteValue hKey, sName     'Delete Key - caution
                                End If
                'prepare for next value
                Cnt = Cnt + 1
                sName = Space(BUFFER_SIZE)
                sData = Space(BUFFER_SIZE)
                Ret = BUFFER_SIZE
                RetData = BUFFER_SIZE
            Wend
            'Close the registry key
            RegCloseKey hKey
        
        End If
        
    End Sub
    
     

    You'll need to edit your registry location, I am pointing it to Version 14.0. I test it on Version 9.0. So far it works.

    You can to modify the code to suit your needs.

    Please note that I'm use the subkey name to delete the Menu which is "Test" (the name of the subkey not the Menu name).

    Use "Left$(sData, RetData - 1)" to find the string of the menu you created. I have not try it yet. I'll leave that to you.

    You could delete all with names "ASCBxxx" but you have to import back from a backup registry file. That's just the trouble with it.

    HTH

    Friday, October 18, 2019 3:06 AM

All replies

  • I think none of that is possible. Perhaps the simplest way would be to "print" the report to PDF using DoCmd.OutputTo. Then pick up that file and use CDO programming to send it out.

    -Tom. Microsoft Access MVP

    Friday, October 11, 2019 3:36 PM
  • I think that you can add a shortcut menu to the report and perform pretty much every action you want

    Here what Ms says about shortcut menus in newer versions : https://docs.microsoft.com/en-us/office/vba/access/concepts/miscellaneous/create-a-shortcut-menu-for-a-form-form-control-or-report

    I am afraid i don't have the time to test it...but it proves for some reason impossible

    you could have a custom ribbon and add a button to email report ..this could be disabled and enabled on report view...i know this works because i had a client who wanted this kind of functionality for printing the Report.

    Friday, October 11, 2019 9:15 PM
  • What I need is a way to send CDO emails via right click on an Access Report Preview. Is it possible?

    H i Peter,

    For a running event I make "personalized reports", that I send to the subscriber. I make a "report"/overview in HTML and assign that to HTMLBody.

    Since I don't use Access reports I don't know if it is possible, but can you convert the report to HTML?

    Imb.

    Saturday, October 12, 2019 9:51 AM
  • Hi Imb

    It is possible and easy to convert reports to HTML.


    Cheers // Peter Forss Stockholm

    Saturday, October 12, 2019 10:17 AM
  • It is possible and easy to convert reports to HTML.

    Hi Peter,

    Ok. Then you can output the report as HTML-file, grab the contents of that file, and assign it to the HTMLBody property of the CDO email.

    Imb.

    Saturday, October 12, 2019 7:34 PM
  • Converting Report to HTML

    Viable..Yes

    Easy...unless you don't use a 3rd party control...lets say it should take some time to get everything translated to HTML

    Saturday, October 12, 2019 8:16 PM
  • Hi Peter,

    As pointed out to you, not possible due to the fact that the Print Preview is a built-in toolbar/menu.

    The only way is to create a Ribbon for that Report.

    Good and Bad news.....

    The good news is that you can create a custom menu for the Report Print Preview.

    The bad news is that you cannot remove once you created it.

    Sub AddCustomMenuToReport()
    Dim NewItem  As Object
      Set NewItem = CommandBars("Print Preview Popup").Controls.Add(Type:=1)
      With NewItem
        .BeginGroup = True
        .Caption = "My CDO Email"   'your email???  I mean name what you want it
        .FaceId = 0
        .OnAction = "TestRoutine"   'run your function/sub?
      End With
    End Sub

    The code only need to run once.

    So, do it with caution for your FE.

    I have no idea how to remove that custom added command-menu. Once added, it becomes part of "Print Preview Popup" context menu. "Print Preview Popup" cannot be deleted.

    However, like Ribbons, you can create a custom toolbar(the old method).

    PS: Affect all Reports/Forms/etc in Print Preview Mode --- do with caution.

    HTH.



    • Edited by AccessVandal Wednesday, October 16, 2019 9:06 AM add
    • Marked as answer by ForssPeterNova Wednesday, October 16, 2019 9:14 AM
    Wednesday, October 16, 2019 9:02 AM
  • Hi Vandal

    Thank you very much for this.

    I will try it in a test database before I implement it in the "real" FE


    Cheers // Peter Forss Stockholm

    Wednesday, October 16, 2019 9:13 AM
  • Hi Peter,

    On a side note, just for your info. You could locate it in Windows Registry.

    HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Access\Settings\CommandBars

    Assuming your default install of Office/Access and your version number.

    It starts with ASCBxxx, but I'm not sure we can delete them. I suggest that you export out the Reg item as backup in case you need to put it back.


    • Edited by AccessVandal Wednesday, October 16, 2019 9:47 AM add
    Wednesday, October 16, 2019 9:36 AM
  • Vandal

    That was a good one. I thought the commandbar become a part of the database.

    But it is for all databases in my Access installation!


    Cheers // Peter Forss Stockholm

    Wednesday, October 16, 2019 10:20 AM
  • Hi Peter,

    It appears that it can be deleted. No harm to Windows or Access. The only problem is that you'll have to reset your Toolbars and Custom toolbars.

    Real tricky to deploy since you don't know the name of the subkey as it will be random. But you still can read it with an API to search the name of the menu.

    You can start the FE with the code and delete the menu on your FE during on Close or Exit of the database.

    After some time searching.....I came up with this.

    Option Compare Database
    Option Explicit
    
    Const ERROR_NO_MORE_ITEMS = 259&
    Const HKEY_CURRENT_CONFIG = &H80000005
    Const HKEY_LOCAL_MACHINE = &H80000002
    
    Const HKEY_CURRENT_USER = &H80000001
    Const REG_OPTION_BACKUP_RESTORE = 4     ' open for backup or restore
    Const REG_OPTION_VOLATILE = 1           ' Key is not preserved when system is rebooted
    Const REG_OPTION_NON_VOLATILE = 0       ' Key is preserved when system is rebooted
    Const STANDARD_RIGHTS_ALL = &H1F0000
    Const SYNCHRONIZE = &H100000
    Const READ_CONTROL = &H20000
    Const STANDARD_RIGHTS_READ = (READ_CONTROL)
    Const STANDARD_RIGHTS_WRITE = (READ_CONTROL)
    Const KEY_CREATE_LINK = &H20
    Const KEY_CREATE_SUB_KEY = &H4
    Const KEY_ENUMERATE_SUB_KEYS = &H8
    Const KEY_NOTIFY = &H10
    Const KEY_QUERY_VALUE = &H1
    Const KEY_SET_VALUE = &H2
    Const KEY_READ = ((STANDARD_RIGHTS_READ Or KEY_QUERY_VALUE Or KEY_ENUMERATE_SUB_KEYS Or KEY_NOTIFY) And (Not SYNCHRONIZE))
    Const KEY_WRITE = ((STANDARD_RIGHTS_WRITE Or KEY_SET_VALUE Or KEY_CREATE_SUB_KEY) And (Not SYNCHRONIZE))
    Const KEY_EXECUTE = (KEY_READ)
    Const KEY_ALL_ACCESS = ((STANDARD_RIGHTS_ALL Or KEY_QUERY_VALUE Or KEY_SET_VALUE Or KEY_CREATE_SUB_KEY Or KEY_ENUMERATE_SUB_KEYS Or KEY_NOTIFY Or KEY_CREATE_LINK) And (Not SYNCHRONIZE))
    
    Private Declare Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As Long) As Long
    Private Declare Function RegOpenKey Lib "advapi32.dll" Alias "RegOpenKeyA" (ByVal hKey As Long, ByVal lpSubKey As String, phkResult As Long) As Long
    Private Declare Function RegEnumKeyEx Lib "advapi32.dll" Alias "RegEnumKeyExA" (ByVal hKey As Long, ByVal dwIndex As Long, ByVal lpName As String, lpcbName As Long, ByVal lpReserved As Long, ByVal lpClass As String, lpcbClass As Long, lpftLastWriteTime As Any) As Long
    Private Declare Function RegEnumValue Lib "advapi32.dll" Alias "RegEnumValueA" (ByVal hKey As Long, ByVal dwIndex As Long, ByVal lpValueName As String, lpcbValueName As Long, ByVal lpReserved As Long, lpType As Long, lpData As Any, lpcbData As Long) As Long
    
    Private Declare Function RegDeleteKey Lib "advapi32.dll" Alias "RegDeleteKeyA" (ByVal hKey As Long, ByVal lpSubKey As String) As Long
    Private Declare Function RegCreateKeyEx Lib "advapi32.dll" Alias "RegCreateKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, ByVal Reserved As Long, ByVal lpClass As String, ByVal dwOptions As Long, ByVal samDesired As Long, lpSecurityAttributes As Any, phkResult As Long, lpdwDisposition As Long) As Long
    Private Declare Function RegOpenKeyEx Lib "advapi32.dll" Alias "RegOpenKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, ByVal Reserved As Long, ByVal samDesired As Long, phkResult As Long) As Long
    Private Declare Function RegDeleteValue Lib "advapi32.dll" Alias "RegDeleteValueA" (ByVal hKey As Long, ByVal lpValueName As String) As Long
    
    Sub LookForSubKeys()
        'KPD-Team 2001
        'URL: http://www.allapi.net/
        'E-Mail: KPDTeam@Allapi.net
        Dim hKey As Long, Cnt As Long, sName As String, sData As String, Ret As Long, RetData As Long
        Const BUFFER_SIZE As Long = 255
        'Set the forms graphics mode to persistent
        'Me.AutoRedraw = True
        'Me.Print "RegEnumKeyEx"
        'Debug.Print "RegEnumKeyEx"
        Ret = BUFFER_SIZE
        'Open the registry key
        If RegOpenKey(HKEY_CURRENT_USER, "CommandBars", hKey) = 0 Then
            'Create a buffer
            sName = Space(BUFFER_SIZE)
            'Enumerate the keys
            While RegEnumKeyEx(hKey, Cnt, sName, Ret, ByVal 0&, vbNullString, ByVal 0&, ByVal 0&) <> ERROR_NO_MORE_ITEMS
                'Show the enumerated key
                'Me.Print "  " + Left$(sName, Ret)
                Debug.Print "  " + Left$(sName, Ret)
                'prepare for the next key
                Cnt = Cnt + 1
                sName = Space(BUFFER_SIZE)
                Ret = BUFFER_SIZE
            Wend
            'close the registry key
            RegCloseKey hKey
        Else
            Debug.Print "  Error while calling RegOpenKey"
        End If
        Debug.Print vbCrLf + "RegEnumValue"
        Cnt = 0
        'Open a registry key
        If RegOpenKey(HKEY_CURRENT_USER, "Software\Microsoft\OFFICE\14.0\ACCESS\SETTINGS\COMMANDBARS\", hKey) = 0 Then
            'initialize
            sName = Space(BUFFER_SIZE)
            sData = Space(BUFFER_SIZE)
            Ret = BUFFER_SIZE
            RetData = BUFFER_SIZE
            'enumerate the values
            While RegEnumValue(hKey, Cnt, sName, Ret, 0, ByVal 0&, ByVal sData, RetData) <> ERROR_NO_MORE_ITEMS
                'show data
                If RetData > 0 Then Debug.Print "  " + Left$(sName, Ret) + "=" + Left$(sData, RetData - 1)
                'prepare for next value
                Cnt = Cnt + 1
                sName = Space(BUFFER_SIZE)
                sData = Space(BUFFER_SIZE)
                Ret = BUFFER_SIZE
                RetData = BUFFER_SIZE
            Wend
            'Close the registry key
            RegCloseKey hKey
        Else
            Debug.Print "  Error while calling RegOpenKey"
        End If
    End Sub
    
    Sub DeleteKeys()
    'KPD-Team 2000
        'URL: http://www.allapi.net/
        'E-Mail: KPDTeam@Allapi.net
        Dim Result As Long
        Dim hKey As Long, Cnt As Long, sName As String, sData As String, Ret As Long, RetData As Long
        Const BUFFER_SIZE As Long = 255
        'Debug.Print "RegEnumKeyEx"
        Ret = BUFFER_SIZE
        'Check if the specified key exists
        If RegOpenKey(HKEY_CURRENT_USER, "Software\Microsoft\OFFICE\14.0\ACCESS\SETTINGS\COMMANDBARS\", hKey) = 0 Then
        'initialize
            sName = Space(BUFFER_SIZE)
            sData = Space(BUFFER_SIZE)
            Ret = BUFFER_SIZE
            RetData = BUFFER_SIZE
            'enumerate the values
            While RegEnumValue(hKey, Cnt, sName, Ret, 0, ByVal 0&, ByVal sData, RetData) <> ERROR_NO_MORE_ITEMS
                'show data
                If RetData > 0 Then Debug.Print "  " + Left$(sName, Ret) + "=" + Left$(sData, RetData - 1)
                                If Left$(sName, Ret) Like "TEST" Then
                                RegDeleteValue hKey, sName     'Delete Key - caution
                                End If
                'prepare for next value
                Cnt = Cnt + 1
                sName = Space(BUFFER_SIZE)
                sData = Space(BUFFER_SIZE)
                Ret = BUFFER_SIZE
                RetData = BUFFER_SIZE
            Wend
            'Close the registry key
            RegCloseKey hKey
        
        End If
        
    End Sub
    
     

    You'll need to edit your registry location, I am pointing it to Version 14.0. I test it on Version 9.0. So far it works.

    You can to modify the code to suit your needs.

    Please note that I'm use the subkey name to delete the Menu which is "Test" (the name of the subkey not the Menu name).

    Use "Left$(sData, RetData - 1)" to find the string of the menu you created. I have not try it yet. I'll leave that to you.

    You could delete all with names "ASCBxxx" but you have to import back from a backup registry file. That's just the trouble with it.

    HTH

    Friday, October 18, 2019 3:06 AM
  • Hi Vandal

    Thank very much for this research!

    This is a great forum.


    Cheers // Peter Forss Stockholm

    Friday, October 18, 2019 4:01 AM
  • For anything relating to Right-Click menus I always refer people to http://www.devhut.net/2017/03/18/great-access-tools-access-shortcut-right-click-tool/

    Simply let Dale's tool do all the hard work for you!


    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Friday, October 18, 2019 2:02 PM