Solved

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

Posted on 2004-03-24
5
182 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Join & Write a Comment

Learn more about how the humble email signature can be used as more than just an electronic business card. When used correctly, a signature can easily be tailored for different purposes by different departments within an organization.
Create high volume marketing opportunities using email signatures with these top 10 DOs and DON'Ts of email signature marketing.
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…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

744 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

10 Experts available now in Live!

Get 1:1 Help Now