When drag-drop Outlook email into Access, extract "Subject", edit msg "Body"? (1 Viewer)

southen

Registered User.
Local time
Today, 07:10
Joined
Jan 11, 2013
Messages
22
When drag-drop Outlook email into Access, extract "Subject", edit msg "Body"?

Hi,

The below code allows a user to drag an email from Outlook to an Access field; the code will then save the email as a msg in a defined folder, using a filename that is derived from two of the Access fields. The user manually fills in these two fields ("TrackingNo" and "Title")

This project is for two separate work departments, and I've been struggling for the last few days, without any luck, to accomplish both of them.

For the first department, rather than manually entering text into table field "Title," I'd like if the Title field in the Access table automatically populates with the email's subject (when the user drags the email into Access).

Since there may be characters that can't be used as a filename, I'd imagine we would just keep the CleanString to remove the characters from Title, and so still use that process to give the file its the filename.

The second department requires another item in addition to the one above. I'm not sure this is possible, but I'd like the VBA to insert at the beginning of the email saved (or even replace the subject line) the value in the field "TrackingNo". If this is possible, is it possible to include HTML, like h3, to make the TrackingNo. really stand out?

I'm using Office 2013. I am grateful for any guidance or assistance with this.

I've posted the operative code, with one edit (bottom part) since I was not able to post that here (forum posting rules since I have <10 posts).

Thank you!!!


Code:
Private Sub EmailMemo_Dirty(Cancel As Integer)

    'I got the guts of this sub from Remou on tek-tips. She told me I can drag and drop an
    'email to a memo field, then gave me the object control code to save the file.
    Dim olApp As Outlook.Application
    Dim olExp As Outlook.Explorer
    Dim olSel As Outlook.Selection
    Dim i, intCounter, intResponse As Integer
    Dim strFilename, strSQL, strFolderPath, strPathAndFile, strMsg As String
    Dim fs As Object
    Dim fsFolder As Object
    Dim blnFolderExists, blnFileExists As Boolean
    
    'This field is used to control attaching emails by dropping them on the field.
    'To allow this the field must be editable. This means the user could accidentally
    'type in the field and trigger the code to attach an email. Therefore, this user
    'verification makes sure the user intentionally dropped an email on the field.
    strMsg = "WARNING: You have triggered the E-mail Attachment Function. CHOOSE CAREFULLY ..." & vbCr & vbCr
    strMsg = strMsg & "If you intended to attach an e-mail to this note, answer Yes below. "
    strMsg = strMsg & "If you did not intend to attach an e-mail and don't know what's going on, "
    strMsg = strMsg & "answer No below." & vbCr & vbCr
    strMsg = strMsg & "Did you intentionally drag and drop an e-mail to attach it to this note?"
    intResponse = MsgBox(strMsg, vbYesNo)
    If intResponse = 7 Then 'No
        Cancel = True
        Exit Sub
    End If
    'My network consultant advises not putting too many files in a folder - like our Permanent Images.
    'Therefore, I will separate emails into a new folder each year. This code allows me
    'to never check on it, by creating the folder automatically when the year changes.
    Set fsFolder = CreateObject("Scripting.FileSystemObject")
    strFolderPath = "S:\PATH\Emails " & Year(Date)
    If fsFolder.FolderExists(strFolderPath) = False Then
        fsFolder.CreateFolder (strFolderPath)
    End If


    'Clean Subject of Illegal Characters
    TempSubjectString = CleanString(Me.Title) 'Clean e-mail subject line of invalid characters, etc.


    'Create the filename as a message file from the Counter plus Subject (title) info - which will be unique
  
    strFilename = Me.TrackingNo & "_" & TempSubjectString & ".msg"
    'Combine for full path and file name
    strPathAndFile = strFolderPath & "\" & strFilename
    
    'Make sure this file does not already exist to avoid overwriting email files when there is a
    'system glitch.
    Set fs = CreateObject("Scripting.FileSystemObject")
    blnFileExists = fs.FileExists(strPathAndFile)
    If blnFileExists = False Then
        'There's not already a file for this counter plus subject. This is the way it always
        'should be. But stuff happens. So, I'm checking.
        'Save the email to the filename just created as a message file
        Set olApp = GetObject(, "Outlook.Application")  'First argument is blank to return the currently
                                                        'active Outlook object, otherwise runtime fails
        Set olExp = olApp.ActiveExplorer
        Set olSel = olExp.Selection
        For i = 1 To olSel.Count
            olSel.Item(1).SaveAs strPathAndFile, olMSG
              
        Next
  Else
        'There's already a file for this Title and Numbering. This should be impossible,
        'but stuff happens. In this case we notify the user and then re-establish the links
        'so the user can handle it.
        strMsg = "ATTENTION: The system detected an e-mail file already created for this note. "
        strMsg = strMsg & "That e-mail is now linked to this title & tracking number. Please do the following:" & vbCr & vbCr
        strMsg = strMsg & "1. View the e-mail normally." & vbCr
        strMsg = strMsg & "2. If it is the correct e-mail, you don't need to do anything else." & vbCr
        strMsg = strMsg & "3. If it is the wrong e-mail, use the Un-Attach E-mail button to get rid of it. "
        strMsg = strMsg & "Then attach the correct e-mail."
        MsgBox strMsg
    End If
 'Update the location field with the location.
    Cancel = True   'To roll back changes caused by the drop.
    Me!....EmailLocation... = strPathAndFile
