如何使用VBA或Macros将Outlook邮件复制到excel中 [英] How to copy Outlook mail message into excel using VBA or Macros

问题描述

这是我的第一篇文章。
我是VBA和Macros的新手。如果有人帮助我使用VBA代码和宏，这将是有帮助的。

This is my first post. I'm a newbie in VBA and Macros. If someone helps me with VBA code and macros, it will be helpful.

每天我会收到大约50-60个邮件与一个标准主题：任务完成。我已经创建了一个规则，所有这些邮件移动到一个特定的文件夹：任务完成。

Daily I'll receive around 50-60 mails with one standard subject: "Task Completed". I have created a rule to all those mail to move to a specific folder: "Task Completed".

每天阅读所有50-60个邮件，更新所有邮件是非常花费很多时间
所有50-60邮件到我的收件箱将具有相同的主题，但来自不同的用户。
邮件正文将有所不同。

Reading all 50-60 mails a day and updating all mails is very much time consuming. All 50-60 mails coming to my inbox will have same subject but from different users. Body of mail will vary.

我正在使用Outlook 2010和Excel 2010

I'm using Outlook 2010 and Excel 2010

谢谢并且您的所有帮助将不胜感激。

Thanks and all your help will be much appreciated.

推荐答案

由于你没有提到需要复制的内容，所以我在下面的代码中将该部分留空。

Since you have not mentioned what needs to be copied, I have left that section empty in the code below.

此外，您不需要先将电子邮件移动到该文件夹​​，然后在该文件夹中运行该宏。您可以在接收的邮件上运行宏，然后将其移动到同一时间的文件夹。

Also you don't need to move the email to the folder first and then run the macro in that folder. You can run the macro on the incoming mail and then move it to the folder at the same time.

这将让您开始。我已经对代码进行了评论，所以你不会面对任何问题。

This will get you started. I have commented the code so that you will not face any problem understanding it.

首先将下面提到的代码粘贴到outlook模块中。

First paste the below mentioned code in the outlook module.

然后

1. 点击工具~~>规则和提醒


2. 点击新规则


3. 点击从空白规则开始


4. 选择查看邮件到达时


5. 在条件下，单击主题


6. 点击规则说明下的特定单词。


7. 在弹出的对话框中输入要检查的单词单击添加。


8. 单击确定，然后单击下一步


9. 选择将其移动到指定的文件夹 / strong>在同一个框中选择运行脚本


10. 在下面的框中，指定特定文件夹以及脚本（模块中具有的宏）运行。 / li>
    
11. 点击完成，你完成了。

1. Click on Tools~~>Rules and Alerts
2. Click on "New Rule"
3. Click on "start from a blank rule"
4. Select "Check messages When they arrive"
5. Under conditions, click on "with specific words in the subject"
6. Click on "specific words" under rules description.
7. Type the word that you want to check in the dialog box that pops up and click on "add".
8. Click "Ok" and click next
9. Select "move it to specified folder" and also select "run a script" in the same box
10. In the box below, specify the specific folder and also the script (the macro that you have in module) to run.
11. Click on finish and you are done.

当新的电子邮件到达时，电子邮件将不仅移动到您指定的文件夹，而且还会将数据从Excel导出为Excel

When the new email arrives not only will the email move to the folder that you specify but data from it will be exported to Excel as well.

UNTESTED

Const xlUp As Long = -4162

Sub ExportToExcel(MyMail As MailItem)
    Dim strID As String, olNS As Outlook.Namespace
    Dim olMail As Outlook.MailItem
    Dim strFileName As String

    '~~> Excel Variables
    Dim oXLApp As Object, oXLwb As Object, oXLws As Object
    Dim lRow As Long

    strID = MyMail.EntryID
    Set olNS = Application.GetNamespace("MAPI")
    Set olMail = olNS.GetItemFromID(strID)

    '~~> Establish an EXCEL application object
    On Error Resume Next
    Set oXLApp = GetObject(, "Excel.Application")

    '~~> If not found then create new instance
    If Err.Number <> 0 Then
        Set oXLApp = CreateObject("Excel.Application")
    End If
    Err.Clear
    On Error GoTo 0

    '~~> Show Excel
    oXLApp.Visible = True

    '~~> Open the relevant file
    Set oXLwb = oXLApp.Workbooks.Open("C:\Sample.xls")

    '~~> Set the relevant output sheet. Change as applicable
    Set oXLws = oXLwb.Sheets("Sheet1")

    lRow = oXLws.Range("A" & oXLApp.Rows.Count).End(xlUp).Row + 1

    '~~> Write to outlook
    With oXLws
        '
        '~~> Code here to output data from email to Excel File
        '~~> For example
        '
        .Range("A" & lRow).Value = olMail.Subject
        .Range("B" & lRow).Value = olMail.SenderName
        '
    End With

    '~~> Close and Clean up Excel
    oXLwb.Close (True)
    oXLApp.Quit
    Set oXLws = Nothing
    Set oXLwb = Nothing
    Set oXLApp = Nothing

    Set olMail = Nothing
    Set olNS = Nothing
End Sub

FOLLOWUP

要从您的电子邮件正文中提取内容，您可以使用SPLIT（）拆分内容，然后从中解析出相关信息。看到这个例子

To extract the contents from your email body, you can split it using SPLIT() and then parsing out the relevant information from it. See this example

Dim MyAr() As String

MyAr = Split(olMail.body, vbCrLf)

For i = LBound(MyAr) To UBound(MyAr)
    '~~> This will give you the contents of your email
    '~~> on separate lines
    Debug.Print MyAr(i)
Next i

这篇关于如何使用VBA或Macros将Outlook邮件复制到excel中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！