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!!!
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:
