Macro that copies and pastes text from an Outlook e-mail into a Word document upon receipt.

[bdhopp]

Hello:

I want to create a custom action in Outlook that will--upon receipt of an e-mail from a specific sender--trigger a macro to copy the text in the Outlook e-mail and paste it into a Word or Excel document. Can anyone help me get started with this? Thank you!

 

[Low_Jack]

Hi all,

Is everyone still registered to mrexcel?
I'm new to VBA and I'm willing to create a macro similar to this thread to get some dates included in an email that I receive daily from the same person and in the same format.
Can anyone assist?
1) The email comes always from same person
2) The email's body contains a list of items with their price and there is a due date for each of them in the format of YYYY-MM-DD.
3) I want to extract all of the dates from that email and compare it with the current date to confirm it is not due. If so, I will pop up a essage window.

Can anyone help?
Thanks in advance,

Low_Jack

 

[hahemant]

Dear Sir,

I needed this macro. When I am running this, it is showing the error - "Compile Error - User-defined types not defined". Please suggest how to rectify this.

I want that the mail should be copied and pasted not on receipt, but on the click of a button on the active mail. I want the first line of the document to be the name of the document.

Thanks for your kind help.

Regards
Hemant

This was knocked up in a bit of a hurry but it seems to work.

First of all, in VBA add a reference to the Microsoft Word Object Library (Tools > References).

Paste the code in your ThisOutlookSession's code window. Change the bit in red to point to folder where you want the Word documents saved. The filename is made up from the word "Mail" plus the date and time the macro ran and a one-up serial number per email, like: "Mail_21Feb11_230113_0001" (.doc or.docx). This is to ensure we get a unique filename for every email.

The bit in pink is where the contents of the email get copied to the Word document. If you want to copy more stuff, just add more wordRange.InsertAfter commands.

The comment in green indicates where you can do some filtering on the incoming mail: if you start typing If objMailItem. you will get a drop-down list of the various properties you can test. Leave that for later - just get the code working first before you start modifying it.

Option Explicit
 
Private Sub Application_NewMailEx(ByVal EntryIDCollection As String)
 
  Dim objMailItem As MailItem
  Dim arrMailItems() As String
  Dim iCount As Integer
  
  Const SavePath As String = "[COLOR=red][B]C:\Temp\[/B][/COLOR]"
  Dim ThisDocument As String
  
  Dim wordApp As Word.Application
  Dim wordDoc As Word.Document
  Dim wordRange As Word.Range
  
  ThisDocument = [COLOR=blue][B]"Mail_" & Format(Now(), "ddmmmyy_hhnnss")
[/B][/COLOR]  
  arrMailItems = Split(EntryIDCollection, ",")
    
  For iCount = 0 To UBound(arrMailItems)
    Set objMailItem = Application.Session.GetItemFromID(arrMailItems(iCount))
[COLOR=green]    ' here you can [COLOR=green]check objMailItem.SenderName or objMailItem.Subject[/COLOR] here (for example)[/COLOR]
    Set wordApp = CreateObject("Word.Application")
    With wordApp
      .WindowState = Word.WdWindowState.wdWindowStateMaximize
      .Documents.Add ("normal.dotm")
      Set wordDoc = .ActiveDocument
      Set wordRange = wordDoc.Range
[COLOR=#ff00ff][B]      wordRange.InsertAfter objMailItem.Body
[/B][/COLOR]      .ActiveDocument.SaveAs SavePath & ThisDocument & "_" & Right("000" & CStr(iCount + 1), 4)
      .ActiveDocument.Close
      .Application.Quit
      Set wordDoc = Nothing
      Set wordApp = Nothing
    End With
  Next iCount
 
End Sub

Let me know how it goes?

 

[Ruddles]

First of all, in <acronym title="visual basic for applications">VBA</acronym> add a reference to the Microsoft Word Object Library (Tools > References).

Did you do that?

 

[hahemant]

Did you do that?

Dear Sir,

Thank you. Now it is working.

- Is it possible that the format of the mail is copied and pasted as it is ?

- I want that the mail should be copied and pasted not on receipt of mail, but on the click of a button on the active mail.

- I want the first line of the document to be the name of the document.

Thanks for your kind help.

Regards
Hemant

 

[Ruddles]

Is it possible that the format of the mail is copied and pasted as it is ?

I've no idea how to do this. If the mail is HTML, perhaps copy the HTML as well as the text.

I want that the mail should be copied and pasted not on receipt of mail, but on the click of a button on the active mail.

In that case, instead of placing the code in an event handler, you'll have to place it in a separate module and have that called by a button 'somehow'. I've never added a command button to Outlook so you'll have to Google how to do that, unless someone else can provide the answer.

A slightly less convenient way to call a macro would be to go Tools > Macro, but I guess you knew that already.

I want the first line of the document to be the name of the document.

As a general solution, the .Body or .HTMLbody property of the mail item contains the text of the mail. You would have to write some code to extract as much from the beginning of the .Body or .HTMLbody as you need (up to the first end-of-line character, perhaps) and use that to build a filename.

Plenty to keep you busy over the coming weekend then!