Macro that copies from body of email to a specific Excel file

greyfort

New Member
Joined
Oct 12, 2015
Messages
27
By google I see that it IS possible to write a macro that will copy the body of an email to an excel workbook, but I can't make heads or tails over what I'm finding. For example I am trying to understand this code (below). I think part of the problem (besides the obvious - that I really don't know VBA) is that most of the samples I am finding are just trying to pull part of the body of the email. Or are trying to pull the subject line, etc... I just need the entire body copied over.

Would someone point me in the right direction?

(One of the sources I've been trying to use: https://www.slipstick.com/developer/vba-copy-outlook-email-excel-workbook/)
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
First, I notice where the body is put in to a string:

Code:
sText = olItem.Body

So, what is olItem?

Code:
Sub CopyToExcel(olItem As Outlook.MailItem)

This means that you have to pass the message to this sub as a parameter.

Let's look at the next example they have "Sub CopyAllMessagesToExcel()"

Code:
Set objFolder = objOL.ActiveExplorer.CurrentFolder
    Set objItems = objFolder.Items
    For Each olItem In objItems 
      On Error Resume Next
     With olItem
     sText = olItem.Body

This says go through every mail item in "current folder" and put the body in to "sText". You would want to delete everything after "sText = olItem.Body" and write your code to deal with the body using that variable.

This could take a very long time if you run this on the "inbox" so my question is:

How do you plan on selecting the mailbox item that you want to copy?
 
Upvote 0
Thank you Hackslash for your reply!

Ideally it would be a Ribbon - Macro Assigned Button as that's what I've been doing with everything else. I will play with the code again based on your response and see if it starts making more sense now :D

Thanks!
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top