i am trying to figure out how to have an excel file updated every time an email arrives, I would like to have
Sender
Sent time
Subject
Body
in four separate columns. I know this may not be the appropriate forum but I can not seem to figure out how to do this and you guys seem to have the answers to everything. I am using 2010. Any suggestions would be helpful. Thanks!
I found this code on mrexcel, but I haevnt gotten it to work yet, run time error 1004, it opens the excel sheet- the first time it dumped information in one cell but I have not been able to get it to process anything since then.
Private Sub Application_NewMail()
Dim objNS As NameSpace
Dim objFolder As MAPIFolder
Dim myItem As MailItem
Dim myXLApp As Excel.Application
Dim myXLWB As Excel.Workbook
Dim StrBody As String
Dim TotalRows As Long, i As Long
Set objNS = GetNamespace("MAPI")
Set objFolder = objNS.GetDefaultFolder(olFolderInbox)
Set myItem = objFolder.Items(1)
Set myXLApp = New Excel.Application
myXLApp.Visible = True
Set myXLWB = myXLApp.Workbooks.Add
TotalRows = Sheets(1).Range("A65536").End(xlUp).Row
i = TotalRows + 1
With myXLWB.Worksheets(1)
.Cells(i, 1) = Format(myItem.SentOn, "mm/dd/yyyy")
.Cells(i, 2) = myItem.SenderName
.Cells(i, 3) = myItem.To
.Cells(i, 4) = myItem.Body
End With
End Sub
Bookmarks