Capture.jpgCapture.jpg


I am trying to automate and capture the following details from the sent mail items into an excel sheet using VBA. The objective is - whenever I sent an email with a particular subject let's say " Index Coverage Request", then the following details should automatically get saved in the excel sheet . I am pretty new to vba and not sure how to extract data from email body.


- recipient email address
- sender email address
- Index Name

- sent date and time
- Email body


Code:


Public WithEvents objMails As Outlook. Items

Private Sub Application_Startup()
Set objMails = Outlook.Application.Session.GetDefaultFolder(olFoldersentitems).Items
End Sub

Private Sub objMails_ItemAdd(ByVal Item As Object)
Dim objMail As Outlook.MailItem
Dim strExcelFile As String
Dim objExcelApp As Excel.Application
Dim objExcelWorkBook As Excel.Workbook
Dim objExcelWorkSheet As Excel.Worksheet
Dim nNextEmptyRow As Integer
Dim strColumnB As String
Dim strColumnC As String
Dim strColumnD As String
Dim strColumnE As String

If Item.Class = olMail Then
Set objMail = Item
End If


strExcelFile = "E:\Email\Email Statistics.xlsx"

On Error Resume Next
Set objExcelApp = GetObject(, "Excel.Application")
If Error <> 0 Then
Set objExcelApp = CreateObject("Excel.Application")
End If
Set objExcelWorkBook = objExcelApp.Workbooks.Open(strExcelFile)
Set objExcelWorkSheet = objExcelWorkBook.Sheets("Sheet1")


nNextEmptyRow = objExcelWorkSheet.Range("B" & objExcelWorkSheet.Rows.Count).End(xlUp).Row + 1


strColumnB = objMail.ReceipentEmailAddress
strColumnC = objMail.SenderEmailAddress
strColumnD = objMail.SentTime
strColumnE = objMail.Body



objExcelWorkSheet.Range("A" & nNextEmptyRow) = nNextEmptyRow - 1
objExcelWorkSheet.Range("B" & nNextEmptyRow) = strColumnB
objExcelWorkSheet.Range("C" & nNextEmptyRow) = strColumnC
objExcelWorkSheet.Range("D" & nNextEmptyRow) = strColumnD
objExcelWorkSheet.Range("E" & nNextEmptyRow) = strColumnE

objExcelWorkSheet.Columns("A:E").AutoFit

objExcelWorkBook.Close SaveChanges:=True
End Sub




[1]: https://i.stack.imgur.com/YnEnk.png
[2]: https://i.stack.imgur.com/ddAH3.png