Copying body of email into excel

Question

Hi all, I am trying to automate a process whereby i receive an email via outlook with a particular subject line every day and i want to copy the body of the email into an existing excel spreadsheet and save it.
It's the first time i've really used <acronym title="visual basic for applications">vba</acronym> in outlook, and i have written one to save any attachments which works ok using rules/run a script, but i'm stuck when it comes to copying the body
It would be really helpful if when pasting into excel it didn't go all into one cell and just pasted over like if you were performing it manually
I'd be very grateful for any guidance anyone can give me?? I am using Outlook and Excel 2010, Many Thanks

Answer 1

Assuming you want to perform this operation through Microsoft Outlook I would post your question to the Outlook for Developers forum:

http://social.msdn.microsoft.com/Forums/en/outlookdev/threads

This forum is primarily for Visual Basic .NET.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 2

If you are using a rule to run a script, that "script" has a reference to the mail item being processed. If your reference is "mail" then mail.Body would retrieve the plain text body as a string value. Using mail.HTML would retrieve the HTML content as raw HTML into the string return value.

 

Is that what you wanted?

--
Ken Slovak
[MVP-Outlook]
http://www.slovaktech.com
Author: Professional Programming Outlook 2007

"Knelly412" <=?utf-8?B?S25lbGx5NDEy?=> wrote in message news:cd8583f1-01ad-4409-a792-054b14f94108...

Hi all, I am trying to automate a process whereby i receive an email via outlook with a particular subject line every day and i want to copy the body of the email into an existing excel spreadsheet and save it.
It's the first time i've really used <acronym title="visual basic for applications">vba</acronym> in outlook, and i have written one to save any attachments which works ok using rules/run a script, but i'm stuck when it comes to copying the body
It would be really helpful if when pasting into excel it didn't go all into one cell and just pasted over like if you were performing it manually
I'd be very grateful for any guidance anyone can give me?? I am using Outlook and Excel 2010, Many Thanks

---

Ken Slovak MVP - Outlook

Answer 3

Is the body of the email a set of data?  If so, is it one line of data or several?

If it's just one line of data, once you've pasted the text body of the email into the Excel cell, make a Range.TextToColumns call, configured to match the delimiter or fixed-width format of the email, to spread the data across the appropriate number of columns.

Answer 4

Body is a string representing the plain text content of the email. It can be one or many lines, depending on the content.

 

HTMLBody is also a string, containg raw HTML (all the tags and such). Again it can have as many lines as the author of the email put into the item. It can also contain the HTML for inline image attachments as "cid" references to attachments.

 

If one were to want to split a plain text body on each line one would use a string Split() function and split on newlines. Depending on the platform and language that could be Environment.Newline, vbCRLF or something else. To split an HTML string is harder as one must parse for <br>, <p>, and various other methods of formatting the text.

--
Ken Slovak
[MVP-Outlook]
http://www.slovaktech.com
Author: Professional Programming Outlook 2007

"Brian Skinn" <=?utf-8?B?QnJpYW4gU2tpbm4=?=> wrote in message news:4338e2ce-5705-4d7f-aad8-33c163727208...

Is the body of the email a set of data?  If so, is it one line of data or several?

---

Ken Slovak MVP - Outlook