Auto Expot to Excel

  • Thread starter Thread starter blackbox via OfficeKB.com
  • Start date Start date
B

blackbox via OfficeKB.com

Is it possible to automatically export the subject line and date/time
received to excel?

Ideally only certain subject lines but all would be fine as well.

Thanks
 
all I really want to see is the Subject and the date/time received for 5
different emails.
Ideally, I'd like it to export those 2 pieces on information to specific
cells in Excel as soon as the email arrives. They are generated by another
program, so the subject of each email is always the same.

right now I have Excel retrieving that info but it's set on timers and each
has a delayed loop, in case the email is late. The problem is, this
interrupts other code that is running.

I thought it might be smoother if it was in Outlook running off a Change
event that watches the Inbox count or something.

something like this

If Subject = "XXXXX" and Received Date = Today() Then export Subject to
Excel(MyWorkbook) Range ("A1") and Received date/time to Range ("B1")
 
Thanks I'll take a look
Sounds like all you need to do is stick with your Excel code and replace the
timer even by an Outlook event: Please see the ItemAdd event; a sample is
availabe in Outlook's VBA help.
all I really want to see is the Subject and the date/time received for 5
different emails.
Click to expand...
[quoted text clipped - 22 lines]
Click to expand...
 
Hi! Try someting like this.

Code: 
Sub WriteToExcel()
'don't forget to register your reference to Excel Oject Library!
Dim objXLS, objItem As Object
Dim myfile As String
Dim lastrow As Integer
Dim objOutlook As Outlook.Application
'binding with outlook object library
Set objOutlook = CreateObject("Outlook.Application")
'get active mail item
Set objItem = objOutlook.ActiveExplorer.Selection.Item(1)
	myfile = "C:\Temp\TEST.XLS"
'open an Excel application
	Set objXLS = CreateObject("excel.application")
	objXLS.Application.Visible = True
	objXLS.workbooks.Open (myfile)				 'open your excel file
	objXLS.worksheets(1).Range("A1").Select
' Find the last row
objXLS.Range("A65536").End(xlUp).Select
	objXLS.ActiveCell.Offset(0, 1).Value = objItem.ReceivedTime 'remember to always refer to your object
	objXLS.ActiveCell.Offset(0, 2).Value = objItem.Subject
	objXLS.ActiveWorkbook.Close SaveChanges:=True 'save excel file
	objXLS.Quit									 'quit excel
	Set objXLS = Nothing
Set objOutlook = Nothing
Set objItem = Nothing
End Sub
 
Back
Top