'邮件自动转发处理子程序

'功能：根据发件人过滤，读取未读邮件，转发邮件

'

'

Sub AutoForward(rcvMail As Outlook.MailItem)

    '定义邮件转发项目

    Dim myAutoForwardMailItem As Outlook.MailItem

    Dim rcvhtmlBody  As String

    Dim rcvBody As String

    Dim mto As String

    Dim ie, dmt, bd

    '定义邮件体

    Dim myAutoForwardHTMLBody As String

    '创建邮件体

    myForwardHTMLBody = CreateHTMLBody(2)

    If (rcvMail.UnRead) And (rcvMail.SenderEmailAddress = "942387T841U@qq.com") Or (rcvMail.SenderEmailAddress = "rdmod01@163.com") Or (rcvMail.SenderEmailAddress = "ju.li@163.com") Then

        '将邮件设为已读

        rcvMail.UnRead = False

        '设置转发器

        Set myAutoForwardMailItem = rcvMail.ReplyAll

        '设置收件人

        myAutoForwardMailItem.Recipients.Add "xx@qq.com"

        myAutoForwardMailItem.Recipients.Add "yy@qq.com"

         rcvhtmlBody = rcvMail.HTMLBody

         rcvBody = rcvMail.Body

         mto = rcvMail.To

         Debug.Print ("htmlBody string: " & rcvhtmlBody)

         Debug.Print ("Body string: " & rcvBody)

         Debug.Print ("Recipients: " & mto)

        '处理邮件内容

         Set ie = CreateObject("InternetExplorer.Application")

        '设置邮件体格式为outlook html格式

        myAutoForwardMailItem.BodyFormat = olFormatHTML

        '将原始邮件与新邮件连起来

        myAutoForwardMailItem.HTMLBody = myForwardHTMLBody & myAutoForwardMailItem.HTMLBody

        'Displays a new Inspector object for the item.

        'myAutoForwardMailItem.Display

        '发送邮件

        'Sends the e-mail message.

        myAutoForwardMailItem.Send

        '原保存邮件

        'Saves the Microsoft Outlook item to the current folder or, if this is a new item, to the Outlook default folder for the item type.

        rcvMail.Save

    End If

    '清空对象

    Set rcvMail = Nothing

    Set myAutoReplyMailItem = Nothing

End Sub

Sub AutoForward1()

   Debug.Print ("xxx:" & RemoveHTML)

End Sub

Public Function CreateHTMLBody(id As Integer) As String

    'Creates a new e-mail item and modifies its properties

    Dim objHTMLBody As String

    '可以设置多个模板

    If id = 1 Then

        objHTMLBody = _

        "<font face = 微软雅黑 size = 3>" & _

        "感谢你的来信。我是<font color=red>机器人小星</font>，邮件我已代为阅读。" & _

        "<br/> <br/> " & _

        "来自小星的智能转发</font>"

    ElseIf id = 2 Then

       objHTMLBody = _

       "<table style = border-collapse:collapse <tbody>" & _

       "<tr><td style = border:1px solid #B0B0B0 colspan= 2>版本</td></tr>" & _

       "<tr><td style= border:1px solid #B0B0B0 >APP版本</td></tr>" & _

       "<tr><td style = border:1px solid #B0B0B0>SDK版本</td></tr>" & _

       "</tbody></table>" & _

       "" & _

       "<br/> <br/> " & _

       "来自小星的智能回复</font>"

    End If

    CreateHTMLBody = objHTMLBody

End Function

Sub test()

  Dim str As String

  Dim result As String

  str = _

       "<table style = border-collapse:collapse <tbody>" & _

       "<tr><td style = border:1px solid #B0B0B0 colspan= 2>版本</td></tr>" & _

       "<tr><td style= border:1px solid #B0B0B0 >APP版本</td></tr>" & _

       "<tr><td style = border:1px solid #B0B0B0>SDK版本</td></tr>" & _

       "</tbody></table>" & _

       "" & _

       "<br/> <br/> " & _

       "来自小星的智能回复</font>"

  result = RemoveHTML(str)

  Debug.Print (result)

