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 ('[email protected]','[email protected]') 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 ('[email protected]','[email protected]') 00013: order by ReceivedTime desc
В этих двух процедурах - указанные адреса, как вы поняли, мои. Именно по ним, а не по признаку ReceivedByName is null я отбираю, мне послано письмо или от меня. Это бывает полезно, если для переписки например, вы отправляли почту непосредственно с MAIL.RU, а потом переместили ее прямо на MAIL.RU из папки в папку, после чего считали всю почту Аутлуком. В этом случае поле ReceivedByName неактуально.
Делая такие отборы по базе, вы например можете узнать перед отсылкой почты, обращались ли вы уже к этому работодателю или клиенту - когда и сколько раз - и был ли смысл в этих обращениях.
Comments (
)
)
Link to this page:
//www.vb-net.com/windows/OutLook/index.htm
|
|