时间戳

首页 > 解决方案 > 电子邮件正文循环值

excel - 电子邮件正文循环值

问题描述

我正在尝试遍历工作表中的一列 (n=96)，当它遇到一个 <10 的值时，我希望宏从它找到的值中打开 Outlook 和电子邮件偏移值（四列）。

我已经生成了一个工作示例，尽管它似乎仅限于我测试过的一个示例。我想我是从错误的角度接近它。

Sub SendReminderMail()
    Dim p As Long
    Dim OutLookApp As Object
    Dim OutLookMailItem As Object
    Dim iCounter As Integer
    Dim MailDest As String

    'If MsgBox("Are you sure?", vbYesNo) = vbNo Then Exit Sub

    Set OutLookApp = CreateObject("Outlook.application")
    Set OutLookMailItem = OutLookApp.CreateItem(0)

    p = 2

    Do Until Trim$(Cells(p, 1).Value) = ""
        If Cells(p, 1).Value <= 10 Then
            Set OutLookMailItem = OutLookApp.CreateItem(0)
        With OutLookMailItem
            .To = "Emailaddress etc"
            .Subject = "Reminder: " & Cells(1, 7).Value
            .Body = Cells(p, 1).Offset(0, 4).Value
            .Display
        End With
        End If
    p = p + 1
    Loop

End Sub

如何将其设置为遍历所有 <10 值并告诉它将偏移值粘贴到电子邮件正文中？

标签： excelvba

解决方案

我认为您需要将其拆分为两个代码块。

第一个块将遍历行，检查条件，如果需要，调用第二个块，因此邮件发送 Sub，传递必要的参数。

类似于下面的代码：

Sub SendReminderMail(ByVal MailSubject As String, mailBody As String)
Dim p As Long
Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim iCounter As Integer
Dim MailDest As String

'If MsgBox("Are you sure?", vbYesNo) = vbNo Then Exit Sub

Set OutLookApp = CreateObject("Outlook.application")
Set OutLookMailItem = OutLookApp.CreateItem(0)
Set OutLookMailItem = OutLookApp.CreateItem(0)
 With OutLookMailItem
      .To = "Emailaddress etc"
      .Subject = MailSubject
      .Body = mailBody
      .Display
  End With
End Sub

Sub IterateThroughRows()
Dim p As Integer
Dim Sht As Worksheet
Dim MailSubject As String
Dim mailBody As String

Set Sht = ThisWorkbook.Sheets("SheetName")

p = 2

Do Until Sht.Cells(p, 1).Value = ""
      If Cells(p, 1).Value <= 10 Then
            mailBody = mailBody + " | " + Sht.Cells(p, 1).Offset(0, 4).Value
      End If
        p = p + 1
Loop

Call SendReminderMail(MailSubject, mailBody)
MailSubject = "Reminder: " & Sht.Cells(1, 7).Value
End Sub

推荐阅读