如果存在特定的单元格值，则自动发送电子邮件；在体内包含相邻的值 [英] Automatically send an email if a specific cell value exists; include adjacent value in body

问题描述

我一直在研究xlsm工作表，如果它在其其他数据文件中找不到匹配项，则作为功能的一部分，它将在J列中生成无数据"的结果.

I have been working on an xlsm sheet that as part of its function produces a result of "No Data" in column J if it cannot find a match in its other data files.

我需要做的是让Excel遍历J列并自动生成一封电子邮件，如果J ="No Data"中的值以及电子邮件正文中的值，我需要包括同一列F的单元格偏移值排.

What I need is to have Excel loop through Column J and automatically generate an email if the value in J = "No Data" and in the body of the email I need to include the cell offset value from Column F of the same Row.

我使用了Ron De Bruin代码，并使用了项目中其他地方类似功能的Looping代码对其进行了修改.

I have used the Ron De Bruin code and modified it with Looping code from a similar function elsewhere in the project.

我无法使它起作用，可以使用一些指示.这是我到目前为止的代码

I cannot get this to function and could use some direction. Here is the code I have up to this point

Private Sub EmailIC()

'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
'This macro adapted from: http://www.rondebruin.nl/win/s1/outlook/bmail4.htm

    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim wbXLoc As String, wbX As Workbook, wsX As Worksheet, wsXName As String
    Dim Xlr As Long
    Dim rngX As Range, cel As Range, order As Range

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)

    wbXLoc = "C:\Users\Cole\Downloads\Dads Work\XDock\AutoXrpt.xlsm" 
    wsXName = "AutoX"

    Set wsX = wbX.Sheets(wsXName)

    'Loop through Column J to determine if = "No Data"

    With wbX
         Xlr = .Sheets("AutoX").Cells(Rows.Count, 1).End(xlUp).Row
         Set rngX = wbX.Sheets("AutoX").Range("J2:J" & Xlr)
    End With

 'do the loop and find
    For Each cel In rngX
        If cel.Value = "No Data" Then
            On Error Resume Next
               With OutMail
                   .to = "robe******@msn.com"
                   .CC = ""
                   .BCC = ""
                   .Subject = "Need Pick Face please!"
                   .Body = rngX.cel.Offset(0, -4).Value
                   .Send
               End With
            On Error GoTo 0

         Set OutMail = Nothing
         Set OutApp = Nothing
       End If
    Next cel
End Sub

推荐答案

Om3r看起来不错，他们指出您需要先将wsX变量设置为实际工作表，然后才能设置范围变量rngX.这可能就是为什么您的循环可能无法正常工作的原因.在不知道运行代码时抛出什么错误的情况下很难说.

What Om3r has looks good, they pointed out that you needed to set the wsX variable to an actual sheet before being able to set the range variable rngX. This might be why your loop might not have worked. Hard to say without knowing what error was thrown when you ran your code.

此外，请确保已启用Outlook的对象库.检查功能区下的工具>参考，并确保列出了您的Outlook库.

Also, be sure to have the object library for Outlook enabled. Check under the ribbon Tools>References and make sure your Outlook Library is listed.

这篇关于如果存在特定的单元格值，则自动发送电子邮件；在体内包含相邻的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！