'ABOVE changed due to Forum posting difficulties
    Me.EmailMemo = "EMAIL ATTACHED: Click Here To View"
    Me.EmailMemo.Locked = False
    Me.Dirty = False    'To save the changes.
      
    Set fsFolder = Nothing
    Set fs = Nothing
    Set olSel = Nothing
    Set olExp = Nothing
    Set olApp = Nothing
    

End Sub
 
Last edited:

southen

Registered User.
Local time
Today, 07:10
Joined
Jan 11, 2013
Messages
22
Re: When drag-drop Outlook email into Access, extract "Subject", edit msg "Body"?

I updated some of the code above, so that the particular saved .msg is read and certain information is extracted to populate the relevant Access fields (subject, from). However, this only achieves half the effect - while it populates the fields based on file information, the saved .msg filename is not descriptive.

I'm looking to extract the email's subject *before* it is saved as a .msg; the purpose being to use the the email's subject as the .msg's filename.

I appreciate any help on this!!

Thanks.

Code:
 'Clean Subject of Illegal Characters.  This is important if the Me.Title is used for the email filename.
'Since filename is just based off of "Number", and the Title is obtained from the .msg file via VBA, there's no reason to use this function - hence it's REMmed out
'TempSubjectString = CleanString(Me.Title)
 
   'Create the filename as a message file from the ClientID and the NoteID - which will be unique
   'BELOW WAS: strFilename = Me.TrackingNo & "_" & Me.Title & ".msg", but removed Me.Title since special character messed it up.
   'Then updated to: strFilename = Me.TrackingNo & "_" & TempSubjectString & ".msg"  - but also REMmed out - see TemptSubjectString note above.
       
    strFilename = Me.TrackingNo & ".msg"
    
    'Combine for full path and file name
    strPathAndFile = strFolderPath & "\" & strFilename
    
    'Make sure this file does not already exist to avoid overwriting email files when there is a
    'system glitch.
    Set fs = CreateObject("Scripting.FileSystemObject")
    blnFileExists = fs.FileExists(strPathAndFile)
    If blnFileExists = False Then
        'There's not already a file for this client and noteID. This is the way it always
        'should be. But stuff happens. So, I'm checking.
        'Save the email to the filename just created as a message file
        Set olApp = GetObject(, "Outlook.Application")  'First argument is blank to return the currently
                                                        'active Outlook object, otherwise runtime fails
        Set olExp = olApp.ActiveExplorer
        Set olSel = olExp.Selection
        For i = 1 To olSel.Count
            olSel.Item(1).SaveAs strPathAndFile, olMSG
            
        Next
        
    Else
        'There's already a file for this client and noteID. This should be impossible,
        'but stuff happens. In this case we notify the user and then re-establish the links
        'so the user can handle it.
        strMsg = "ATTENTION: The system detected an e-mail file already created for this note. "
        strMsg = strMsg & "That e-mail is now linked to this note ID. Please do the following:" & vbCr & vbCr
        strMsg = strMsg & "1. View the e-mail normally." & vbCr
        strMsg = strMsg & "2. If it is the correct e-mail, you don't need to do anything else." & vbCr
        strMsg = strMsg & "3. If it is the wrong e-mail, use the Un-Attach E-mail button to get rid of it. "
        strMsg = strMsg & "Then attach the correct e-mail."
        MsgBox strMsg
    End If
     'Update the location field with the location.
    Cancel = True   'To roll back changes caused by the drop.
    Me!....EmailLocation... = strPathAndFile