End Sub

'移除html标签

Public Function RemoveHTML(strText As String)

    Dim nPos1

    Dim nPos2

     Debug.Print ("Body string: ")

    nPos1 = InStr(strText, "<")

    Do While nPos1 > 0

        nPos2 = InStr(nPos1 + 1, strText, ">")

        If nPos2 > 0 Then

            strText = Left(strText, nPos1 - 1) & Mid(strText, nPos2 + 1)

        Else

            Exit Do

        End If

        nPos1 = InStr(strText, "<")

    Loop

    RemoveHTML = strText

End Function

Sub searchWebViaIE()

    Dim ie As SHDocVw.InternetExplorer

    Dim doc As MSHTML.HTMLDocument

    Dim anchors As MSHTML.IHTMLElementCollection

    Dim anchor As MSHTML.HTMLAnchorElement

    Dim prodSpec As MSHTML.HTMLAnchorElement

    Dim tableCells As MSHTML.IHTMLElementCollection

    Dim materialValueElement As MSHTML.HTMLTableCell

    Dim tableCell As MSHTML.HTMLTableCell

    Set ie = New SHDocVw.InternetExplorer

    'Set ie = CreateObject("InternetExplorer.Application")

    With ie

        .navigate "http://www.baidu.com"

        .Visible = True

        Do While .readyState <> READYSTATE_COMPLETE Or .Busy = True

            DoEvents

        Loop

        Set doc = .document

        Set anchors = doc.getElementsByTagName("a")

        For Each anchor In anchors

            If InStr(anchor.innerHTML, "Product Specificatie") <> 0 Then

                anchor.Click

                Exit For

            End If

        Next anchor

        Do While .readyState <> READYSTATE_COMPLETE Or .Busy = True

            DoEvents

        Loop

    End With

    For Each anchor In anchors

        If InStr(anchor.innerHTML, "Product Specificatie") <> 0 Then

            Set prodSpec = anchor

        End If

    Next anchor

    Set tableCells = doc.getElementById("list-table").getElementsByTagName("td")

    If Not tableCells Is Nothing Then

        For Each tableCell In tableCells

            If tableCell.innerHTML = "Materiaal" Then

                Set materialValueElement = tableCell.NextSibling

            End If

        Next tableCell

    End If

    MsgBox materialValueElement.innerHTML

End Sub

'邮件自动转发处理子程序

'功能：根据发件人过滤，读取未读邮件，转发邮件

'

'

Sub AutoForward(rcvMail As Outlook.mailitem)

    '定义邮件转发项目

    Dim myAutoForwardMailItem As Outlook.mailitem

    Dim rcvhtmlBody  As String

    Dim rcvBody As String

    Dim mto As String

    'Dim ie, dmt, bd

    Dim sender As String

    '定义邮件体

    Dim myAutoForwardHTMLBody As String

    Dim ie As SHDocVw.InternetExplorer

    Dim doc As MSHTML.HTMLDocument

    '创建邮件体

    myForwardHTMLBody = CreateHTMLBody(2)

    If (rcvMail.UnRead) And (rcvMail.SenderEmailAddress = "94s841@qq.com") Or (rcvMail.SenderEmailAddress = "rdmod01@163.com") Or (rcvMail.SenderEmailAddress = "ju.li@163.com") Then

        '将邮件设为已读

        rcvMail.UnRead = False

        '设置转发器

        Set myAutoForwardMailItem = rcvMail.ReplyAll

        'rcvMail.Attachments.item(1).SaveAsFile ("D:\")

        '设置收件人

        myAutoForwardMailItem.Recipients.Add "2s3016@qq.com"

        myAutoForwardMailItem.Recipients.Add "129s615@qq.com"

         rcvhtmlBody = rcvMail.HTMLBody

         rcvBody = rcvMail.body

         mto = rcvMail.To

         Debug.Print ("htmlBody string: " & rcvhtmlBody)

         Debug.Print ("Body string: " & rcvBody)

         Debug.Print ("Recipients: " & mto)

        '处理邮件内容

         'Set ie = CreateObject("InternetExplorer.Application")

         '保存附件

         saveAttachments rcvMail

         '解析邮件主体

         resolveAttach

         Set ie = New SHDocVw.InternetExplorer

         'Set doc = .document

        '设置邮件体格式为outlook html格式

        myAutoForwardMailItem.BodyFormat = olFormatHTML

        '将原始邮件与新邮件连起来

        myAutoForwardMailItem.HTMLBody = myForwardHTMLBody & myAutoForwardMailItem.HTMLBody

        'Displays a new Inspector object for the item.

        'myAutoForwardMailItem.Display

        '发送邮件

        'Sends the e-mail message.

        myAutoForwardMailItem.Send

        '原保存邮件

        'Saves the Microsoft Outlook item to the current folder or, if this is a new item, to the Outlook default folder for the item type.

        rcvMail.Save

    End If

    '清空对象

    Set rcvMail = Nothing

    Set myAutoReplyMailItem = Nothing

