How to call VBA code from Outlook then VBA code from Excel?

Question

I have a project that needs both Outlook and Excel connections. What I need is:

1. Extract data of Unread emails from the Outlook Inbox (date received, sender, subject) then paste in Excel. See code below.

2. After data is pasted in Excel, format the extracted data (I already have the code in Excel) then save the Excel file.

3. Mark the emails that were extracted as "Read"

My Questions are:

• Is there a way to run the code automatically (ex: run at 7am, 2pm and 6am)?

• How will I set the emails as "read" after the information has been extracted?

• How will I select only "Unread" emails to be extracted?

• How can I connect my Outlook code to my Excel code and run it automatically?

Code in Outlook to extract data:

Sub Extract()
Set myOlApp = Outlook.Application
Set mynamespace = myOlApp.GetNamespace("mapi")
Set myfolder = myOlApp.ActiveExplorer.CurrentFolder
Set xlobj = CreateObject("Excel.Application")
xlobj.Visible = True
xlobj.workbooks.Add
'Set Heading
xlobj.Range("a" & 1).Value = "Received time"
xlobj.Range("b" & 1).Value = "Subject"
xlobj.Range("c" & 1).Value = "Importance"
xlobj.Range("d" & 1).Value = "Sender"
For i = 1 To myfolder.Items.Count
    Set myitem = myfolder.Items(i)
    xlobj.Range("a" & i + 1).Value = myitem.ReceivedTime
    xlobj.Range("b" & i + 1).Value = myitem.Subject
    xlobj.Range("c" & i + 1).Value = myitem.Importance
    xlobj.Range("d" & i + 1).Value = myitem.SenderName
Next
End Sub

Answer 1

Is there a way for me to run the macros automatically (ex: run the macro at 7am, 2pm and 6am)?

Not built into Excel. You could try a scheduled task in Windows (Accessories --> System Tools --> Task Scheduler) to open the Excel file (include parameters for file path and name). You would need to make Excel run that macro when it is opened (right click sheet, then "View Code" to add):

Private Sub Workbook_Open()
    Call MacroThatYouWantToRun()
End Sub

More details --> http://support.microsoft.com/kb/265113

I am not sure this can be just as easliy done in Outlook. Maybe this helps: Outlook VBA - Run a code every half an hour

How will I set the emails as "read" after the information has been extracted?

Outlook items apparently have an ".UnRead" property. Just set it to False for the items that you want to mark read.

How will I select only "Unread" emails to be extracted?

I'm not an expert but worst case, you can run a loop through all folder items and check that property for every item. If the .UnRead property is set to True, execute your command, i.e. copy some of its properties to Excel.

Also: Ways to run through all items in an Outlook folder have been previously discussed here:

• Can I iterate through all Outlook emails in a folder including sub-folders?
• Iterate all email items in a specific Outlook folder
• outlook vba select messages in sub-folder

How can I connect my outlook macro to my excel macro and run it automatically?

I don't think that is necessary. You can access one application from the other -- as you did in your example (Outlook --> Excel). If you want to go the other way round (Excel --> Outlook), consider something like:

' This is running in Excel, not Outlook
Dim objOutlook As Object
Set objOutlook = CreateObject("Outlook.Application")
With objOutlook
    ' Specify the commands you want to run in Outlook
End With

If you want to stick with running the macro from Outlook, try changing your code like this

' First line in Excel you want the copied data to go
Dim ExcelCount As Integer
ExcelCount = 1

For i = 1 To myfolder.Items.Count
Set myitem = myfolder.Items(i)
   ' Only copy data from unread items
   If myitem.UnRead = True Then
      xlobj.Range("a" & ExcelCount).Value = myitem.ReceivedTime
      xlobj.Range("b" & ExcelCount).Value = myitem.Subject
      xlobj.Range("c" & ExcelCount).Value = myitem.Importance
      xlobj.Range("d" & ExcelCount).Value = myitem.SenderName

      ' After copying the information, mark item as read
      myitem.UnRead = False

      ' Increase Excel line count
      ExcelCount = ExcelCount + 1
   End If
Next

Answer 2

You may find the How to automate Outlook from another program article helpful. The article provides an overview of programming Outlook using Automation from another program.