Email Item as Link from Access

[ADezii]

@Mariostg:
Thanks for the clarification. I actually tried to work Command Line Arguments into the following Syntax, utilizing HTMLBody, but as of yet, to no avail.

.HTMLBody =  "<a href=file://C:\Test\FE.mdb>Click to Open Database</a>"

[NeoPa]

I've tried to avoid any reliance on using HTML type emails. If you could let us know if that can be assumed then it might make it all a lot easier (as HTML tags will then become feasible).

[TheSmileyCoder]

The message will likely be read in outlook 2003. What that means in terms of html or not I honestly dont know.

[ADezii]

I have developed some Code that will:

1. Send an E-Mail to any User consisting of a Semi-Colon Delimited List of Values placed in the Subject of the E-Mail (Employees;FL;3 3484).
2. Minimal Code (Outlook VBA) on the End Users PC examines the Incoming Item, then:
   1. If it is in fact an E-Mail, and nothing else, and
   2. If it is from a specific Sender (TheSmileyCoder ),
3. Opens Access and sends the Subject String (Employees;FL;3 3484) as a Command Line Argument where Access can now parse it into its individual Elements, and make the appropriate action.
4. The problem is that, right now, there is absolutely no User Intervention. You would send the E-Mail, and Access would Open on the End User's PC with the appropriate Command Line Arguments.
5. This approach is rather 'Rogue', even by my standards, but if you are interested just let me know and I'll post the insanity (LOL).

[TheSmileyCoder]

Hi Adezii.

It sounds good. I don't like the rogue part either, but I figure it would be possible to convert whatever outlook code you have to something that could be placed in a custom outlook button? And thus only execute when the user presses said button.

I would like very much to see what you have.

[ADezii]

1. An E-Mail would be sent to the End User with the Subject consisting of a Delimited List of Values, such as: Employees;FL;33 484.
2. In Outlook, on the End User's PC, Macros must be enabled in some manner.
3. In the ThisOutlookSess ion Code Module of Outlook:
   

   Public WithEvents myOItems As Outlook.Items

   Private Sub Application_Startup()
     Set myOItems = Outlook.Session.GetDefaultFolder(olFolderInbox).Items
   End Sub

   Private Sub myOItems_ItemAdd(ByVal Item As Object)
   Dim varRetVal As Variant
   
   'Is this an E-Mail Item?
   If TypeName(Item) = "MailItem" Then
     With Item
       If .Sender = "Dezii, Armund" Then       'Execute if only from this Sender
         'Open the Front End Database, passing to it the Delimited String
         varRetVal = Shell("C:\Program Files\Office 2003\OFFICE11\MSACCESS.EXE C:\Test\FE.mdb /cmd " & _
                            .Subject, vbMaximizedFocus)
       End If
     End With
   End If
   End Sub

4. At some Entry Point in the Front End Database, parse the Command String, and take some action accordingly:
   

   Dim varSplit As Variant
   
   varSplit = Split(Command, ";")
   
   Debug.Print "Form Name : " & varSplit(0)
   Debug.Print "State Name: " & varSplit(1)
   Debug.Print "Zip Code  : " & varSplit(2)

5. Code has been tested with Outlook/Access 2003, and is functional. Should be a simple matter to channge it to a Manual Operation as opposed to Automatic.

[ADezii]

@TheSmileyCoder :
The more that I think about this, the more I feel as though Outlook needs to 'Capture' your E-Mail as soon as it arrives, then Prompt the User for a Response. The following, slightly revised, Code appears to accomplish this quite well. It displays a Modal Dialog Box (Message Box) to the User while at the same time allowing E-Mails to filter in behind it. I really was not sure how this Logic would play out, but it apparently does.

Private Sub myOItems_ItemAdd(ByVal Item As Object)
Dim varRetVal As Variant
Dim intResponse As Integer

'Is this an E-Mail Item?
If TypeName(Item) = "MailItem" Then
  With Item
    If .Sender = "Dezii, Armund" Then       'Execute if only from this Sender
      intResponse = MsgBox("Open Access Database with Parameters specified in the Subject Field?", _
                    vbDefaultButton1 + vbQuestion + vbYesNo, "Open Access Prompt")
      If intResponse = vbYes Then
        'Open the Front End Database, passing to it the Delimited String
        varRetVal = Shell("C:\Program Files\Office 2003\OFFICE11\MSACCESS.EXE C:\Test\FE.mdb /cmd " & _
                           .Subject, vbMaximizedFocus)
      End If
    End If
  End With
End If
End Sub

[Mariostg]

@ADezii
I am amazed. Pretty clever to tell outlook to parse the subject line.
I have a routine that sends emails with subject pretty much broken down like you suggested. But it does not go beyond that. I will definitely have to look at extending this with the proposed codes.

