423,851 Members | 1,043 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,851 IT Pros & Developers. It's quick & easy.

Sub Routine That Copies Email Atatchments to Share Folder

USTRAGNU1
P: 29
Good Day,

I have a sub routine which copies attachments from email items (.msg files) that I have placed in a share folder to another share folder.

Unfortunately, sometimes the mail item has Digital ID problems:
"Sorry, we're having trouble opening this item. this could be temporary, but if you see it again you might want to restart outlook. your digital id name cannot be found by the underlying security system".

I have not been able to find VBA error handling (EH) that enables the sub to continue when encountering this error. It seems like trying to open a password protected file without the password. Even though I have moved the email from Outlook to the share folder, the Outlook error still prevails. I admit I don't know much about the connection.

This kind of defeats the purpose of my automated process via my sub if I have to test each email by opening it to see if there is a certificate problem or not.

Have any of you encountered this before? Is there a work around with Access VBA error handling?

Would I be better able to bypass the certificate check with PowerShell scripting? Any insight into this would be most helpful.

Please let me know if you have questions or require additional information. Thank you for your time and effort.

Sincerely,
UTS
Jul 31 '18 #1

✓ answered by twinnyfo

UTS,

In order for you to copy attachments, you are forced to open the file--which will cause this digital ID problem. The reason that Access's VBA can't error trap this isbecause the errors are being generated by Outlook.

I've done some searching for PowerShell scripting of handling of attachments, but I think all of the scripts I've seen assume there is no digital ID/encryption. For obvious reasons, the encryption/Digital ID is there to prevent unauthorized access to these messages, so without a form of authenitcation, you will receive an error that prevents you from doing so.

Encryption assumes users will never want to automate anything they do on a regular basis.

Not sure if that helps! It's just kinda the way it is--unless someone has found a workaround for the encryption, which would result in a violation of the whole security thing....

Share this Question
Share on Google+
15 Replies


Nauticalgent
P: 73
Perhaps you could post your code?
Jul 31 '18 #2

twinnyfo
Expert Mod 2.5K+
P: 2,707
UTS,

In order for you to copy attachments, you are forced to open the file--which will cause this digital ID problem. The reason that Access's VBA can't error trap this isbecause the errors are being generated by Outlook.

I've done some searching for PowerShell scripting of handling of attachments, but I think all of the scripts I've seen assume there is no digital ID/encryption. For obvious reasons, the encryption/Digital ID is there to prevent unauthorized access to these messages, so without a form of authenitcation, you will receive an error that prevents you from doing so.

Encryption assumes users will never want to automate anything they do on a regular basis.

Not sure if that helps! It's just kinda the way it is--unless someone has found a workaround for the encryption, which would result in a violation of the whole security thing....
Jul 31 '18 #3

zmbd
Expert Mod 5K+
P: 5,285
1) Is the VBA running in Outlook or Access
2) Need to see the actual code
3) To confirm, the messages (*.msg) reside in a network directory or local
Jul 31 '18 #4

USTRAGNU1
P: 29
A person I know used to work Exchange and he suggested an Outlook Macro to strip the cert before the attachment gets copied to the network share folder, but he did not provide said macro. I am going to look around for something. I have also started working with someone on the Access VBA angle for this situation, and he is currently working through some error handling scenarios, so I will keep you posted and provide code if/when successful. Thank you.
Aug 2 '18 #5

zmbd
Expert Mod 5K+
P: 5,285
USTRAGNU1
{...}A person I know used to work Exchange and he suggested an Outlook Macro to strip the cert before the attachment gets copied to the network share folder,{...}
This is why I asked you if the code was running from Outlook or Access. If you were running from within Outlook then it would make more sense to save the Attachment directly from within Outlook where the authentication should have already been established. Then save the message out as plain text if needed.
-z
Aug 2 '18 #6

zmbd
Expert Mod 5K+
P: 5,285
Ok,
Found my code:

>This is in a module in Outlook running on an exchange server

I have this attached to a button in the ribbon; however, you should be able to have a rule run this as well.

