代码之家 › 专栏 › 技术社区 › RonanC

电子邮件正文循环值

vba excel

RonanC · 技术社区 · 5 年前

我试图循环查看工作表中的一列(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个值并告诉它将偏移值粘贴到电子邮件正文中?

1 回复 | 直到 5 年前

Mateusz_G 5 年前

我认为你需要把它分成两块代码。

第一个块将遍历行、检查条件,如果需要,调用第二个块,即邮件发送子模块,传递必要的参数。

类似于以下代码:

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

推荐文章