(NET) NET (2004 год)

OutlookUnload - разгрузка почтовой базы из OutLook в SQL. Сгрузить MSI-файл.


Однажды мне потребовался сервис, по которому я мог бы УДОБНО понять, сколько именно писем и когда я УЖЕ посылал этому адресату. Для такого сервиса мне потребовалось выгрузить почтовую базу, хранимую в Outlook.




Для этого я набросал следующую небольшую следующую прогу:

00001: Module ReloadMail
00002:     Dim CN As New System.Data.SqlClient.SqlConnection(My.Settings.SQL.ToString)
00003:     Dim CMD As New System.Data.SqlClient.SqlCommand("InsertNewMail", CN)
00004:     Public Sub Main()
00005:         Console.Write("Разгрузка базы OUTLOOK в SQL (by VBNET2000)")
00006:         CN.Open()
00007:         CMD.CommandType = CommandType.StoredProcedure
00008:         CMD.Parameters.Add("FolderName", SqlDbType.NVarChar)
00009:         CMD.Parameters.Add("AlternateRecipientAllowed", SqlDbType.Bit)
00010:         CMD.Parameters.Add("Attachments_Count", SqlDbType.Int)
00011:         CMD.Parameters.Add("AutoForwarded", SqlDbType.Bit)
00012:         CMD.Parameters.Add("AutoResolvedWinner", SqlDbType.Bit)
00013:         CMD.Parameters.Add("BCC", SqlDbType.NVarChar)
00014:         CMD.Parameters.Add("BillingInformation", SqlDbType.NVarChar)
00015:         CMD.Parameters.Add("Body", SqlDbType.NVarChar)
00016:         CMD.Parameters.Add("BodyFormat", SqlDbType.Int)
00017:         CMD.Parameters.Add("Categories", SqlDbType.NVarChar)
00018:         CMD.Parameters.Add("Companies", SqlDbType.NVarChar)
00019:         CMD.Parameters.Add("CC", SqlDbType.NVarChar)
00020:         CMD.Parameters.Add("ConversationIndex", SqlDbType.NVarChar)
00021:         CMD.Parameters.Add("ConversationTopic", SqlDbType.NVarChar)
00022:         CMD.Parameters.Add("CreationTime", SqlDbType.DateTime)
00023:         CMD.Parameters.Add("DeferredDeliveryTime", SqlDbType.DateTime)
00024:         CMD.Parameters.Add("DeleteAfterSubmit", SqlDbType.Bit)
00025:         CMD.Parameters.Add("DownloadState", SqlDbType.Int)
00026:         CMD.Parameters.Add("EntryID", SqlDbType.NVarChar)
00027:         CMD.Parameters.Add("ExpiryTime", SqlDbType.DateTime)
00028:         CMD.Parameters.Add("FlagDueBy", SqlDbType.DateTime)
00029:         CMD.Parameters.Add("FlagIcon", SqlDbType.Int)
00030:         CMD.Parameters.Add("FlagRequest", SqlDbType.NVarChar)
00031:         CMD.Parameters.Add("FlagStatus", SqlDbType.Int)
00032:         CMD.Parameters.Add("FormDescription_Name", SqlDbType.NVarChar)
00033:         CMD.Parameters.Add("HTMLBody", SqlDbType.NVarChar)
00034:         CMD.Parameters.Add("InternetCodepage", SqlDbType.Int)
00035:         CMD.Parameters.Add("IsConflict", SqlDbType.Bit)
00036:         CMD.Parameters.Add("ItemProperties_Count", SqlDbType.Int)
00037:         CMD.Parameters.Add("LastModificationTime", SqlDbType.DateTime)
00038:         CMD.Parameters.Add("Links_Count", SqlDbType.Int)
00039:         CMD.Parameters.Add("MarkForDownload", SqlDbType.Int)
00040:         CMD.Parameters.Add("MessageClass", SqlDbType.NVarChar)
00041:         CMD.Parameters.Add("Mileage", SqlDbType.NVarChar)
00042:         CMD.Parameters.Add("NoAging", SqlDbType.Bit)
00043:         CMD.Parameters.Add("OriginatorDeliveryReportRequested", SqlDbType.Bit)
00044:         CMD.Parameters.Add("OutlookInternalVersion", SqlDbType.Int)
00045:         CMD.Parameters.Add("OutlookVersion", SqlDbType.NVarChar)
00046:         CMD.Parameters.Add("Permission", SqlDbType.Int)
00047:         CMD.Parameters.Add("PermissionService", SqlDbType.Int)
00048:         CMD.Parameters.Add("ReadReceiptRequested", SqlDbType.Int)
00049:         CMD.Parameters.Add("ReceivedByEntryID", SqlDbType.NVarChar)
00050:         CMD.Parameters.Add("ReceivedByName", SqlDbType.NVarChar)
00051:         CMD.Parameters.Add("ReceivedOnBehalfOfEntryID", SqlDbType.NVarChar)
00052:         CMD.Parameters.Add("ReceivedOnBehalfOfName", SqlDbType.NVarChar)
00053:         CMD.Parameters.Add("ReceivedTime", SqlDbType.DateTime)
00054:         CMD.Parameters.Add("RecipientReassignmentProhibited", SqlDbType.Bit)
00055:         CMD.Parameters.Add("Recipients_Count", SqlDbType.Int)
00056:         CMD.Parameters.Add("ReminderOverrideDefault", SqlDbType.Bit)
00057:         CMD.Parameters.Add("ReminderPlaySound", SqlDbType.Bit)
00058:         CMD.Parameters.Add("ReminderSet", SqlDbType.Bit)
00059:         CMD.Parameters.Add("ReminderSoundFile", SqlDbType.NVarChar)
00060:         CMD.Parameters.Add("ReminderTime", SqlDbType.DateTime)
00061:         CMD.Parameters.Add("RemoteStatus", SqlDbType.Int)
00062:         CMD.Parameters.Add("ReplyRecipientNames", SqlDbType.NVarChar)
00063:         CMD.Parameters.Add("ReplyRecipients_Count", SqlDbType.Int)
00064:         CMD.Parameters.Add("Saved", SqlDbType.Bit)
00065:         CMD.Parameters.Add("SaveSentMessageFolder_FolderPath", SqlDbType.NVarChar)
00066:         CMD.Parameters.Add("SenderEmailAddress", SqlDbType.NVarChar)
00067:         CMD.Parameters.Add("SenderEmailType", SqlDbType.NVarChar)
00068:         CMD.Parameters.Add("SenderName", SqlDbType.NVarChar)
00069:         CMD.Parameters.Add("Sensitivity", SqlDbType.Int)
00070:         CMD.Parameters.Add("Sent", SqlDbType.Int)
00071:         CMD.Parameters.Add("SentOn", SqlDbType.DateTime)
00072:         CMD.Parameters.Add("SentOnBehalfOfName", SqlDbType.NVarChar)
00073:         CMD.Parameters.Add("Size", SqlDbType.Int)
00074:         CMD.Parameters.Add("Subject", SqlDbType.NVarChar)
00075:         CMD.Parameters.Add("Submitted", SqlDbType.Bit)
00076:         CMD.Parameters.Add("To", SqlDbType.NVarChar)
00077:         CMD.Parameters.Add("UnRead", SqlDbType.Bit)
00078:         CMD.Parameters.Add("UserProperties_Count", SqlDbType.Int)
00079:         CMD.Parameters.Add("VotingOptions", SqlDbType.NVarChar)
00080:         CMD.Parameters.Add("VotingResponse", SqlDbType.NVarChar)
00081:         '
00082:         Dim X As Microsoft.Office.Interop.Outlook.Application = New Microsoft.Office.Interop.Outlook.Application
00083:         'AddHandler X.MAPILogonComplete, AddressOf OutlookLogon
00084:         Console.WriteLine(" - Start.")
00085:         Dim Root As Microsoft.Office.Interop.Outlook.NameSpace = X.GetNamespace("MAPI")
00086:         Dim FoldersName As New Collections.Generic.List(Of String)
00087:         Dim RootFolder As Microsoft.Office.Interop.Outlook.MAPIFolder = Root.Folders(1)
00088:         Dim OneFolder As Microsoft.Office.Interop.Outlook.FoldersClass = CType(RootFolder.Folders, Microsoft.Office.Interop.Outlook.FoldersClass)
00089:         FoldersName.Add(OneFolder.GetFirst.Name)
00090:         For i = 1 To RootFolder.Folders.Count - 1
00091:             FoldersName.Add(OneFolder.GetNext.Name)
00092:         Next
00093:         Dim MailItems As Microsoft.Office.Interop.Outlook.ItemsClass = OneFolder.GetFirst.Items
00094:         GetMailItems(MailItems, FoldersName(1))
00095:         For i = 1 To RootFolder.Folders.Count - 1
00096:             MailItems = OneFolder.GetNext.Items
00097:             GetMailItems(MailItems, FoldersName(i))
00098:         Next
00099:         X.Quit()
00100:         CN.Close()
00101:         Console.WriteLine("End")
00102:     End Sub
00103:     Private Sub GetMailItems(ByVal MailItems As Microsoft.Office.Interop.Outlook.ItemsClass, ByVal FolderName As String)
00104:         Console.Writeline(FolderName & " - всего " & MailItems.Count.ToString)
00105:         Dim i As Integer = 0
00106:         Try
00107:             For Each OneMail As Microsoft.Office.Interop.Outlook.MailItem In MailItems
00108:                 CMD.Parameters("FolderName").Value = FolderName
00109:                 CMD.Parameters("AlternateRecipientAllowed").Value = OneMail.AlternateRecipientAllowed
00110:                 CMD.Parameters("Attachments_Count").Value = OneMail.Attachments.Count
00111:                 CMD.Parameters("AutoForwarded").Value = OneMail.AutoForwarded
00112:                 CMD.Parameters("AutoResolvedWinner").Value = OneMail.AutoResolvedWinner
00113:                 CMD.Parameters("BCC").Value = OneMail.BCC
00114:                 CMD.Parameters("BillingInformation").Value = OneMail.BillingInformation
00115:                 CMD.Parameters("Body").Value = OneMail.Body
00116:                 CMD.Parameters("BodyFormat").Value = OneMail.BodyFormat
00117:                 CMD.Parameters("Categories").Value = OneMail.Categories
00118:                 CMD.Parameters("Companies").Value = OneMail.Companies
00119:                 CMD.Parameters("CC").Value = OneMail.CC
00120:                 CMD.Parameters("ConversationIndex").Value = OneMail.ConversationIndex
00121:                 CMD.Parameters("ConversationTopic").Value = OneMail.ConversationTopic
00122:                 CMD.Parameters("CreationTime").Value = OneMail.CreationTime
00123:                 CMD.Parameters("DeferredDeliveryTime").Value = OneMail.DeferredDeliveryTime
00124:                 CMD.Parameters("DeleteAfterSubmit").Value = OneMail.DeleteAfterSubmit
00125:                 CMD.Parameters("DownloadState").Value = OneMail.DownloadState
00126:                 CMD.Parameters("EntryID").Value = OneMail.EntryID
00127:                 CMD.Parameters("ExpiryTime").Value = OneMail.ExpiryTime
00128:                 CMD.Parameters("FlagDueBy").Value = OneMail.FlagDueBy
00129:                 CMD.Parameters("FlagIcon").Value = OneMail.FlagIcon
00130:                 CMD.Parameters("FlagRequest").Value = OneMail.FlagRequest
00131:                 CMD.Parameters("FlagStatus").Value = OneMail.FlagStatus
00132:                 CMD.Parameters("FormDescription_Name").Value = OneMail.FormDescription.Name
00133:                 CMD.Parameters("HTMLBody").Value = OneMail.HTMLBody
00134:                 CMD.Parameters("InternetCodepage").Value = OneMail.InternetCodepage
00135:                 CMD.Parameters("IsConflict").Value = OneMail.IsConflict
00136:                 CMD.Parameters("ItemProperties_Count").Value = OneMail.ItemProperties.Count
00137:                 CMD.Parameters("LastModificationTime").Value = OneMail.LastModificationTime
00138:                 CMD.Parameters("Links_Count").Value = OneMail.Links.Count
00139:                 CMD.Parameters("MarkForDownload").Value = OneMail.MarkForDownload
00140:                 CMD.Parameters("MessageClass").Value = OneMail.MessageClass
00141:                 CMD.Parameters("Mileage").Value = OneMail.Mileage
00142:                 CMD.Parameters("NoAging").Value = OneMail.NoAging
00143:                 CMD.Parameters("OriginatorDeliveryReportRequested").Value = OneMail.OriginatorDeliveryReportRequested
00144:                 CMD.Parameters("OutlookInternalVersion").Value = OneMail.OutlookInternalVersion
00145:                 CMD.Parameters("OutlookVersion").Value = OneMail.OutlookVersion
00146:                 CMD.Parameters("Permission").Value = OneMail.Permission
00147:                 CMD.Parameters("PermissionService").Value = OneMail.PermissionService
00148:                 CMD.Parameters("ReadReceiptRequested").Value = OneMail.ReadReceiptRequested
00149:                 CMD.Parameters("ReceivedByEntryID").Value = OneMail.ReceivedByEntryID
00150:                 CMD.Parameters("ReceivedByName").Value = OneMail.ReceivedByName
00151:                 CMD.Parameters("ReceivedOnBehalfOfEntryID").Value = OneMail.ReceivedOnBehalfOfEntryID
00152:                 CMD.Parameters("ReceivedOnBehalfOfName").Value = OneMail.ReceivedOnBehalfOfName
00153:                 CMD.Parameters("ReceivedTime").Value = OneMail.ReceivedTime
00154:                 CMD.Parameters("RecipientReassignmentProhibited").Value = OneMail.RecipientReassignmentProhibited
00155:                 CMD.Parameters("Recipients_Count").Value = OneMail.Recipients.Count
00156:                 CMD.Parameters("ReminderOverrideDefault").Value = OneMail.ReminderOverrideDefault
00157:                 CMD.Parameters("ReminderPlaySound").Value = OneMail.ReminderPlaySound
00158:                 CMD.Parameters("ReminderSet").Value = OneMail.ReminderSet
00159:                 CMD.Parameters("ReminderSoundFile").Value = OneMail.ReminderSoundFile
00160:                 CMD.Parameters("ReminderTime").Value = OneMail.ReminderTime
00161:                 CMD.Parameters("RemoteStatus").Value = OneMail.RemoteStatus
00162:                 CMD.Parameters("ReplyRecipientNames").Value = OneMail.ReplyRecipientNames
00163:                 CMD.Parameters("ReplyRecipients_Count").Value = OneMail.ReplyRecipients.Count
00164:                 CMD.Parameters("Saved").Value = OneMail.Saved
00165:                 CMD.Parameters("SaveSentMessageFolder_FolderPath").Value = OneMail.SaveSentMessageFolder.FolderPath
00166:                 CMD.Parameters("SenderEmailAddress").Value = OneMail.SenderEmailAddress
00167:                 CMD.Parameters("SenderEmailType").Value = OneMail.SenderEmailType
00168:                 CMD.Parameters("SenderName").Value = OneMail.SenderName
00169:                 CMD.Parameters("Sensitivity").Value = OneMail.Sensitivity
00170:                 CMD.Parameters("Sent").Value = OneMail.Sent
00171:                 CMD.Parameters("SentOn").Value = OneMail.SentOn
00172:                 CMD.Parameters("SentOnBehalfOfName").Value = OneMail.SentOnBehalfOfName
00173:                 CMD.Parameters("Size").Value = OneMail.Size
00174:                 CMD.Parameters("Subject").Value = OneMail.Subject
00175:                 CMD.Parameters("Submitted").Value = OneMail.Submitted
00176:                 CMD.Parameters("To").Value = OneMail.To
00177:                 CMD.Parameters("UnRead").Value = OneMail.UnRead
00178:                 CMD.Parameters("UserProperties_Count").Value = OneMail.UserProperties.Count
00179:                 CMD.Parameters("VotingOptions").Value = OneMail.VotingOptions
00180:                 CMD.Parameters("VotingResponse").Value = OneMail.VotingResponse
00181:                 Dim InsertRecord As Integer = CMD.ExecuteScalar
00182:                 i += 1
00183:                 If InsertRecord > 0 Then Console.WriteLine("NEW :" & FolderName & " : " & OneMail.EntryID)
00184:             Next
00185:         Catch ex As Exception
00186:             Console.WriteLine("Прочиталось - " & i.ToString)
00187:             'Console.WriteLine(ex.Message)
00188:             Exit Sub
00189:         End Try
00190:     End Sub
00191: 
00192: End Module
00193: 

