[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1132
  • Last Modified:

Outlook parse incoming mail VBA

Can someone tell me how to code VBA in Outlook.
I am accustomed to VBA in Excel, I do custom ribbons and addins etc, userforms, but have no idea how to put VBA code into Outlook.

I want Outlook to parse the body, subject, to and from fields of every incoming email and save the text to a SQL database. I know how to send commands in SQL language, that bit is no problem.
Then I want Outlook to save the email message in a Windows folder (just like when you go Ctrl + C on an email and then Ctrl + V in a Windows folder and it pastes the email message as a file).
If you need more info let me know.
Outlook versions are all either 2007 or 2010.
0
p-plater
Asked:
p-plater
  • 2
1 Solution
 
Kannan KManager - EngineeringCommented:
Hi,

even I would like know about more like this requirement in outlook, experts please help us.

KK,
0
 
redmondbCommented:
Hi, p-plater.

First some warnings...
(1) My VBA skills are primarily in Excel. I'm at little further into Outlook than you are, but not a lot. If a true Outlook expert comes along - go with him/her!
(2) There's some noise (including from Microsoft) about events not being fired when a "lot" of emails are received. I've not seen any definition of "lot".
(3) If you're an Exchange user then all bets are off.
(4) Outlook Rules and anti-virus/spam operate asynchronously, and so may interfere with the macro.
(5) You may get errors for "funny" items (e.g. Meeting requests, Read Receipts).

Still here?! OK, open the Visual Basic Editor. You should see "Microsoft Outlook Objects" in the Project Explorer pane. Expand that to display ThisOutlookSession. Double-click on that and enter the following code...
Private Sub Application_NewMailEx(ByVal EntryIDCollection As String)
Dim varEntryIDs
Dim objItem As MailItem
Dim i As Integer, j As Integer
Dim xRecipients As String

varEntryIDs = Split(EntryIDCollection, ",")
For i = 0 To UBound(varEntryIDs)
    Set objItem = Application.Session.GetItemFromID(varEntryIDs(i))
    For j = 1 To objItem.Recipients.Count
        xRecipients = xRecipients & objItem.Recipients(j).Address & ";"
    Next
    Debug.Print "NewMailEx - " & varEntryIDs(i) & " :: " & objItem.subject & " :: " & xRecipients & " :: " & objItem.SenderEmailAddress & " :: " & Mid(objItem.body, 1, 99)
    objItem.SaveAs "D:\" & varEntryIDs(i) & ".msg", olMSG
Next

End Sub

Open in new window

For each email received, this debug.prints so sample fields. It also saves the email to the D drive named with the email's EntryID.

Regards,
Brian.

0
 
p-platerAuthor Commented:
Thanks heaps
0
 
redmondbCommented:
Thanks, p-plater!
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now