We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

RUSS_EMI
RUSS_EMI asked
on
Medium Priority
207 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
Comment
Watch Question

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.

Author

Commented:
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

Author

Commented:
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.
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.