Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2004-03-24
5
Medium Priority
?
188 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Read this checklist to learn more about the 15 things you should never include in an email signature.
By default Outlook 2016 displays only one time zone in the Calendar. The following article explains how to display two time zones in one calendar view.
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

664 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