Конечно, все вышеперечисленные параметры почтовых сообщений являются спецификой MAPI интерфейса и, как правило не несут полезной информации для обычных почтовых сообщений, тем не менее, я выгрузил их в базу все. Часть строк в этой проге вы легко можете закомментировать.


Конечно для работы с MAPI есть гораздо более полные пакеты программ:

однако в моей проге подкупает именно простота - ведь смысловой код составляет всего несколько строк и прекрасно выполняет свою фунцию. Единственное ограничение этой проги - одноуровневая структура папок Аутлука - впрочем чтобы докрутить отбор из многоуровневой иерархии - требуется всего несколько строк.


Для работы этой проги вам потребуется вот такая табла:

00001: CREATE TABLE [dbo].[Mail](
00002:     [i] [int] IDENTITY(1,1) NOT NULL,
00003:     [FolderName] [nvarchar](max) NULL,
00004:     [AlternateRecipientAllowed] [bit] NULL,
00005:     [Attachments_Count] [int] NULL,
00006:     [AutoForwarded] [bit] NULL,
00007:     [AutoResolvedWinner] [bit] NULL,
00008:     [BCC] [nvarchar](max) NULL,
00009:     [BillingInformation] [nvarchar](max) NULL,
00010:     [Body] [nvarchar](max) NULL,
00011:     [BodyFormat] [int] NULL,
00012:     [Categories] [nvarchar](max) NULL,
00013:     [Companies] [nvarchar](max) NULL,
00014:     [CC] [nvarchar](max) NULL,
00015:     [ConversationIndex] [nvarchar](max) NULL,
00016:     [ConversationTopic] [nvarchar](max) NULL,
00017:     [CreationTime] [datetime] NULL,
00018:     [DeferredDeliveryTime] [datetime] NULL,
00019:     [DeleteAfterSubmit] [bit] NULL,
00020:     [DownloadState] [int] NULL,
00021:     [EntryID] [nvarchar](max) NULL,
00022:     [ExpiryTime] [datetime] NULL,
00023:     [FlagDueBy] [datetime] NULL,
00024:     [FlagIcon] [int] NULL,
00025:     [FlagRequest] [nvarchar](max) NULL,
00026:     [FlagStatus] [int] NULL,
00027:     [FormDescription_Name] [nvarchar](max) NULL,
00028:     [HTMLBody] [nvarchar](max) NULL,
00029:     [InternetCodepage] [int] NULL,
00030:     [IsConflict] [bit] NULL,
00031:     [ItemProperties_Count] [int] NULL,
00032:     [LastModificationTime] [datetime] NULL,
00033:     [Links_Count] [int] NULL,
00034:     [MarkForDownload] [int] NULL,
00035:     [MessageClass] [nvarchar](max) NULL,
00036:     [Mileage] [nvarchar](max) NULL,
00037:     [NoAging] [bit] NULL,
00038:     [OriginatorDeliveryReportRequested] [bit] NULL,
00039:     [OutlookInternalVersion] [int] NULL,
00040:     [OutlookVersion] [nvarchar](max) NULL,
00041:     [Permission] [int] NULL,
00042:     [PermissionService] [int] NULL,
00043:     [ReadReceiptRequested] [int] NULL,
00044:     [ReceivedByEntryID] [nvarchar](max) NULL,
00045:     [ReceivedByName] [nvarchar](max) NULL,
00046:     [ReceivedOnBehalfOfEntryID] [nvarchar](max) NULL,
00047:     [ReceivedOnBehalfOfName] [nvarchar](max) NULL,
00048:     [ReceivedTime] [datetime] NULL,
00049:     [RecipientReassignmentProhibited] [bit] NULL,
00050:     [Recipients_Count] [int] NULL,
00051:     [ReminderOverrideDefault] [bit] NULL,
00052:     [ReminderPlaySound] [bit] NULL,
00053:     [ReminderSet] [bit] NULL,
00054:     [ReminderSoundFile] [nvarchar](max) NULL,
00055:     [ReminderTime] [datetime] NULL,
00056:     [RemoteStatus] [int] NULL,
00057:     [ReplyRecipientNames] [nvarchar](max) NULL,
00058:     [ReplyRecipients_Count] [int] NULL,
00059:     [Saved] [bit] NULL,
00060:     [SaveSentMessageFolder_FolderPath] [nvarchar](max) NULL,
00061:     [SenderEmailAddress] [nvarchar](max) NULL,
00062:     [SenderEmailType] [nvarchar](max) NULL,
00063:     [SenderName] [nvarchar](max) NULL,
00064:     [Sensitivity] [int] NULL,
00065:     [Sent] [int] NULL,
00066:     [SentOn] [datetime] NULL,
00067:     [SentOnBehalfOfName] [nvarchar](max) NULL,
00068:     [Size] [int] NULL,
00069:     [Subject] [nvarchar](max) NULL,
00070:     [Submitted] [bit] NULL,
00071:     [To] [nvarchar](max) NULL,
00072:     [UnRead] [bit] NULL,
00073:     [UserProperties_Count] [int] NULL,
00074:     [VotingOptions] [nvarchar](max) NULL,
00075:     [VotingResponse] [nvarchar](max) NULL
00076: ) ON [PRIMARY]

