Macro to Copy Specific content from Mail Body and Paste to Excel

Status
Not open for further replies.

Harish Shrivastava

New Member
Outlook version
Outlook 2013 32 bit
Email Account
Hi,

Please help me out with an Outlook macro which copies and pastes specific (mentioned below) text from mail body and paste it to excel sheet one after another.

Test to copy looks like:
File Date: 8/19/2016 4:25:20 AM
File Size: 159457 bytes
Records: 8586

This should be pasted horizontally. Only the data after the colon.

What i want is whenever an email comes to a specific folder the macro should run and it should copy the data to excel automatically.

It would be a great help guys. Thanks in advance :)
 
Thank you Diane for the reply. It would be really helpful if you help me out with below 2 additions in your code as given in the article:

1. The macro should get the text from an email every time it comes to a particular inbox folder
2. It should paste the text in an Excel sheet one after one.

Thank you for your help.
 
Replace the code in Use a macro to copy data in Outlook email to Excel workbook between the With Reg1 and the End If block right before xlSheet.Range("B" & rCount) = vText with the following.


Change the macro name to
Private Sub objItems.ItemAdd(ByVal Item As Object)

Then get the first macro at How to use an ItemAdd Macro and set the folder you want to watch. Working with VBA and non-default Outlook Folders has info on how to refer to different folders.

(the macros all go into ThisOutlookSession)

Code:
For i = 1 To 3

With Reg1
    Select Case i
    Case 1
        .Pattern = "File Date:\s((.*))\n"
        .Global = False
       
    Case 2
       .Pattern = "File Size:\s(([\d]*))\n"
       .Global = False
      
    Case 3
        .Pattern = "Records:\s(([\d]*))\n"
        .Global = False
    End Select
   
End With
   
   
    If Reg1.test(olMail.Body) Then
    
        Set M1 = Reg1.Execute(olMail.Body)
        For Each M In M1
            Debug.Print M.SubMatches(1)
            strSubject = M.SubMatches(1)
           
         if i = 1 then vText = strSubject
         if i = 2 then vText2 = strSubject
         if i = 3 then vText3 = strSubject
        
         Next
    End If
         
Next i
 
I *think* those patterns will work - you might need to tweak them a bit though. And, of course, you'll need to refer to the correct workbook and sheet.
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
R Macro to copy email to excel - Runtime Error 91 Object Variable Not Set Outlook VBA and Custom Forms 11
Diane Poremsky Use a macro to copy data in Outlook email to Excel workbook Using Outlook 0
Diane Poremsky Use a macro to copy data in Outlook email to Excel workbook Using Outlook 0
T Macro to find contacts by category and copy them to another folder Outlook VBA and Custom Forms 15
A Macro to Copy Data from Outlook to Excel Spreadsheet Using Outlook 2
Aussie Looking for Outlook macro to Copy Recipient Names into Email Body Outlook VBA and Custom Forms 3
A Macro to copy email body to new email Outlook VBA and Custom Forms 5
R Macro to copy email address Using Outlook 6
X Custom icon (not from Office 365) for a macro in Outlook Outlook VBA and Custom Forms 1
X Run macro automatically when a mail appears in the sent folder Using Outlook 5
mrrobski68 Issue with Find messages in a conversation macro Outlook VBA and Custom Forms 1
G Creating Macro to scrape emails from calendar invite body Outlook VBA and Custom Forms 6
M Use Macro to change account settings Outlook VBA and Custom Forms 0
J Macro to Reply to Emails w/ Template Outlook VBA and Custom Forms 3
C Outlook - Macro to block senders domain - Macro Fix Outlook VBA and Custom Forms 1
Witzker Outlook 2019 Macro to seach in all contact Folders for marked Email Adress Outlook VBA and Custom Forms 1
S macro error 4605 Outlook VBA and Custom Forms 0
A Macro Mail Alert Using Outlook 4
J Outlook 365 Outlook Macro to Sort emails by column "Received" to view the latest email received Outlook VBA and Custom Forms 0
J Macro to send email as alias Outlook VBA and Custom Forms 0
M Outlook Macro to save as Email with a file name format : Date_Timestamp_Sender initial_Email subject Outlook VBA and Custom Forms 0
Witzker Outlook 2019 Macro GoTo user defined search folder Outlook VBA and Custom Forms 6
D Outlook 2016 Creating an outlook Macro to select and approve Outlook VBA and Custom Forms 0
Witzker Outlook 2019 Macro to send an Email Template from User Defined Contact Form Outlook VBA and Custom Forms 0
Witzker Outlook 2019 Macro to check Cursor & Focus position Outlook VBA and Custom Forms 8
V Macro to mark email with a Category Outlook VBA and Custom Forms 4
M Outlook 2019 Macro not working Outlook VBA and Custom Forms 0
S Outlook 365 Help me create a Macro to make some received emails into tasks? Outlook VBA and Custom Forms 1
Geldner Send / Receive a particular group via macro or single keypress Using Outlook 1
D Auto Remove [EXTERNAL] from subject - Issue with Macro Using Outlook 21
V Macro to count flagged messages? Using Outlook 2
sophievldn Looking for a macro that moves completed items from subfolders to other subfolder Outlook VBA and Custom Forms 7
S Outlook Macro for [Date][Subject] Using Outlook 1
E Outlook - Macro - send list of Tasks which are not finished Outlook VBA and Custom Forms 3
E Macro to block senders domain Outlook VBA and Custom Forms 1
D VBA Macro to Print and Save email to network location Outlook VBA and Custom Forms 1
N VBA Macro To Save Emails Outlook VBA and Custom Forms 1
N Line to move origEmail to subfolder within a reply macro Outlook VBA and Custom Forms 0
Witzker Outlook 2019 Macro to answer a mail with attachments Outlook VBA and Custom Forms 2
A Outlook 2016 Macro to Reply, ReplyAll, or Forward(but with composing new email) Outlook VBA and Custom Forms 0
J Macro to Insert a Calendar Outlook VBA and Custom Forms 8
W Macro to Filter Based on Latest Email Outlook VBA and Custom Forms 6
T Macro to move reply and original message to folder Outlook VBA and Custom Forms 6
D Autosort macro for items in a view Outlook VBA and Custom Forms 2
S HTML to Plain Text Macro - Help Outlook VBA and Custom Forms 1
A Macro to file emails into subfolder based on subject line Outlook VBA and Custom Forms 1
N Help creating a VBA macro with conditional formatting to change the font color of all external emails to red Outlook VBA and Custom Forms 5
S Visual indicator of a certain property or to show a macro toggle Outlook VBA and Custom Forms 2
L Modifying VBA script to delay running macro Outlook VBA and Custom Forms 3
S Macro to extract and modify links from emails Outlook VBA and Custom Forms 3

Similar threads

Back
Top