End Sub

Sub saveAttachments(mailitem As Outlook.mailitem)

    Dim olAtt As Attachment

    Dim count: count = mailitem.attachments.count

    Dim attachments: attachments = mailitem.attachments

    Dim i: i = 0

    While i < count

       i = i + 1

       '附件索引从1开始

       Set olAtt = attachments(i)

       olAtt.SaveAsFile "D:\firefly\" & olAtt.FileName

    Wend

End Sub

Sub resolveAttach()

    Dim ie As SHDocVw.InternetExplorer

    Dim doc As MSHTML.HTMLDocument

    Dim body As MSHTML.HTMLBody

    Set ie = New SHDocVw.InternetExplorer

    ie.Visible = False

    ie.navigate "D:\firefly\test.html"

    Do Until ie.readyState = 4 '检查网页是否加载完毕

     DoEvents '没有加载完毕就将权限还给系统

    Loop

    Set doc = ie.document

    Set body = doc.body

    body.

End Sub

Public Function CreateHTMLBody(id As Integer) As String

    'Creates a new e-mail item and modifies its properties

    Dim objHTMLBody As String

    '可以设置多个模板

    If id = 1 Then

        objHTMLBody = _

        "<font face = 微软雅黑 size = 3>" & _

        "感谢你的来信。我是<font color=red>机器人小星</font>，邮件我已代为阅读。" & _

        "<br/> <br/> " & _

        "来自小星的智能转发</font>"

    ElseIf id = 2 Then

       objHTMLBody = _

       "<table style = border-collapse:collapse <tbody>" & _

       "<tr><td style = border:1px solid #B0B0B0 colspan= 2>版本</td></tr>" & _

       "<tr><td style= border:1px solid #B0B0B0 >APP版本</td></tr>" & _

       "<tr><td style = border:1px solid #B0B0B0>SDK版本</td></tr>" & _

       "</tbody></table>" & _

       "" & _

       "<br/> <br/> " & _

       "来自小星的智能回复</font>"

    End If

    CreateHTMLBody = objHTMLBody

End Function

Sub test()

  Dim str As String

  Dim result As String

  str = _

       "<table style = border-collapse:collapse <tbody>" & _

       "<tr><td style = border:1px solid #B0B0B0 colspan= 2>版本</td></tr>" & _

       "<tr><td style= border:1px solid #B0B0B0 >APP版本</td></tr>" & _

       "<tr><td style = border:1px solid #B0B0B0>SDK版本</td></tr>" & _

       "</tbody></table>" & _

       "" & _

       "<br/> <br/> " & _

       "来自小星的智能回复</font>"

  result = RemoveHTML(str)

  Debug.Print (result)

