Solved

How do I parse the body of an email, and export the fields into Excel using VBA

Posted on 2004-03-24
5
183 Views
Last Modified: 2010-08-05
I have an email body that is constructed as follows:

NAME
ORG
DATE
TIME
LOCATION

Each line item is to be a separate field in Excel.  How do I access each line uniquely?
Thanks,
Russ
0
Comment
Question by:RUSS_EMI
  • 2
5 Comments
 
LVL 1

Expert Comment

by:millicent_lam
ID: 10678037
You can only export data from an email to Excel if you are using a custom form. Every item that you open in Outlook uses a form to display information. E.g. email messages use the Message form, a new contact is entered via the Contact form.

In your case, you will need to customise the Message form, publish it in a Forms Library, and only then you can export data into Excel using VBScript or VBA, depending on what version of Outlook you are using.

www.slipstick.com is a good place to start with.
0
 

Author Comment

by:RUSS_EMI
ID: 10684445
The code stops after Set ex = CreateObject("Excel.Applicaton").
Here is my complete code, running within the Rules Wizard as a script in Outlook.

Sub TestMessageRule(Item As Outlook.MailItem)
Dim lines As Variant
Dim sheet
Dim ex
Dim x As Long

lines = Split(Item.Body, VbCr)
Set ex = CreateObject("Excel.Applicaton")
Set objWB = ex.Workbooks.Open("c:\tryme.xls")
Set sheet = objWB.Sheets.Item(1)

For x = 0 To UBound(lines)
              sheet.cells(1, x).Value = lines(x)
Next x

objWB.Save
ex.Quit
Set ex = Nothing
End Sub

Thanks,
Russ
0
 

Author Comment

by:RUSS_EMI
ID: 10685039
I found the problem.  No longer need assistance with this exact question.  Need to close out and refund points.  Need help with appending data to Excel, rather than creating a new one each time.
0
 
LVL 1

Accepted Solution

by:
GhostMod earned 0 total points
ID: 11019371
PAQed, with points refunded (500)

GhostMod
Community Support Moderator
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What does UTC stand for?  “Coordinated Universal Time” – Think of this as the true time on Planet Earth that never changes with the exception of minor leap seconds here and there to account for the changes in the planet's rotation.   What does th…
Follow this checklist to learn more about the 15 things you should never include in an email signature from personal quotes, animated gifs and out-of-date marketing content.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now