MS Outlook VBA - 识别电子邮件中的MS Excel或MS Access附件，并在发送后警告双重检查PII的内容。 [英] MS Outlook VBA - identify MS Excel or MS Access attachments in email and warn to double check contents for PII after hitting send.

问题描述

我的工作环境中，个人识别信息（PII）经常被无意中通过非加密电子邮件传输。这些数据大多数存储在Excel或Access电子表格中。

我想在Outlook中创建一些代码，用于在发送后识别Access或Excel附件，并询问附有此电子邮件的Access或Excel文件你确定这些不包含PII？

我经常在Access中使用VBA，但我有点不确定我会如何在Outlook中执行此操作。我知道我需要添加一些IF和THEN语句，以及在附件名称中捕获xlsx或accdb，但我不知道该怎么做。我理解的MsgBox部分，但是在附件名称中找到标识xlsx或accdb的标准，我不知道。我知道使用正确功能我可以做到这一点，但我尝试，它没有工作。

这是不对的，但至少有一个尝试：

  Private Sub Application_ItemSend（ByVal Item As Object，Cancel As Boolean）
 
如果右（[attachment_Name]，4）= xlsx然后
 
 answer = MsgBox（访问或Excel文件附加到此电子邮件，你确定这些不包含PHI吗？，vbYesNo）
 
如果answer = vbNo 
取消= True 
 
否则
 
如果
 
结束If 
 
 End Sub 
  

感谢任何指导。

解决方案

以下是您要查找的代码：

  Private Sub Application_ItemSend（ByVal Item As Object，Cancel As Boolean）
 
 Dim bolSensitiveAttach As Boolean 
 Dim answer As Double 
 
 Set Msg = Item 
 
 bolSensitiveAttach = False 
如果Msg.Attachments.Count> 0然后
对于i = 1到Msg.Attachments.Count 
如果右（Msg.Attachments（i）.FileName，3）=xls或_ 
 Left（Right（Msg。附件（i）.FileName，4），3）=xls或_ 
右（Msg.Attachments（i）.FileName，5）=accdb或_ 
右（Msg.Attachments （i）.FileName，3）=mdb然后
 bolSensitiveAttach = True 
结束如果
 
下一个i 
如果
 
如果bolSensitiveAttach = True然后
 answer = MsgBox（有Access或Excel文件附加到这个_ 
&邮件，你确定这些不包含PHI？，vbYesNo）
如果answer = vbNo然后
取消= True 
如果
结束If 
 
 End Sub 
  

希望这有帮助。

编辑包含.mdb扩展名和xls *扩展名（xlsm，xlsx ... ）而不是xlsx。感谢Parfait的建议。

I work in an environment where personal identifying information (PII) is often inadvertently transmitted through non-encrypted emails. Most of the times these data are stored in Excel or Access spreadsheets.

I'd like to create some code in Outlook that would identify Access or Excel attachments after hitting send and ask "There are Access or Excel files attached to this email, are you sure these do not contain PII?"

I use VBA in Access quite often, but I'm a little unsure how I'd go about doing this in Outlook. I know I'd need to add in some "IF" and "THEN" statements and something to capture the "xlsx" or "accdb" in the attachment name, but I'm unsure how to go about doing this. The MsgBox part I understand, but getting the criteria for identifying "xlsx" or "accdb" in the attachment name I just don't get. I know using the "right" function I may be able to do this, but I tried and it didn't work.

This isn't right, but it's at least an attempt:

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)

If Right([attachment_Name],4) = xlsx then

answer = MsgBox("There are Access or Excel files attached to this email, are you sure these do not contain PHI?",vbYesNo)

If answer = vbNo 
Cancel = True

Else

End If

End If

End Sub

I appreciate any guidance.

解决方案

Here's the code you're looking for:

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)

Dim bolSensitiveAttach As Boolean
Dim answer As Double

Set Msg = Item

bolSensitiveAttach = False
If Msg.Attachments.Count > 0 Then
    For i = 1 To Msg.Attachments.Count
         If Right(Msg.Attachments(i).FileName, 3) = "xls" Or  _
                Left(Right(Msg.Attachments(i).FileName, 4), 3) = "xls" Or _
                Right(Msg.Attachments(i).FileName, 5) = "accdb" Or _
                Right(Msg.Attachments(i).FileName, 3) = "mdb" Then  
                     bolSensitiveAttach = True
         End If

    Next i
End If

If bolSensitiveAttach = True Then
    answer = MsgBox("There are Access or Excel files attached to this" _
                 & "mail, are you sure these do not contain PHI?", vbYesNo)
    If answer = vbNo Then
        Cancel = True
    End If
End If

End Sub

Hope this helps.

EDITED TO INCLUDE .mdb extension and xls* extensions (xlsm, xlsx...) instead of just xlsx. Thanks for the suggestion Parfait.

这篇关于MS Outlook VBA - 识别电子邮件中的MS Excel或MS Access附件，并在发送后警告双重检查PII的内容。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！