И вот такая процедура:

00001: CREATE PROCEDURE [dbo].[InsertNewMail]
00002: @FolderName                        nvarchar(max) =NULL,
00003: @AlternateRecipientAllowed         bit =NULL ,
00004: @Attachments_Count                 int =NULL,
00005: @AutoForwarded                     bit =NULL,
00006: @AutoResolvedWinner                bit =NULL,
00007: @BCC                               nvarchar(max) =NULL,
00008: @BillingInformation                nvarchar(max) =NULL,
00009: @Body                              nvarchar(max) =NULL,
00010: @BodyFormat                        int =NULL,
00011: @Categories                        nvarchar(max) =NULL,
00012: @Companies                         nvarchar(max) =NULL,
00013: @CC                                nvarchar(max) =NULL,
00014: @ConversationIndex                 nvarchar(max) =NULL,
00015: @ConversationTopic                 nvarchar(max) =NULL,
00016: @CreationTime                      datetime =NULL,
00017: @DeferredDeliveryTime              datetime =NULL,
00018: @DeleteAfterSubmit                 bit =NULL,
00019: @DownloadState                     int =NULL,
00020: @EntryID                           nvarchar(max) =NULL,
00021: @ExpiryTime                        datetime =NULL,
00022: @FlagDueBy                         datetime =NULL,
00023: @FlagIcon                          int =NULL,
00024: @FlagRequest                       nvarchar(max) =NULL,
00025: @FlagStatus                        int =NULL,
00026: @FormDescription_Name              nvarchar(max) =NULL,
00027: @HTMLBody                          nvarchar(max) =NULL,
00028: @InternetCodepage                  int =NULL,
00029: @IsConflict                        bit =NULL,
00030: @ItemProperties_Count              int =NULL,
00031: @LastModificationTime              datetime =NULL,
00032: @Links_Count                       int =NULL,
00033: @MarkForDownload                   int =NULL,
00034: @MessageClass                      nvarchar(max) =NULL,
00035: @Mileage                           nvarchar(max) =NULL,
00036: @NoAging                           bit =NULL,
00037: @OriginatorDeliveryReportRequested bit =NULL,
00038: @OutlookInternalVersion            int =NULL,
00039: @OutlookVersion                    nvarchar(max) =NULL,
00040: @Permission                        int =NULL,
00041: @PermissionService                 int =NULL,
00042: @ReadReceiptRequested              int =NULL,
00043: @ReceivedByEntryID                 nvarchar(max) =NULL,
00044: @ReceivedByName                    nvarchar(max) =NULL,
00045: @ReceivedOnBehalfOfEntryID         nvarchar(max) =NULL,
00046: @ReceivedOnBehalfOfName            nvarchar(max) =NULL,
00047: @ReceivedTime                      datetime =NULL,
00048: @RecipientReassignmentProhibited   bit =NULL,
00049: @Recipients_Count                  int =NULL,
00050: @ReminderOverrideDefault           bit =NULL,
00051: @ReminderPlaySound                 bit =NULL,
00052: @ReminderSet                       bit =NULL,
00053: @ReminderSoundFile                 nvarchar(max) =NULL,
00054: @ReminderTime                      datetime =NULL,
00055: @RemoteStatus                      int =NULL,
00056: @ReplyRecipientNames               nvarchar(max) =NULL,
00057: @ReplyRecipients_Count             int =NULL,
00058: @Saved                             bit =NULL,
00059: @SaveSentMessageFolder_FolderPath  nvarchar(max) =NULL,
00060: @SenderEmailAddress                nvarchar(max) =NULL,
00061: @SenderEmailType                   nvarchar(max) =NULL,
00062: @SenderName                        nvarchar(max) =NULL,
00063: @Sensitivity                       int =NULL,
00064: @Sent                              int =NULL,
00065: @SentOn                            datetime =NULL,
00066: @SentOnBehalfOfName                nvarchar(max) =NULL,
00067: @Size                              int =NULL,
00068: @Subject                           nvarchar(max) =NULL,
00069: @Submitted                         bit =NULL,
00070: @To                                nvarchar(max) =NULL,
00071: @UnRead                            bit =NULL,
00072: @UserProperties_Count              int =NULL,
00073: @VotingOptions                     nvarchar(max) =NULL,
00074: @VotingResponse                    nvarchar(max) =NULL
00075: as
00076: INSERT [Mail]
00077:            ([FolderName]
00078:            ,[AlternateRecipientAllowed]
00079:            ,[Attachments_Count]
00080:            ,[AutoForwarded]
00081:            ,[AutoResolvedWinner]
00082:            ,[BCC]
00083:            ,[BillingInformation]
00084:            ,[Body]
00085:            ,[BodyFormat]
00086:            ,[Categories]
00087:            ,[Companies]
00088:            ,[CC]
00089:            ,[ConversationIndex]
00090:            ,[ConversationTopic]
00091:            ,[CreationTime]
00092:            ,[DeferredDeliveryTime]
00093:            ,[DeleteAfterSubmit]
00094:            ,[DownloadState]
00095:            ,[EntryID]
00096:            ,[ExpiryTime]
00097:            ,[FlagDueBy]
00098:            ,[FlagIcon]
00099:            ,[FlagRequest]
00100:            ,[FlagStatus]
00101:            ,[FormDescription_Name]
00102:            ,[HTMLBody]
00103:            ,[InternetCodepage]
00104:            ,[IsConflict]
00105:            ,[ItemProperties_Count]
00106:            ,[LastModificationTime]
00107:            ,[Links_Count]
00108:            ,[MarkForDownload]
00109:            ,[MessageClass]
00110:            ,[Mileage]
00111:            ,[NoAging]
00112:            ,[OriginatorDeliveryReportRequested]
00113:            ,[OutlookInternalVersion]
00114:            ,[OutlookVersion]
00115:            ,[Permission]
00116:            ,[PermissionService]
00117:            ,[ReadReceiptRequested]
00118:            ,[ReceivedByEntryID]
00119:            ,[ReceivedByName]
00120:            ,[ReceivedOnBehalfOfEntryID]
00121:            ,[ReceivedOnBehalfOfName]
00122:            ,[ReceivedTime]
00123:            ,[RecipientReassignmentProhibited]
00124:            ,[Recipients_Count]
00125:            ,[ReminderOverrideDefault]
00126:            ,[ReminderPlaySound]
00127:            ,[ReminderSet]
00128:            ,[ReminderSoundFile]
00129:            ,[ReminderTime]
00130:            ,[RemoteStatus]
00131:            ,[ReplyRecipientNames]
00132:            ,[ReplyRecipients_Count]
00133:            ,[Saved]
00134:            ,[SaveSentMessageFolder_FolderPath]
00135:            ,[SenderEmailAddress]
00136:            ,[SenderEmailType]
00137:            ,[SenderName]
00138:            ,[Sensitivity]
00139:            ,[Sent]
00140:            ,[SentOn]
00141:            ,[SentOnBehalfOfName]
00142:            ,[Size]
00143:            ,[Subject]
00144:            ,[Submitted]
00145:            ,[To]
00146:            ,[UnRead]
00147:            ,[UserProperties_Count]
00148:            ,[VotingOptions]
00149:            ,[VotingResponse])
00150: SELECT     @FolderName,                        --nvarchar(max),
00151:            @AlternateRecipientAllowed,         --bit,
00152:            @Attachments_Count,                 --int,
00153:            @AutoForwarded,                     --bit,
00154:            @AutoResolvedWinner,                --bit,
00155:            @BCC,                               --nvarchar(max),
00156:            @BillingInformation,                --nvarchar(max),
00157:            @Body,                              --nvarchar(max),
00158:            @BodyFormat,                        --int,
00159:            @Categories,                        --nvarchar(max),
00160:            @Companies,                         --nvarchar(max),
00161:            @CC,                                --nvarchar(max),
00162:            @ConversationIndex,                 --nvarchar(max),
00163:            @ConversationTopic,                 --nvarchar(max),
00164:            @CreationTime,                      --datetime,
00165:            @DeferredDeliveryTime,              --datetime,
00166:            @DeleteAfterSubmit,                 --bit,
00167:            @DownloadState,                     --int,
00168:            @EntryID,                           --nvarchar(max),
00169:            @ExpiryTime,                        --datetime,
00170:            @FlagDueBy,                         --datetime,
00171:            @FlagIcon,                          --int,
00172:            @FlagRequest,                       --nvarchar(max),
00173:            @FlagStatus,                        --int,
00174:            @FormDescription_Name,              --nvarchar(max),
00175:            @HTMLBody,                          --nvarchar(max),
00176:            @InternetCodepage,                  --int,
00177:            @IsConflict,                        --bit,
00178:            @ItemProperties_Count,              --int,
00179:            @LastModificationTime,              --datetime,
00180:            @Links_Count,                       --int,
00181:            @MarkForDownload,                   --int,
00182:            @MessageClass,                      --nvarchar(max),
00183:            @Mileage,                           --nvarchar(max),
00184:            @NoAging,                           --bit,
00185:            @OriginatorDeliveryReportRequested, --bit,
00186:            @OutlookInternalVersion,            --int,
00187:            @OutlookVersion,                    --nvarchar(max),
00188:            @Permission,                        --int,
00189:            @PermissionService,                 --int,
00190:            @ReadReceiptRequested,              --int,
00191:            @ReceivedByEntryID,                 --nvarchar(max),
00192:            @ReceivedByName,                    --nvarchar(max),
00193:            @ReceivedOnBehalfOfEntryID,         --nvarchar(max),
00194:            @ReceivedOnBehalfOfName,            --nvarchar(max),
00195:            @ReceivedTime,                      --datetime,
00196:            @RecipientReassignmentProhibited,   --bit,
00197:            @Recipients_Count,                  --int,
00198:            @ReminderOverrideDefault,           --bit,
00199:            @ReminderPlaySound,                 --bit,
00200:            @ReminderSet,                       --bit,
00201:            @ReminderSoundFile,                 --nvarchar(max),
00202:            @ReminderTime,                      --datetime,
00203:            @RemoteStatus,                      --int,
00204:            @ReplyRecipientNames,               --nvarchar(max),
00205:            @ReplyRecipients_Count,             --int,
00206:            @Saved,                             --bit,
00207:            @SaveSentMessageFolder_FolderPath,  --nvarchar(max),
00208:            @SenderEmailAddress,                --nvarchar(max),
00209:            @SenderEmailType,                   --nvarchar(max),
00210:            @SenderName,                        --nvarchar(max),
00211:            @Sensitivity,                       --int,
00212:            @Sent,                              --int,
00213:            @SentOn,                            --datetime,
00214:            @SentOnBehalfOfName,                --nvarchar(max),
00215:            @Size,                              --int,
00216:            @Subject,                           --nvarchar(max),
00217:            @Submitted,                         --bit,
00218:            @To,                                --nvarchar(max),
00219:            @UnRead,                            --bit,
00220:            @UserProperties_Count,              --int,
00221:            @VotingOptions,                     --nvarchar(max),
00222:            @VotingResponse                     --nvarchar(max),
00223: WHERE NOT EXISTS (SELECT EntryID FROM [Mail] WHERE EntryID=@EntryID)
00224: SELECT @@ROWCOUNT