Two VBA Scripts, both I've borrowed a bit from sources here on the Web - As a rule I don't save the messages out for various business reasons; however, I'm sure there's something along the same lines:

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Public Sub SaveAttachmentsToDirectory()
  4.   Dim zAttachment As Outlook.Attachment
  5.   Dim zSaveDirectory As String
  6.   Dim zCurrentItem As Outlook.MailItem
  7.   Dim zCount As Long
  8.   '
  9. On Error GoTo zErrTrap
  10.   Set zCurrentItem = GetCurrentItem
  11.   If TypeName(zCurrentItem) = "MailItem" Then
  12. '
  13. 'Change the W to your directory path... this happens to be a mapped network location on my PC at work.
  14.     zSaveDirectory = "W:\"
  15.     '
  16.     For Each zAttachment In zCurrentItem.Attachments
  17.       zAttachment.SaveAsFile Path:=zSaveDirectory & zAttachment.DisplayName
  18.       zCount = zCount + 1
  19.     Next
  20.   Else
  21.     Err.Raise Number:=13, Source:="SaveAttachmentsToDirectory", Description:="Sorry that isn't an Outlook Mail Item"
  22.   End If
  23.   '
  24. zCleanUP:
  25.   On Error Resume Next
  26.   If Not zCurrentItem Is Nothing Then Set zCurrentItem = Nothing
  27.   MsgBox prompt:=zCount & " - Attachments Saved to: " & vbCrLf & zSaveDirectory, Title:="Action Completed"
  28. Exit Sub
  29. zErrTrap:
  30.   MsgBox prompt:=Err.Source & vbCrLf & Err.Number & vbCrLf & Err.Description, Title:="Save Attachment Error"
  31. Resume zCleanUP
  32. End Sub
Dependent on:
(borrowed this directly from someone - it isn't my code; however, it's pretty slick and saved me a lot of time!
Expand|Select|Wrap|Line Numbers
  1. Public Function GetCurrentItem() As Object
  2.     Dim objApp As Outlook.Application
  3.  
  4.     Set objApp = Application
  5.     On Error Resume Next
  6.     Select Case TypeName(objApp.ActiveWindow)
  7.         Case "Explorer"
  8.             Set GetCurrentItem = objApp.ActiveExplorer.Selection.Item(1)
  9.         Case "Inspector"
  10.             Set GetCurrentItem = objApp.ActiveInspector.currentItem
  11.     End Select
  12.  
  13.     Set objApp = Nothing
  14. End Function
Aug 2 '18 #7

USTRAGNU1
P: 29
Thanks for the code zmbd. Are these both in the same Outlook Module? I want to make sure of the structure and sequence. Thanks!
UTS
Aug 3 '18 #8

zmbd
Expert Mod 5K+
P: 5,285
You can put them in their own modules, the GetCurrentItem() function is declared as public so available to the entire project.
In fact, I have this function in its own module and call it from several different scripts to work with mailitems.

-z
Aug 3 '18 #9

USTRAGNU1
P: 29
Oh yes, thanks! Have a good weekend! UTS
Aug 3 '18 #10

USTRAGNU1
P: 29
Would it be too much to ask if you could modify "SaveAttachmentsToDirectory" to target specific file extensions? I am only working with .csv files for this project...
UTS
Aug 3 '18 #11

zmbd
Expert Mod 5K+
P: 5,285
( revised code in post#14 )

I think that the following
Expand|Select|Wrap|Line Numbers
  1. If UCASE(Right(zAttachment.DisplayName,3))= "CSV" Then
  2.   'the saving code here
  3. End If
to pull the right three characters from the display name and If..Then clause to check should do the trick.

if not then we need to start a new thread with a cross-link back to this thread for context :)
BOL
Aug 3 '18 #12

NeoPa
Expert Mod 15k+
P: 31,121
That's good, but you could also use :
Expand|Select|Wrap|Line Numbers
  1. If zAttachment.DisplayName Like "*.CSV" Then
The case is almost never an issue (depending on the database or the OPTION COMPARE ... statement at the head of the code).
Aug 3 '18 #13

zmbd
Expert Mod 5K+
P: 5,285
Looking into the situation a bit more
Instead of Display name property we should use the file name - just dawned on me that these may be different.

Using NeoPa's suggestion (I wasn't aware that the Like would work in Outlook-VBA - thought it was an Access/SQL thing, just checked and it does work with Outlook - always learning something new!)

We have

Expand|Select|Wrap|Line Numbers
  1. If zAttachment.FileName Like "*.CSV" Then
  2.    'the saving code here
  3. End If
... much More Better ...
Aug 3 '18 #14

USTRAGNU1
P: 29
Top notch, thanks so much! I will run and test and let you know what happens!
UTS
Aug 3 '18 #15

NeoPa
Expert Mod 15k+
P: 31,121
ZMBD:
... much More Better ...
Everyone's into tautology now it seems :-(
Probably just trying to wind me up :-D

Bear in mind Z, although Jet/ACE and the expression service are separate from VBA, and the VBA library particularly, the Like syntax is supported in both, and VBA is independent of the application it's used with. So, Like is supported VBA syntax and can be used in any of applications that support VBA. It's also supported in Jet/ACE but yoy knew that already of course.
Aug 4 '18 #16

Post your reply

Sign in to post your reply or Sign up for a free account.