'ABOVE changed due to Forum posting difficulties
    Me.EmailMemo = "EMAIL ATTACHED: Click Here To View"
    Me.EmailMemo.Locked = False
    Me.Dirty = False    'To save the changes.
 Set ol = CreateObject("Outlook.Application")
Set fso = CreateObject("Scripting.FileSystemObject")
   Set msg = ol.CreateItemFromTemplate(strPathAndFile)
    Me.Title = msg.Subject
    Me.To = msg.Sender.Name & "  <" & msg.Sender.Address & ">"
  '  Me.Received = msg.SentOn
    
    Set fsFolder = Nothing
    Set fs = Nothing
    Set olSel = Nothing
    Set olExp = Nothing
    Set olApp = Nothing
    
 End Sub
 
Last edited:

southen

Registered User.
Local time
Today, 07:10
Joined
Jan 11, 2013
Messages
22
Re: When drag-drop Outlook email into Access, extract "Subject", edit msg "Body"?

I apologize, but I have to...bump....
 

southen

Registered User.
Local time
Today, 07:10
Joined
Jan 11, 2013
Messages
22
Re: When drag-drop Outlook email into Access, extract "Subject", edit msg "Body"?

I hate to answer my own question - especially when that answer's a workaround - but I wanted to post what I did since this might help others. Basically, I renamed the static file using a scrubbed me.Title - which itself is pulled from the email's subject after that email is saved as an .msg So: Save .msg using the auto-tracking # as the file name, extract the Subject from that email and stick it into me.Title, then use a scrubbed me.Title as the file name to which rename the file. I provide the updated part below (which I explicitly comment out).

The only remaining challenge: finding out how to delete the file associated with the record when the record is deleted (note: the file w/ pathname is provided in one of the record fields).

Code:
     'Update the location field with the location.
    Cancel = True   'To roll back changes caused by the drop.
       Me.EmailMemo = "EMAIL ATTACHED: Click Here To View"
    Me.EmailMemo.Locked = False
    Me.Dirty = False    'To save the changes.
 Set ol = CreateObject("Outlook.Application")
Set fso = CreateObject("Scripting.FileSystemObject")
   Set msg = ol.CreateItemFromTemplate(strPathAndFile)
    Me.Title = msg.Subject
    Me.To = msg.Sender.Name & "  <" & msg.Sender.Address & ">"
  '  Me.Received = msg.SentOn
    
 ' THIS IS WHAT I ADDED TO / UPDATED ON THE PREVIOUS CODE:
 
'Scrub the Title of illegal characters.....
    TempSubjectString = CleanString(Me.Title)

 ' ...then rename the .msg file to be the IndexNo & scrubbed title.  Has to be done by rename because I can't figure out how to name the file  dynamically using a scrubbed subject when initially saving!
Name strFolderPath & "\" & strFilename As strFolderPath & "\" & Me.TrackingNo & "_" & TempSubjectString & ".msg"
    
    ' Finally, set the email location
    Me!..[.EmailLocation..]. = strFolderPath & "\" & Me.TrackingNo & "_" & TempSubjectString & ".msg"
 'ABOVE MODIFIED WITH A "...[. .]..." SINCE FORUM READS THIS AS AN EMAIL ADDRESS, AND SO WON'T ALLOW ME TO POST IT.
 
    Set fsFolder = Nothing
    Set fs = Nothing
    Set olSel = Nothing
    Set olExp = Nothing
    Set olApp = Nothing
    
 End Sub
Code:
 Function CleanString(strData)
    'Replace invalid strings.
 
    strData = Replace(strData, "´", "'")
    strData = Replace(strData, "`", "'")
    strData = Replace(strData, "{", "(")
    strData = Replace(strData, "[", "(")
    strData = Replace(strData, "]", ")")
    strData = Replace(strData, "}", ")")
    strData = Replace(strData, "  ", " ")     'Replace two spaces with one space
    strData = Replace(strData, "   ", " ")    'Replace three spaces with one space
    'Eliminate invalid characters.
    strData = Replace(strData, ": ", "_")     ': followed by a space
    strData = Replace(strData, ":", "_")      ': with no space
    strData = Replace(strData, "/", "_")
    strData = Replace(strData, "\", "_")
    strData = Replace(strData, "*", "_")
    strData = Replace(strData, "?", "_")
    strData = Replace(strData, """", "'")
    strData = Replace(strData, "<", "_")
    strData = Replace(strData, ">", "_")
    strData = Replace(strData, "|", "_")
    CleanString = Trim(strData)
End Function
 
Last edited:

Users who are viewing this thread

Top Bottom