Выбрать все почтовые адреса, встретившиеся вам в переписках - можно вот такими вьюшками:

00001: Create View [dbo].[FromAddr]
00002: as
00003: select count(*) as [Count],SenderEmailAddress as [From]
00004: from Mail 
00005: Group By [SenderEmailAddress]
00006: GO
00007: Create view [dbo].[ToAddr]
00008: as
00009: select count(*)as [Count],Replace([To],'''','') as [TO]
00010: from Mail 
00011: Group By Replace([To],'''','')
00012: GO
00013: create view [dbo].[AllAddr]
00014: as
00015: select [Count],[From] as Addr from dbo.FromAddr
00016: union all
00017: select [Count],[To] as Addr from dbo.ToAddr
00018: GO

Которые вам покажут примерно следующие результаты:




Ну а собственно отбор отправленных и принятых почтовых сообщений делается вот такими двумя процедурами:

00001: CREATE procedure [dbo].[SendedMail]
00002: @Date datetime = '01/01/2007'
00003: as
00004: select i,
00005: ReceivedTime, 
00006: Replace([To],'''','') as [TO],
00007: [Subject],Body
00008: from Mail 
00009: where 
00010: ReceivedTime>@Date and
00011: SenderEmailAddress in ('vbnet2000@mail.ru','aspnet2@mail.ru')
00012: order by ReceivedTime desc 
00001: CREATE procedure [dbo].[RecievedMail]
00002: @Date datetime = '01/01/2007'
00003: as
00004: select i,
00005: ReceivedTime, 
00006: SenderEmailAddress as [From], 
00007: [Subject],
00008: Body
00009: from Mail 
00010: where 
00011: ReceivedTime>@Date and
00012: SenderEmailAddress not in ('vbnet2000@mail.ru','aspnet2@mail.ru')
00013: order by ReceivedTime desc

В этих двух процедурах - указанные адреса, как вы поняли, мои. Именно по ним, а не по признаку ReceivedByName is null я отбираю, мне послано письмо или от меня. Это бывает полезно, если для переписки например, вы отправляли почту непосредственно с MAIL.RU, а потом переместили ее прямо на MAIL.RU из папки в папку, после чего считали всю почту Аутлуком. В этом случае поле ReceivedByName неактуально.




Делая такие отборы по базе, вы например можете узнать перед отсылкой почты, обращались ли вы уже к этому работодателю или клиенту - когда и сколько раз - и был ли смысл в этих обращениях.



Комментарии к этой страничке ( )
ссылка на эту страничку: http://www.vb-net.ru/windows/OutLook/index.htm
<Назад>  <Назад>  <Назад>  <Назад>  <На главную>  <В раздел ASP>  <В раздел NET>  <В раздел SQL>  <В раздел Разное>  <Написать автору>  < Поблагодарить>
Московская хельсинская группа   Радио Свобода  Новая газета   The New Times (Новое время)