概要
outlookの特定のフォルダに入っているメールをエクセルのシートに出力する
サンプル
Public Sub ExportEmailToExcel()
' outlook
Dim objOutlook As Outlook.Application
Dim objNameSpace As Outlook.namespace
Dim objFolder As Outlook.Folder
' excel
Dim sheetName As String
Dim objSheet As Worksheet
Set objOutlook = New Outlook.Application
Set objNameSpace = objOutlook.GetNamespace("MAPI")
' エクスポート対象のOutlookフォルダを選択する
Set objFolder = objNameSpace.PickFolder
If objFolder Is Nothing Then
MsgBox "処理を中断します", vbOKOnly, "中断"
Exit Sub
End If
' データ出力用シートの作成
sheetName = Format(Now, "yyyymm_hhnnss") & "_email"
Set objSheet = Sheets.Add
objSheet.Name = sheetName
' エクセルシートにヘッダを追加
Dim tmpHeader() As Variant
ReDim tmpHeader(1 To 4)
tmpHeader(1) = "受信日"
tmpHeader(2) = "送信者"
tmpHeader(3) = "タイトル"
tmpHeader(4) = "本文"
objSheet.Range("A1:D1") = tmpHeader
Dim rowIdx As Integer
rowIdx = 2
' フォルダ内のメール一覧を取得する
Dim tmpItem As MailItem
Application.ScreenUpdating = False
For Each tmpItem In objFolder.Items
objSheet.Cells(rowIdx, 1) = tmpItem.ReceivedTime
objSheet.Cells(rowIdx, 2) = tmpItem.SenderName
objSheet.Cells(rowIdx, 3) = tmpItem.Subject
objSheet.Cells(rowIdx, 4) = tmpItem.Body
rowIdx = rowIdx + 1
Next
Application.ScreenUpdating = True
End Sub