End Sub

'移除html标签

Public Function RemoveHTML(strText As String)

    Dim nPos1

    Dim nPos2

     Debug.Print ("Body string: ")

    nPos1 = InStr(strText, "<")

    Do While nPos1 > 0

        nPos2 = InStr(nPos1 + 1, strText, ">")

        If nPos2 > 0 Then

            strText = Left(strText, nPos1 - 1) & Mid(strText, nPos2 + 1)

        Else

            Exit Do

        End If

        nPos1 = InStr(strText, "<")

    Loop

    RemoveHTML = strText

End Function

'''

'需求描述

'公司里面每天都会有很多邮件，三分之一都是不需要看的，Outlook的过滤功能不错，都可以处理掉。还有些邮件，根据正文或者附件做一下处理自动转发出去就行了。于是上网搜集了一些资料，写个了小程序，共享一下，以后可以参考，也希望对大家有点用处。

'实现

'废话少说，直接上代码吧。打开Outlook，按Alt+F11打开代码编辑器，输入下面的代码。可能有些兄弟不知道怎么入手，后面会放几个链接做参考。

'

'编辑完保存，在”开始->规则->创建规则”中添加一个过滤规则，在”如何处理该邮件”中选择运行脚本，并选择这个脚本。

Sub AutoResponseReceipt(item As mailitem)

    Debug.Print ("receive an email")

    Dim id As String

    Dim SubjectString As String

    Dim sender As String

    Dim email As Outlook.mailitem

    On Error GoTo Err

    id = item.EntryID                   ' 先获取邮件的ID

    Set email = Application.Session.GetItemFromID(id)

    SubjectString = email.Subject       ' 邮件主题

    sender = email.SenderEmailAddress   ' 邮件的发送人地址

    Debug.Print ("new email arrivaved: subject is " & SubjectString & "  sender is " & sender)

    Debug.Print ("new email arrivaved: subject is " & SubjectString & "  recvs is " & email.Recipients)

    ' 校验主题，这里是对主题做过滤，不合适的直接返回不处理

    Dim index As Integer

    index = InStr(SubjectString, "小票")

    If 0 = index Then

        index = InStr(SubjectString, "receipt")

        If 0 = index Then

            Return

        End If

    End If

    ' 下面这一段是我自己的一些处理逻辑，调用程序处理附件，

    ' 然后将程序处理后的结果当做附件转发给另一个人

    ' 获取附件并执行小票生成程序

    Dim PathPrefix As String

    PathPrefix = "E:\document\receipt_tool\"

    Dim InputFileList As New Collection         ' 这个列表存放收到的附件

    Dim OutputFileList As New Collection        ' 存放程序生成的结果

    Dim AttachFile As Attachment                ' 附件

    For Each AttachFile In email.attachments    ' email.attachments是所有附件

        Debug.Print ("attachment: " & AttachFile.FileName)

        Dim InputFile As String

        Dim OutputFile As String

        InputFile = PathPrefix & AttachFile.FileName

        OutputFile = PathPrefix & AttachFile.FileName & ".docx"

        Debug.Print ("input file is " & InputFile)

        Debug.Print ("output file is " & OutputFile)

        AttachFile.SaveAsFile (InputFile)       ' 保存附件

        Dim cmd As String

        cmd = """" & PathPrefix & "receipt.exe" & """" & " " & InputFile & " " & OutputFile

        Debug.Print ("command string: " & cmd)

        Shell (cmd)                             ' 执行脚本，生成结果

        InputFileList.Add (InputFile)

        OutputFileList.Add (OutputFile)

        'Kill (InputFile)   ' 这里删除的话总会把生成的文件同时删掉

    Next

    If OutputFileList.count = 0 Then

        Debug.Print ("no attachment")

    End If

    ' 转发邮件

    Dim OutMail As Object

    Set OutMail = Outlook.Application.CreateItem(olMailItem)

    With OutMail

        .To = "hnwyllmm@126.com"                ' 要转发邮件的收件人地址

        .Subject = "打印：" & email.Subject     ' 转发邮件的主题

        .body = "帮忙打印小票，谢谢！" & Chr(10) & email.SenderEmailAddress & Chr(10) & email.SenderName ' 转发邮件的正文

    End With

    Dim SendAttach As String                    ' 将程序生成的结果添加到附件中

    For i = 1 To OutputFileList.count

'            MsgBox (SendAttach)

        SendAttach = OutputFileList(i)

        OutMail.attachments.Add (SendAttach)

    Next

    MsgBox ("send")

    OutMail.Send                                ' 发送邮件

    OutMail.Delete                              ' 删除邮件，没用了

Err:

    ' 删除生成的文件

    For i = 1 To OutputFileList.count

        Kill (OutputFileList(i))

    Next

    For i = 1 To InputFileList.count

        Kill (InputFileList(i))

    Next

    email.Delete                                ' 删除收到的邮件

    ' 下面几个是释放对象，其实没有也无所谓

    Set InputFileList = Nothing

    Set OutputFileList = Nothing

    Set OutMail = Nothing

End Sub

Sub Command1_Click7()

    Dim str As String

    Dim li, cd

    Dim c_name As String

    '遍历元素<li>

    For Each li In Dom.document.getElementsByTagName("li")

        '用判断忽略掉列首名称的<li>行

        If li.classname = "lst_row" Then

            '遍历<li>下的节点

            For Each cd In li.ChildNodes

                '判断是否为元素节点

                If cd.NodeType <> 3 Then

                    If cd.classname = "col_2" Then

                        '如果是第2列的<span>,那么再用firstChild取出第一个元素节点<a>

                        str = str & cd.FirstChild.href & " "

                    Else

                        '其他列直接输出文本

                        str = str & cd.innertext & " "

                    End If

                End If

            Next

            str = str & vbCrLf

        End If

    Next

    Print str

End Sub

Sub ParseMaterial()

    Dim Cell As Integer

    Dim ItemNbr As String

    Dim AElement As Object

    Dim AElements As IHTMLElementCollection

    Dim ie As MSXML2.XMLHTTP60

    Set ie = New MSXML2.XMLHTTP60

Dim HTMLDoc As MSHTML.HTMLDocument

Dim HTMLBody As MSHTML.HTMLBody

Set HTMLDoc = New MSHTML.HTMLDocument

Set HTMLBody = HTMLDoc.body

For Cell = 1 To 5                            'I iterate through the file row by row

    ItemNbr = Cells(Cell, 3).Value           'ItemNbr isin the 3rd Column of my spreadsheet

    ie.Open "GET", "http://www.example.com/?item=" & ItemNbr, False

    ie.Send

    While ie.readyState <> 4

        DoEvents

    Wend

    HTMLBody.innerHTML = ie.responseText

    Set AElements = HTMLDoc.getElementById("list-table").getElementsByTagName("tr")

    For Each AElement In AElements

        If AElement.Title = "Material" Then

            Cells(Cell, 14) = AElement.NextNode.Value     'I write the material in the 14th column

        End If

    Next AElement

        Application.Wait (Now + TimeValue("0:00:2"))

Next Cell

End Sub

<html>

<body>

<table width="400" border="1">

 <tr>

  <th align="left">消费项目....</th>

  <th align="right">一月</th>

  <th align="right">二月</th>

 </tr>

 <tr>

  <td align="left">衣服</td>

  <td align="right">241.10</td>

  <td align="right">50.20</td>

 </tr>

 <tr>

  <td align="left">化妆品</td>

  <td align="right">30.00</td>

  <td align="right">44.45</td>

 </tr>

 <tr>

  <td align="left">食物</td>

  <td align="right">730.40</td>

  <td align="right">650.00</td>

 </tr>

 <tr>

  <th align="left">总计</th>

  <th align="right">1001.50</th>

  <th align="right">744.65</th>

 </tr>

</table>

<p>每个表格由 table 标签开始。</p>

<p>每个表格行由 tr 标签开始。</p>

<p>每个表格数据由 td 标签开始。</p>

<h4>一列：</h4>

<table border="1">

<tr>

  <td>100</td>

</tr>

</table>

<h4>一行三列：</h4>

<table border="1">

<tr>

  <td>100</td>

  <td>200</td>

  <td>300</td>

</tr>

</table>

<h4>两行三列：</h4>

<table border="1">

<tr>

  <td>100</td>

  <td>200</td>

  <td>300</td>

</tr>

<tr>

  <td>400</td>

  <td>500</td>

  <td>600</td>

</tr>

</table>

<table border="6">

<caption>我的标题</caption>

<tr>

  <td>100</td>

  <td>200</td>

  <td>300</td>

</tr>

<tr>

  <td>400</td>

  <td>500</td>

  <td>600</td>

</tr>

</table>

<h4>Disc 项目符号列表：</h4>

<ul type="disc">

 <li>苹果</li>

 <li>香蕉</li>

 <li>柠檬</li>

 <li>桔子</li>

</ul>  

<h4>Circle 项目符号列表：</h4>

<ul type="circle">

 <li>苹果</li>

 <li>香蕉</li>

 <li>柠檬</li>

 <li>桔子</li>

</ul>  

<h4>Square 项目符号列表：</h4>

<ul type="square">

 <li>苹果</li>

 <li>香蕉</li>

 <li>柠檬</li>

 <li>桔子</li>

</ul>

<h4>数字列表：</h4>

<ol>

 <li>苹果</li>

 <li>香蕉</li>

 <li>柠檬</li>

 <li>桔子</li>

</ol>  

<h4>字母列表：</h4>

<ol type="A">

 <li>苹果</li>

 <li>香蕉</li>

 <li>柠檬</li>

 <li>桔子</li>

</ol>  

<h4>小写字母列表：</h4>

<ol type="a">

 <li>苹果</li>

 <li>香蕉</li>

 <li>柠檬</li>

 <li>桔子</li>

</ol>  

<h4>罗马字母列表：</h4>

<ol type="I">

 <li>苹果</li>

 <li>香蕉</li>

 <li>柠檬</li>

 <li>桔子</li>

</ol>  

<h4>小写罗马字母列表：</h4>

<ol type="i">

 <li>苹果</li>

 <li>香蕉</li>

 <li>柠檬</li>

 <li>桔子</li>

</ol>  

<h4>一个嵌套列表：</h4>

<ul>

  <li>咖啡</li>

  <li>茶

    <ul>

    <li>红茶</li>

    <li>绿茶</li>

    </ul>

  </li>

  <li>牛奶</li>

</ul>

</body>

</html>

<!DOCTYPE html>

<html>

<head>

    <title>test</title>

</head>

<body>

<div>

<ul class="lstbox">

    <li class="lst_head"><span class="col_1">姓名</span><span class="col_2">邮箱</span><span class="col_3">生日</span></li>

    <li class="lst_row"><span class="col_1">张三</span><span class="col_2"><a href="mailto:zhangsan@web.com" class="email">zhangsan</a></span><span class="col_3">80-5-1</span></li>

    <li class="lst_row"><span class="col_1">李四</span><span class="col_2"><a href="mailto:lisi@web.com" class="email">lisi</a></span><span class="col_3">85-5-1</span></li>

    <li class="lst_row"><span class="col_1">王五</span><span class="col_2"><a href="mailto:wangwu@web.com" class="email">wangwu</a></span><span class="col_3">90-5-1</span></li>

    <li class="lst_row"><span class="col_1">赵六</span><span class="col_2"><a href="mailto:zhaoliu@web.com" class="email">zhaoliu</a></span><span class="col_3">95-5-1</span></li>

</ul>

</div>

</body>

</html>