[ADezii]

@Mariostg:
Outlook is basically passing the Subject Line to Access in tact as a Command Line Parameter, Access is actually doing the parsing and further processing.

[ADezii]

One obvious flaw in my Logic is if TheSmileyCoder wanted to send a Standard E-Mail to a Recipient, one that did not require the Opening on an Access Database. My simple workaround was to also check for the exclusion of a Semi-Colon in the Subject Line (Code Line #8), in addition to being a Mail Item and from TheSmileyCoder.

Private Sub myOItems_ItemAdd(ByVal Item As Object)
Dim varRetVal As Variant
Dim intResponse As Integer
  
'Is this an E-Mail Item?
If TypeName(Item) = "MailItem" Then
  With Item
    If .Sender = "TheSmileyCoder" And InStr(.Subject, ";") <> 0 Then
      intResponse = MsgBox("Open Access Database with Parameters specified in the Subject Field?", _
                    vbDefaultButton1 + vbQuestion + vbYesNo, "Open Access Prompt")
      If intResponse = vbYes Then
        'Open the Front End Database, passing to it the Delimited String
        varRetVal = Shell("C:\Program Files\Office 2003\OFFICE11\MSACCESS.EXE C:\Test\FE.mdb /cmd " & _
                           .Subject, vbMaximizedFocus)
      End If
    End If
  End With
End If
End Sub

[Mariostg]

@ADezii
Yes, that is what I meant to say.
I guess one issue that bugs me now is that whatever codes that has to go in MS Outlook would have to be installed individually??

[Mariostg]

maybe use this, or a variation of, at line #8

If .SenderName = "TheSmileyCoder" And Left(.Subject, 10) = "For Review" Then

That would help a bit.

By the way, for me .Sender is not working. I have to use .SenderName.

[ADezii]

I guess one issue that bugs me now is that whatever codes that has to go in MS Outlook would have to be installed individually??

Unfortunately, yes. You would also have to Close, then Open Outlook so that the myOItems Object Variable gets Initialized - therein lies the problemo. Only the OP (TheSmileyCoder ) can decide if this is worth the effort or not, if he even considers this approach.

P.S. - Odd that .Sender does not work - what Version of Outlook are you using?

If .SenderName = "TheSmileyCoder" And Left(.Subject, 10) = "For Review" Then

is probably a better Option than mine.

[NeoPa]

It seems that the overarching difficulty involved with this is that a hyperlink seems unable to pass parameters. Most frustrating.

In the absence of this facility I've struggled to come up with a way of passing control to the recipient of one of these emails which doesn't involve the user having to follow any instructions more complicated than : Click This, and preferably doesn't involve specialist software developed for an application, Outlook, that isn't directly related to the facility.

I think I have a viable solution. It involves sending an Excel spreadsheet with embedded code (NB. The code must be runnable by the recipient of course.) that runs code to execute the command found in cell A1. Once the code has executed it closes itself down (and can even close Excel if it discovers that it's the only open workbook). Let me know if this sounds like a viable approach for you.

[NeoPa]

I developed a workbook to handle my suggestion, as well as a database to test that the workbook is working as intended. I've changed the data in the workbook to obscure my identity from the public (this time), but the only data is the command line in cell A1 which you can easily configure for yourself. In fact, the design of this is such that you are expected to update the spreadsheet from code anyway.

There are numerous possible options with this. The database is deliberately set up with a space in the name to illustrate how such names should be handled. It simply echoes the command parameter passed and is purely there to illustrate proof of concept.

Have fun :-)

PS. In case it's not clear, the idea is that the instigator (Smiley) would first prepare the data in the RunCmd.Xls spreadsheet to reflect what they wanted the user to run at the far end. This would then be sent to the user, who would simply open the workbook from there by double-clicking on it. As it opens it will execute the command from the Excel code (using Shell()).

Option Explicit

Private Sub Workbook_Open()
    Dim strMsg As String, strCmd As String

    strCmd = Range("A1")
    strCmd = Replace(strCmd, "%AccessPath%", Environ("AccessPath"))
    strCmd = Replace(strCmd, "%ProgramFiles%", Environ("ProgramFiles"))
    strMsg = "You are about to run the command :%L%L%C"
    strMsg = Replace(strMsg, "%L", vbNewLine)
    strMsg = Replace(strMsg, "%C", strCmd)
    If MsgBox(Prompt:=strMsg, _
              Buttons:=vbOKCancel Or vbQuestion, _
              Title:="RunCommand") = vbOK Then _
        Call Shell(PathName:=strCmd, WindowStyle:=vbNormalFocus)
    If Application.Workbooks.Count > 1 Then
        Call Me.Close
    Else
        Call Application.Quit
    End If
End Sub

PS. Attachment removed, as a later version may be found at post #52.