I have a rule setup to move emails based on subject line. What I am attempting to do is when this rule is triggered, it also grabs the Body, Sent Date, and Sender and places it into an Excel Spreadsheet. After doing a bit of research I have come up with the following and the issue i'm having is getting the Msg Object set properly so I can pull the data from the email. Anyone able to assist?
(Xpost from ExcelForum.com)
(Xpost from ExcelForum.com)
Code:
Option Explicit
Sub SendToSAMM()
Dim v As Variant
Dim MSndr, MSdt, MSbdy As String
Dim xlApp, xlSht, Msg As Object
Dim R As Integer
Dim MyMail As MailItem 'Move this to () upon seccess of macro
'============================================================================================
' Checks to see if workbook is open and if not opens it up
'============================================================================================
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Set xlApp = CreateObject("Excel.Application")
xlApp.Application.Visible = True 'Remove upon success of macro
xlApp.Workbooks.Open "R:\S***********\K******\S**** S**** A**** M****.xlsm"
Else
End If
'============================================================================================
' Currently having issues with MyMail not being set.
'============================================================================================
On Error GoTo Handler
'Set Msg = ?????????????
Set xlSht = xlApp.Sheets("Email")
R = xlSht.Range("A:A").Find("").Row
With xlSht
.cells(R, 2).Value = Msg.SentOn
.cells(R, 3).Value = Msg.Sender
.cells(R, 4).Value = Msg.Body
End With
Exit Sub
Handler:
Debug.Print "Error occured: " & Err.Description
End Sub